How to create a read-only Postgres user for a support tool

Read-only usually means "cannot write". For a tool that runs queries to answer a customer, three things can go wrong, and each needs its own guard.

• It writes. A bug or a bad prompt turns a lookup into an update. Grants and read-only transactions stop that.
• It reads too much. Password hashes and API keys sit in the same database as plans and invoices. Granting tables one by one keeps them out of reach.
• It gets in the way. A slow query holds a lock or a connection that production needs. Timeouts, a connection limit and row limits bound that.

The statements below need a role that is allowed to create roles. On managed Postgres that is usually the admin user you were given. The examples use a database called app and a new role called support_reader.

Give the tool a role of its own. Do not reuse the one your application connects with: that role can write, and you could not switch one off without breaking the other.

CREATE ROLE support_reader WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  CONNECTION LIMIT 5;

The three NO options are already the defaults. Writing them out makes the intent visible to whoever reads this later. CONNECTION LIMIT caps how many connections the role can hold open, so a tool that misbehaves cannot use up the pool. Five is a starting point.

Set the password from psql with \password support_reader, not with a PASSWORD clause in the statement. A password typed into SQL can end up in your psql history and in the server log.

A new role starts with no access to your tables. Add it one table at a time.

GRANT CONNECT ON DATABASE app TO support_reader;
GRANT USAGE ON SCHEMA public TO support_reader;
GRANT SELECT ON subscriptions, invoices TO support_reader;

Avoid the shortcuts. GRANT SELECT ON ALL TABLES IN SCHEMA public and the built-in pg_read_all_data role (Postgres 14 and newer) hand over every table, including the ones that hold password hashes, sessions and API keys. With a list, a table you add next month stays invisible until you grant it.

Hide columns with a view

When a table mixes columns the tool needs with columns it must not see, put a view in front of it and grant the view.

CREATE SCHEMA support;

CREATE VIEW support.customers AS
  SELECT id, email, plan, created_at
  FROM public.users;

GRANT USAGE ON SCHEMA support TO support_reader;
GRANT SELECT ON support.customers TO support_reader;

A view reads its tables with its owner's rights, so the role needs SELECT on the view and nothing on public.users. If you build a view for every table, the support schema becomes the complete list of what the tool can see, and you can drop the grants on public.

One more thing on older servers. A database created on Postgres 14 or earlier lets every role create tables in the public schema, and it keeps that after an upgrade. Close it with REVOKE CREATE ON SCHEMA public FROM PUBLIC;

One setting makes read-only the starting state of everything the role does.

ALTER ROLE support_reader SET default_transaction_read_only = on;

From the role's next connection on, every transaction it opens starts read-only, and Postgres rejects INSERT, UPDATE, DELETE and schema changes inside it.

This is a default, not a lock: a session can switch it off again. That is why it comes after the grants and not in place of them. The grants are what make a write impossible. This setting catches the day someone grants too much by mistake.

If you have a read replica, point the tool at that. A replica accepts no writes at all, and a heavy query there does not slow the primary. The cost is that its answers can lag behind.

Three settings keep a slow or stuck query from holding anything up.

ALTER ROLE support_reader SET statement_timeout = '5s';
ALTER ROLE support_reader SET lock_timeout = '1s';
ALTER ROLE support_reader SET idle_in_transaction_session_timeout = '15s';

• statement_timeout cancels a query that runs longer than this.
• lock_timeout gives up when a query has waited this long for a lock.
• idle_in_transaction_session_timeout ends a session that opened a transaction and then went quiet.

A read can still hurt. Even a SELECT takes a lock on its table, the weakest kind, and a schema change such as ALTER TABLE has to wait for it. While the schema change waits, every other query on that table queues behind it. A ceiling on the tool's queries puts a ceiling on that wait.

Treat the values as starting points. A lookup about one customer that needs more than a few seconds usually needs an index, not a longer timeout. Postgres 17 and newer also have transaction_timeout, which limits the whole transaction.

Postgres has no setting that caps how many rows a role may read. That limit belongs in the tool.

• Stop reading in code. Take a fixed number of rows from the result and stop, whatever the query says.
• Send one statement at a time. When a driver sends a parameterized query over the extended protocol, Postgres refuses a string that holds more than one statement. Check that yours does.
• Pass values as parameters. A customer's email address is data. It should never be pasted into the SQL text.

If the tool writes its own queries, wrapping each one caps the result no matter what the inner query asks for.

SELECT * FROM (
  SELECT plan, trial_ends_at
  FROM subscriptions
  WHERE customer_email = $1
) AS lookup
LIMIT 50;

Change the database, table and column names to yours.

CREATE ROLE support_reader WITH
  LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
  CONNECTION LIMIT 5;
-- Then set the password in psql: \password support_reader

GRANT CONNECT ON DATABASE app TO support_reader;
GRANT USAGE ON SCHEMA public TO support_reader;
GRANT SELECT ON subscriptions, invoices TO support_reader;

CREATE SCHEMA support;
CREATE VIEW support.customers AS
  SELECT id, email, plan, created_at
  FROM public.users;
GRANT USAGE ON SCHEMA support TO support_reader;
GRANT SELECT ON support.customers TO support_reader;

ALTER ROLE support_reader SET default_transaction_read_only = on;
ALTER ROLE support_reader SET statement_timeout = '5s';
ALTER ROLE support_reader SET lock_timeout = '1s';
ALTER ROLE support_reader SET idle_in_transaction_session_timeout = '15s';

Connect as support_reader and try to break it. SET ROLE from your admin session is not the same test, because it does not load the role's settings.

SHOW default_transaction_read_only;
-- on

SELECT count(*) FROM invoices;
-- works

UPDATE invoices SET amount_due = 0;
-- ERROR: cannot execute UPDATE in a read-only transaction

BEGIN READ WRITE;
UPDATE invoices SET amount_due = 0;
-- ERROR: permission denied for table invoices
ROLLBACK;

SELECT * FROM users;
-- ERROR: permission denied for table users

SELECT pg_sleep(10);
-- ERROR: canceling statement due to statement timeout

The second UPDATE is the one that matters. The session has switched read-only off, and the write still fails, because the role was never granted it.

Then list what the role holds. Every row should say SELECT.

SELECT table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'support_reader';

• Rows per customer. The role can read every row of the tables you granted. Showing it only some rows is row-level security, which is a separate setup.
• Where the data goes next. Anything the role can read can end up in a reply, or be passed to whichever service runs the tool. Leave out columns you would not paste into an email.
• The network. Require TLS, and restrict where the role can connect from in pg_hba.conf or your provider's allowlist.
• Turning it off. One role per tool means you can stop one tool without touching the rest: ALTER ROLE support_reader NOLOGIN;

Nightclerk is an email agent that drafts replies to your customers, and many of those replies need a lookup like the ones above. You connect your database with read-only credentials that you create. The role from this guide is exactly that.

Nightclerk does not rely on those credentials alone. On its own side it allows only SELECT queries, runs every query in a read-only transaction, applies a timeout and limits the rows it reads. Postgres is supported first.

A lookup only ever feeds a draft, and no draft is sent until you approve it. The rest is on the Security and data access page.