Create a read-only Postgres user

Create a role with login. Grant it connect on the database, usage on the schema, and select on the tables. The role can read each table and can write to none of them.

Guide · zz 0.4.0 or later · Updated 2026-10-07 · Plain text for agents: /docs/postgres-read-only-user.md

The statements are standard SQL of Postgres. Do steps 1 to 3 in one psql session, as the role that owns the tables.

<admin url> is the connection URL of that role. Example: postgres://owner@db.example.com:5432/shop.

Before you start

• psql is installed: psql --version
• The server has Postgres 14 or later, and you can connect: psql "<admin url>" -c "show server_version"

1. Create the role

Do

Open a session with psql "<admin url>".

• <user> is the name of the new role. Example: app_ro.
• <password> is a long random password that you make. Store it as a secret.

create role <user> login password '<password>';

Why

In Postgres, a user is a role that can log in. A new role has no right on your tables.

You should see

CREATE ROLE

If not

• already exists: A role has this name. Use a different name.
• permission denied to create role: Your role cannot create roles. Connect as a role that has CREATEROLE.

2. Let the role connect

Do

• <database> is the name of the database that has the tables.
• <schema> is the schema that has the tables. The default schema is public.
• <user> is the role from step 1.

grant connect on database <database> to <user>;
grant usage on schema <schema> to <user>;

Why

connect lets the role open a session on the database. usage lets it find the tables of the schema.

You should see

GRANT
GRANT

If not

• does not exist: The message names the database, the schema, or the role. Correct the name.

3. Let the role read the tables

Do

<schema> and <user> are the values from step 2.

Note: The second statement applies only to tables that the same role creates. If a different role creates tables, that role must do the second statement.

grant select on all tables in schema <schema> to <user>;
alter default privileges in schema <schema> grant select on tables to <user>;

Why

The first statement is for the tables that are there now. The second is for each table that your role creates later.

You should see

GRANT
ALTER DEFAULT PRIVILEGES

If not

• permission denied for table: Your role does not own that table. Connect as the owner of the tables.
• no privileges were granted: Your role cannot grant on that table. Connect as the owner of the tables.

4. Prove it

Do

• <user url> is the connection URL of the new role. Example: postgres://app_ro:<password>@db.example.com:5432/shop.
• <table> is the name of one table of the schema.

The command ends with exit code 1, because the insert fails. That is correct.

psql "<user url>" -c "select count(*) from <table>" -c "insert into <table> default values"

Why

The select shows that the role can read. The error of the insert shows that it cannot write.

You should see

(1 row)
ERROR:  permission denied for table <table>

If not

• permission denied for table: This line is correct one time, for the insert. If the line (1 row) is absent, do step 3 again.
• permission denied for schema: Do the second statement of step 2 again.
• password authentication failed: The password in <user url> is not the password of step 1.
• INSERT 0 1: The role can write. A different grant gives it this right. Remove that grant.

Postgres 14

In Postgres 14, each role can create a table in the schema public. The new role can thus create its own tables there, and write to them.

Postgres 15 and later do not permit this in a new database. To stop it in Postgres 14, do this statement as the owner of the database:

revoke create on schema public from public;

Note: The statement applies to each role that has no grant of its own. A role that must create tables then needs grant create on schema public.

Use the user in a zriz test

zriz runs end-to-end tests of your app, and an SQL step of a test reads your database. Give that step the read-only user.

Put the connection URL in the file of the environment, and put its name in sensitive. zz then keeps the value on your machine.

.zriz/environments/local.json:

{
  "values": { "SHOP_DB": "postgres://<user>:<password>@<host>:5432/<database>" },
  "sensitive": ["SHOP_DB"]
}

.zriz/resources/shopdb.json:

{
  "type": "sql",
  "connection": "${env.SHOP_DB}",
  "read-only": true,
  "actions": {
    "user-by-email": { "query": "SELECT id, email FROM users WHERE email = ${ctx.email}" }
  }
}

Use the two guards together: the read-only user and the key read-only. Can a zriz test change my data? tells what each guard stops.

On a deployed runner, the connection is in the config of the runner: Secrets and databases in the config.