Datasources

Creating a read-only user in the database

If you do not need the ability to make changes to your database via Cluvio, it is best to reduce the permissions for the role that is used in the datasource.

Here are the basics on how to accomplish this for each of the databases supported. 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', the user to be used by cluvio named 'cluvio_readonly' and the 'secret_password' replaced with a proper password:

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