Create a read-only user

Give QueryDesk a database user that can only read, and a query run through it can't change anything, whoever wrote it. This guide creates one on each engine QueryDesk supports, checks that a write fails, and adds the user to QueryDesk.

Run each script as a user that can create users and grant privileges, such as the database's admin or the owner of its tables. Replace orders with your database, app with the schema or owner your tables are in, and the password with one you generate.

On QueryDesk Cloud the connection comes from 35.224.237.12, or from inside your network through a Tunnel. Where your engine ties a user to a host, allow that address.


PostgreSQL

These steps also cover TimescaleDB and managed PostgreSQL such as Amazon RDS, Aurora, Cloud SQL, Azure Database for PostgreSQL, Supabase and Neon.

Create the user

CREATE ROLE querydesk_readonly WITH LOGIN PASSWORD 'generated-password';
GRANT CONNECT ON DATABASE orders TO querydesk_readonly;
GRANT USAGE ON SCHEMA app TO querydesk_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO querydesk_readonly;

-- tables the owner creates later are readable too
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO querydesk_readonly;

-- every transaction this user opens starts read-only
ALTER ROLE querydesk_readonly SET default_transaction_read_only = on;

Repeat the USAGE, SELECT and ALTER DEFAULT PRIVILEGES lines for each schema, with app_owner as the role that creates tables in it. On PostgreSQL 14 or later, GRANT pg_read_all_data TO querydesk_readonly; grants reads on every schema at once, tables created later included.

On PostgreSQL 14 and earlier, every role can create tables in the public schema. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC; if nothing you run relies on it.

Check that a write fails

-- signed in as querydesk_readonly
CREATE TABLE querydesk_write_check (id int);
-- ERROR:  cannot execute CREATE TABLE in a read-only transaction

MySQL

These steps also cover MariaDB and managed MySQL such as Amazon RDS, Aurora, Cloud SQL and Azure Database for MySQL.

Create the user

CREATE USER 'querydesk_readonly'@'%' IDENTIFIED BY 'generated-password';
GRANT SELECT, SHOW VIEW ON orders.* TO 'querydesk_readonly'@'%';

'%' accepts the user from any host. To accept it only from QueryDesk Cloud, use 'querydesk_readonly'@'35.224.237.12' in both lines. Through a Tunnel, use the address the Tunnel runs at instead.

Check that a write fails

-- signed in as querydesk_readonly
CREATE TABLE orders.querydesk_write_check (id int);
-- ERROR 1142 (42000): CREATE command denied to user 'querydesk_readonly'@...

SQL Server

Create the user

CREATE LOGIN querydesk_readonly WITH PASSWORD = 'generated-password';
GO
USE orders;
GO
CREATE USER querydesk_readonly FOR LOGIN querydesk_readonly;
ALTER ROLE db_datareader ADD MEMBER querydesk_readonly;
GO

On Azure SQL Database, skip the login and create a contained user in the database itself: CREATE USER querydesk_readonly WITH PASSWORD = 'generated-password';, then add it to db_datareader as above.

db_datareader reads every table and view in the database. To limit the user to one schema, skip the role and run GRANT SELECT ON SCHEMA::app TO querydesk_readonly; instead.

Check that a write fails

-- signed in as querydesk_readonly
CREATE TABLE querydesk_write_check (id int);
-- CREATE TABLE permission denied in database 'orders'.

ClickHouse

These steps also cover ClickHouse Cloud.

Create the user

CREATE USER querydesk_readonly IDENTIFIED BY 'generated-password'
  SETTINGS readonly = 2;
GRANT SELECT ON orders.* TO querydesk_readonly;

GRANT SELECT alone keeps the user from writing. readonly = 2 adds a second guard and still lets QueryDesk set the settings it sends with each query, such as max_execution_time for the credential's timeout. Don't use readonly = 1: it refuses those settings, and every query fails.

If your server defines its users in users.xml rather than with SQL, add the user there with a profile that sets <readonly>2</readonly>.

Check that a write fails

-- signed in as querydesk_readonly
CREATE TABLE orders.querydesk_write_check (id UInt8) ENGINE = Memory;
-- Code: 497. DB::Exception: querydesk_readonly: Not enough privileges. ...

Oracle

Create the user

CREATE USER querydesk_readonly IDENTIFIED BY "generated-password";
GRANT CREATE SESSION TO querydesk_readonly;

-- READ allows queries but not SELECT ... FOR UPDATE, which locks rows
BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'APP') LOOP
    EXECUTE IMMEDIATE 'GRANT READ ON APP."' || t.table_name || '" TO querydesk_readonly';
  END LOOP;
END;
/

Grant READ on each table created later the same way. On Oracle Database 23ai, GRANT READ ANY TABLE ON SCHEMA app TO querydesk_readonly; covers the schema's tables, later ones included.

QueryDesk's table list and the editor's suggestions show the tables that the Database's default credential's user owns. A read-only user owns none, so with it as the default credential the list is empty. Queries through it still work: name each table with its owner, as in SELECT * FROM app.orders.

Check that a write fails

-- signed in as querydesk_readonly
CREATE TABLE querydesk_write_check (id NUMBER);
-- ORA-01031: insufficient privileges

Add the user to QueryDesk

  1. On the Databases page, click New database, or the gear icon next to a database that is already connected.
  2. In the Credentials section, click + Add Credential.
  3. Enter the read-only user's Username and Password, and leave Reviews at 0.
  4. Click Test Connection under the new credential.

To keep queries off your primary as well, point the database at a read replica. See Connect a read replica.

Was this page helpful?