Klyrova

How to create a read-only database user in PostgreSQL, SQL Server and Azure SQL

Before you connect any reporting, BI or AI tool to a database, give it a login that can only read. Here is the SQL for PostgreSQL, SQL Server and Azure SQL Database, how to prove the user can't write, and what to do for Microsoft Fabric.

Start freeSee pricing

A dashboard’s datasets, one opened to show the SQL it replays on refresh

The short answer: create a login, let it connect to one database, and grant it SELECT and nothing else. In PostgreSQL that is a role with LOGIN, CONNECT, USAGE on the schema and SELECT on its tables. In SQL Server and Azure SQL it is a database user in the built-in db_datareader role. A tool that connects with that user can read every table you allowed and can't change anything, whatever it sends.

Why a read-only user matters

Any tool that writes SQL for you, whether a BI product or an AI agent, is one bug or one bad prompt away from sending a statement you didn't mean. Klyrova checks every statement it runs and refuses anything but a single SELECT, and on PostgreSQL it also runs each query in a read-only transaction. Those checks live in Klyrova. A read-only user lives in your database, which means it holds even if every check outside the database were to fail. It is the lock you control.

PostgreSQL

Connect as the database owner or an administrator (on a managed service, the admin user your provider gave you) and run this, replacing the names and the password:

-- 1. A role that can sign in, and that starts every transaction read-only
        CREATE ROLE klyrova_reader WITH LOGIN PASSWORD 'use-a-long-random-password';
        ALTER ROLE klyrova_reader SET default_transaction_read_only = on;

        -- 2. Let it connect to one database and see one schema
        GRANT CONNECT ON DATABASE analytics TO klyrova_reader;
        GRANT USAGE ON SCHEMA public TO klyrova_reader;

        -- 3. Read every table and view that exists now
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO klyrova_reader;

        -- 4. ...and the ones created later
        ALTER DEFAULT PRIVILEGES IN SCHEMA public
          GRANT SELECT ON TABLES TO klyrova_reader;

Repeat steps 2 to 4 for each schema the reports need. ALL TABLES covers views and foreign tables too.

The catch with default privileges

ALTER DEFAULT PRIVILEGES only applies to tables created later by the role that ran it. If your application's migrations create tables as a different role, say app_owner, name that role, or new tables won't be readable:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
          GRANT SELECT ON TABLES TO klyrova_reader;

You need to be a member of app_owner (or a superuser) to run it.

PostgreSQL 14 and later: pg_read_all_data

PostgreSQL 14 added a predefined role that reads everything: every table, view and sequence in every schema. It saves the per-schema grants, at the price of being broad:

CREATE ROLE klyrova_reader WITH LOGIN PASSWORD 'use-a-long-random-password';
        ALTER ROLE klyrova_reader SET default_transaction_read_only = on;
        GRANT pg_read_all_data TO klyrova_reader;

Use it when the reports genuinely need the whole database. If some tables hold data the reports should never see, such as password hashes or personal details, prefer the per-schema grants. See the PostgreSQL documentation on predefined roles.

About default_transaction_read_only

Setting default_transaction_read_only on the role makes every transaction it starts read-only, so even a table it somehow had rights to write would refuse. A session can turn it back off with SET, so treat it as a second lock, not the first: the grants are what really limit the user. (Klyrova refuses SET in any case.)

Check the PostgreSQL user can't write

As an administrator, ask PostgreSQL what the user may do to a table:

SELECT has_table_privilege('klyrova_reader', 'public.orders', 'SELECT') AS can_read,
               has_table_privilege('klyrova_reader', 'public.orders', 'INSERT') AS can_insert,
               has_table_privilege('klyrova_reader', 'public.orders', 'UPDATE') AS can_update,
               has_table_privilege('klyrova_reader', 'public.orders', 'DELETE') AS can_delete;

You want true, then three false. Then sign in as the user (for example with psql "host=db.example.com dbname=analytics user=klyrova_reader") and try a harmless write such as CREATE TABLE write_test (id int);. It should fail with "cannot execute CREATE TABLE in a read-only transaction" or "permission denied for schema public".

SQL Server

On SQL Server a login lives on the server and a user lives in each database. Create both, then add the user to db_datareader, which can read every table and view in that database:

-- On the server (in master)
        CREATE LOGIN klyrova_reader WITH PASSWORD = 'use-a-long-random-password';

        -- In the database the reports read
        USE analytics;
        CREATE USER klyrova_reader FOR LOGIN klyrova_reader;
        ALTER ROLE db_datareader ADD MEMBER klyrova_reader;

Keep it away from sensitive tables

DENY wins over the role's grant, so you can carve out a schema or a table the reports must not see:

DENY SELECT ON SCHEMA::hr TO klyrova_reader;
        DENY SELECT ON OBJECT::dbo.payroll TO klyrova_reader;

If only a few tables are needed, skip db_datareader and grant SELECT on those tables or one schema instead (GRANT SELECT ON SCHEMA::sales TO klyrova_reader;). Microsoft's documentation on database-level roles lists exactly what each role allows.

Check the SQL Server user can't write

EXECUTE AS USER = 'klyrova_reader';
        SELECT HAS_PERMS_BY_NAME('dbo.orders', 'OBJECT', 'SELECT') AS can_read,
               HAS_PERMS_BY_NAME('dbo.orders', 'OBJECT', 'INSERT') AS can_insert,
               HAS_PERMS_BY_NAME('dbo.orders', 'OBJECT', 'DELETE') AS can_delete;
        REVERT;

You want 1, 0, 0. Make sure the user isn't also in db_datawriter, db_ddladmin or db_owner: a role membership elsewhere gives back what you left out here.

Azure SQL Database

Azure SQL Database lets you create a contained user: a user with its own password, inside the database, with no server login. Connect to the user database itself, not master, and run:

CREATE USER klyrova_reader WITH PASSWORD = 'use-a-long-random-password';
        ALTER ROLE db_datareader ADD MEMBER klyrova_reader;

The DENY examples and the permission check above work the same way. Two Azure settings decide whether the user can connect at all:

  • Authentication. If the server is set to Microsoft Entra-only authentication, password users can't sign in. A tool that connects with a user name and password needs SQL authentication allowed.
  • Firewall. The server's Networking settings must allow the tool's traffic. Klyrova runs in Microsoft Azure, so allow Azure services and resources to access the server; for a narrower rule, support@klyrova.app can give you Klyrova's current outbound addresses.

More detail in Microsoft's guide to contained database users.

Microsoft Fabric

There is no user to create for Fabric in Klyrova. Klyrova connects to Fabric with your own Microsoft sign-in and delegated permissions, so it reads exactly what your account can read. To narrow that, connect with an account whose workspace access is narrower, or limit it inside a warehouse with T-SQL GRANT and DENY. Lakehouse and mirrored database SQL analytics endpoints are read-only in Fabric itself. See Microsoft Fabric in Klyrova.

Add the user to Klyrova

  1. Open Data sources and choose Add source.
  2. Pick PostgreSQL, SQL Server or Azure SQL Database.
  3. Enter the host (or server), port, database, the read-only user name and its password.
  4. Press Test, then Add & connect. A new source that fails its test isn't kept, so nothing half-configured is left behind.

The password is encrypted at rest and never sent back to your browser. From then on every query Klyrova runs, from the agent or a scheduled refresh, goes through its own read-only check first and then reaches your database as this user. Next: build your first AI dashboard.

Read-only user questions

Is GRANT SELECT enough to make a PostgreSQL user read-only?

Granting only SELECT means the user can't write to those tables. On PostgreSQL 14 and earlier, also check it can't create tables in the public schema, which every role could do by default before version 15; REVOKE CREATE ON SCHEMA public FROM PUBLIC; removes that for all roles.

What does db_datareader allow in SQL Server?

SELECT on every user table and view in the database it is granted in. It doesn't allow inserts, updates, deletes, schema changes or running procedures.

Do new tables become readable automatically?

In SQL Server and Azure SQL, yes: db_datareader covers tables created later. In PostgreSQL only if you set default privileges for the role that creates them, or use pg_read_all_data.

Can I use the same read-only user for several tools?

You can, but one user per tool makes it easy to see who ran what and to cut one tool off without affecting the others.

Does Klyrova need a read-only user if it already checks queries?

It doesn't strictly need one: Klyrova refuses anything but a single SELECT. We still recommend it, because the database enforces it on its own, whatever happens outside.

Updated 30 October 2026

Try it on your own data

Connect a database or drop in a file, ask a question, and keep the answer as a live dashboard. Free to start, with 500 credits and no card.

Start freeSee pricing