# Create a read-only user in PostgreSQL or SQL Server

> Step-by-step SQL to create a read-only user in PostgreSQL, SQL Server and Azure SQL Database, check it can't write, and connect it to a reporting tool.

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.

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](https://www.postgresql.org/docs/current/predefined-roles.html).

### 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](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/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](mailto:support@klyrova.app) can give you Klyrova's current outbound addresses.

More detail in Microsoft's guide to [contained database users](https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable).

## 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](https://klyrova.app/connect/microsoft-fabric).

## 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](https://klyrova.app/guides/build-an-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.

---
Source: https://klyrova.app/guides/read-only-database-user · Updated 2026-10-30 · Klyrova (https://klyrova.app/)
