---
kind: guide
zz: "0.4.0"
updated: 2026-10-07
state: live
rules: https://zriz.io/llms.txt
---
# 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.

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 {#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 {#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.

```sql
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

```text
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 {#grant-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.

```sql
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

```text
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 {#grant-select}

### 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.

```sql
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

```text
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 {#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.

```sh
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

```text
(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 {#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:

```sql
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 {#use-in-zriz}

zriz runs end-to-end tests of your app, and an SQL [step](https://zriz.io/docs/words.md#step) of a test reads your database. Give that step the read-only user.

Put the connection URL in the file of the [environment](https://zriz.io/docs/words.md#environment), and put its name in `sensitive`. `zz` then keeps the value on your machine.

`.zriz/environments/local.json`:

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

`.zriz/resources/shopdb.json`:

```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?](https://zriz.io/docs/security.md#change-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](https://zriz.io/docs/runner.md#config-secrets).