Datasources

Creating a read-only user in the database

If you do not need the ability to make changes to your database via Cluvio, which is the common case, it is best to reduce the permissions for the role that is configured in Cluvio's data source.

Here are the basics on how to accomplish this for each of the supported databases. You should check the database documentation to make sure that the setup matches the exact desired level of permissions.

Assuming the database is in the default schema and named 'my_database', that the user to be used by cluvio is named 'cluvio_readonly' and that the 'secret_password' is replaced with a proper password, the commands to execute are the following:

PostgreSQL

CREATE ROLE cluvio_readonly LOGIN PASSWORD 'secret_password';
GRANT CONNECT ON DATABASE my_database TO cluvio_readonly;
GRANT USAGE ON SCHEMA public TO cluvio_readonly;
GRANT SELECT ON ALL TABLES IN schema public TO cluvio_readonly;

Redshift

CREATE USER cluvio_readonly PASSWORD 'secret_password';
GRANT USAGE ON SCHEMA public TO cluvio_readonly;
GRANT SELECT ON ALL TABLES IN schema public TO cluvio_readonly;

MySQL / MariaDB (ssh tunnel on the same host)

CREATE USER 'cluvio_readonly'@'localhost' IDENTIFIED BY 'secret_password';
GRANT SELECT ON my_database.* TO 'cluvio_readonly'@'localhost';
FLUSH PRIVILEGES;

MySQL / MariaDB (remote connection)

CREATE USER 'cluvio_readonly'@'%' IDENTIFIED BY 'secret_password';
GRANT SELECT ON my_database.* TO 'cluvio_readonly'@'%';
FLUSH PRIVILEGES;

BigQuery

The permissions on Google Big Query are controlled via the Roles attached to the Service Account in the IAM & Admin section in the Big Query console. Cluvio needs at least the following permissions to fetch the schema and run queries:

  • BigQuery > BigQuery Data Viewer
  • BigQuery > BigQuery User

Related articles