跳到主要内容

Read-only database setup

Dataira refuses write-privileged database accounts. When you connect a datasource, we probe the credential: if it can write, change, or drop anything, the connection is refused with the exact reason. This page shows how to create a read-only user for each engine in about two minutes.

When a probe fails, the API response and the onboarding chat include this same SQL for your specific database, ready to copy.

Postgres​

Run as a superuser on the database Dataira will read. Replace the password and database name:

CREATE ROLE dataira_reader LOGIN PASSWORD '<choose-a-strong-password>';
GRANT CONNECT ON DATABASE your_database TO dataira_reader;
GRANT USAGE ON SCHEMA public TO dataira_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataira_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO dataira_reader;

The last line covers tables created after setup. Connect Dataira with user dataira_reader and the password you chose.

MySQL / MariaDB​

CREATE USER 'dataira_reader'@'%' IDENTIFIED BY '<choose-a-strong-password>';
GRANT SELECT ON your_database.* TO 'dataira_reader'@'%';
FLUSH PRIVILEGES;

MySQL datasources connect through the Dataira tunnel. Use % as the host part unless you know the connector egress IPs, in which scope them instead.

ClickHouse​

CREATE ROLE dataira_readonly;
GRANT SELECT ON your_database.* TO dataira_readonly;
CREATE USER dataira_reader
  IDENTIFIED WITH sha256_password BY '<choose-a-strong-password>'
  ROLES dataira_readonly;

BigQuery​

BigQuery uses Google service accounts instead of SQL:

  1. Google Cloud Console, IAM and Admin, Service Accounts, Create service account.
  2. Grant it only BigQuery Data Viewer and BigQuery Job User.
  3. Create a JSON key for it, and paste that JSON when Dataira asks for the credential.

Nothing else is granted: Google enforces the read-only scope at the token level.

After creating the user​

Reconnect or retry activation with the new credential. The probe runs again and confirms read-only access. If it still refuses, the response names the exact privilege that failed, for example a leftover CREATE on the schema.

Verify it yourself (optional)​

-- Postgres: expect all false or 0
SELECT rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'dataira_reader';

Questions about a specific grant? Talk to us.