SaaS architecture
A read-only user in PostgreSQL: what it can still do
Three ways to create a read-only user in PostgreSQL 17, measured: what each still allows, from a write through a function to password verifiers and RLS.
PostgreSQL has no read-only attribute for a role. A read-only user is meant to be a role from which every way to write has been taken away, and there are more of those than INSERT, UPDATE and DELETE. The three usual recipes are a set of SELECT grants, membership of the predefined role pg_read_all_data, and the setting default_transaction_read_only. They are often presented as equivalents.
We built all three on PostgreSQL 17.11, against a schema owned by a migration role, and tried as each of them what a reporting tool, a curious analyst or a leaked password could try. One of the three stops no write that the session decides to make. One reads more than the application’s data. And the careful one could still change a row, in our setup, by calling a function.
How do you create a read-only user in PostgreSQL?
Create a login role, give it CONNECT on the database and USAGE on the schema, SELECT on the tables that exist, and a default privilege for the tables that will exist later:
create role reporting login password '…';
grant connect on database shop to reporting;
grant usage on schema app to reporting;
grant select on all tables in schema app to reporting;
grant select on all sequences in schema app to reporting;
alter default privileges for role migrator in schema app
grant select on tables to reporting;
migrator in the last statement is the role that creates the tables. Without FOR ROLE the default is recorded for whoever runs the statement, and a table the migration role creates next week is refused; we measured that in ALTER DEFAULT PRIVILEGES not working. With it, our read-only role read a table created afterwards without a new grant. A schema created later is not covered; it needs its own USAGE and its own default.
Two lines deserve a remark. CONNECT is granted to PUBLIC on every database by default, so the second line only does something once that has been revoked, which the last section does. And SELECT on sequences lets the role read a sequence’s current value, which pg_dump needs; nextval stayed refused with it.
That is the first of three recipes. Side by side, as measured:
SELECT grants + default privilege | member of pg_read_all_data | read-write grants + default_transaction_read_only = on | |
|---|---|---|---|
update on a table | permission denied for table | permission denied for table | refused, until the session ran begin read write: then UPDATE 1 |
| a table created later | readable | readable | permission denied for table newt; it had no default privilege of its own |
| a table in a schema it was not granted | permission denied for schema hidden | readable | permission denied for schema hidden |
pg_authid, with the password verifiers | permission denied for table pg_authid | readable | permission denied for table pg_authid |
| a table with row-level security and no policy for the role | 0 rows | 0 rows | 0 rows |
Does a read-only transaction default make a user read-only?
No. default_transaction_read_only is a default that the session can change, not a privilege. We gave a role the full set of write grants and alter role … set default_transaction_read_only = on. A plain update failed with cannot execute UPDATE in a read-only transaction. Then the same role ran set default_transaction_read_only = off and the update succeeded; in another session begin read write did the same without touching the setting. Nothing was needed that an ordinary role does not have.
While it is on, it does refuse more than table writes: create temp table failed with cannot execute CREATE TABLE in a read-only transaction. It is useful as a guard against accidents, for a person who connects to production with a role that may write. It does not make a role read-only.
What can a read-only user still do?
More than read the tables it was granted. We ran these as the role from the first recipe, the one with nothing but CONNECT, USAGE and SELECT:
| Statement | Result |
|---|---|
select app.bump(10), a SECURITY DEFINER function that updates a row | executed; the row changed from 10 to 11 |
create temp table x …; insert into x select generate_series(1, 100000) | created, 3568 kB; nothing limits the size |
select lo_create(0) | a large object, still in the database after the session ended |
set statement_timeout = 0 over a role default of 1s | accepted; pg_sleep(1.5) then ran to the end |
alter role <itself> set statement_timeout = 0 | accepted; every later session started without the timeout |
alter role <itself> password '…' | accepted |
set work_mem = '1GB' | accepted |
select pg_advisory_lock(1) | taken |
listen x; notify x, 'hello' | delivered |
begin; lock table app.invoices in access share mode | taken |
copy app.invoices to stdout | all rows |
select prosrc from pg_proc where proname = 'bump' | the function’s source |
select count(*) from pg_roles, select datname from pg_database | every role and every database in the cluster |
a connection to another database in the cluster, and create temp table there | accepted |
And these were refused: insert, update, delete and truncate on the table; select … for update and for share, and lock table … in share mode (all three need a write privilege on the table); create table in the schema and in public; create schema; nextval on a sequence, with or without SELECT on it; vacuum and analyze (a warning, and the table is skipped); alter role <itself> bypassrls and connection limit; show hba_file; pg_read_file; and cancelling a superuser’s query. In pg_stat_activity the role saw one row per backend, with <insufficient privilege> in place of the query text for every one that was not its own. In pg_class it read reltuples for a table whose rows a policy hid from it: 3,000.
Four of the allowed ones are worth a closer look.
Functions. EXECUTE on a new function is granted to PUBLIC, so a read-only role can call every function it can reach unless someone revoked that. For an ordinary function this is harmless: it runs with the caller’s privileges, and a write to a table inside it is refused. A SECURITY DEFINER function runs with its owner’s privileges, and ours updated a table for a role that has no UPDATE anywhere. After revoke execute on function app.bump(bigint) from public the call failed with permission denied for function bump. To make that the default for functions the migration role creates, the revoke has to be global, not per schema; the same article on default privileges measures why.
Temporary tables and large objects. TEMPORARY on a database is granted to PUBLIC too. A read-only role can fill disk with a temporary table, and a large object it creates stays. revoke temporary on database … from public turned the first into permission denied to create temporary tables in database "rodb". Reporting tools that stage results in temporary tables need it back, as a grant to that role. The revoke did not touch large objects; those closed with revoke execute on function lo_create(oid), lo_creat(integer), lo_from_bytea(oid, bytea) from public, after which lo_create gave permission denied for function lo_create.
Limits set on the role. alter role … set statement_timeout = '1s' cancelled a 1.5 second query, until the session set the timeout to 0 itself. A role-level setting is a default, like default_transaction_read_only, and a role may rewrite its own: after alter role … set statement_timeout = 0, run by the role itself, new sessions started with 0. alter role … connection limit 2 did hold: with two sessions open, a third was refused with too many connections for role "f_grant". A limit on query time that a role cannot lift has to sit outside the session, in a pooler or in whatever cancels queries from another connection.
Its own password. A role may change its own password, and ours did. Whoever holds a leaked reporting login can lock the reporting tool out of it. That is not a privilege that can be revoked; it is a reason to give a tool and a person different logins.
What does pg_read_all_data read?
Every table, view and sequence in every schema, the system catalogs included. The documentation describes the role as: “Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly.” In our runs a member read a table in a schema nobody had granted it, a table created after the membership, and pg_authid: select count(*), count(rolpassword) from pg_authid returned every role in the cluster, 117 at that moment, 99 of them with a stored password, each as a SCRAM-SHA-256$… verifier and in our cluster the superuser’s among them. A verifier is not a password, but it is what an offline guessing attack needs.
The catalogs also hold data from tables. With row-level security on and select count(*) from app.invoices returning 0, the member read the planner’s statistics for that table straight from pg_statistic: for tenant_id, the values {1,2,3}. The view pg_stats showed it nothing for the table; the table underneath is not filtered. On a table with names or e-mail addresses those sampled values are customer data. The role with SELECT grants got permission denied for table pg_statistic.
It is the shortest recipe, and for a job that has to read every schema it is the fitting one, with the limits under row-level security that the pg_dump section shows. For a reporting login that people or a BI tool use, it reads further than the application’s data. The grants of the first recipe stop at the schema you name, for rows: the names of tables and columns, function source and policy definitions of every schema stay readable through the catalog for any role.
The membership applies in every database of the cluster the role can connect to. The role that got its SELECT grants in one database got permission denied for schema app in a second one; the member of pg_read_all_data read the application tables there as well.
Does a read-only user bypass row-level security?
Not through the table, and neither does pg_read_all_data. After the migration role enabled row-level security on invoices with a policy for the application role only, both read-only roles counted 0 rows where they had counted 3,000, without an error. pg_roles shows rolbypassrls = f for pg_read_all_data, and the documentation says so: “This role does not have the role attribute BYPASSRLS set.”
What they still read is whatever reaches the table as another role. A view owned by the migration role, which is the table’s owner, returned all three tenants’ totals to the read-only role with the policy in place; with FORCE ROW LEVEL SECURITY on the table it returned none, and so did the same view with security_invoker = true. That is the result of our views and row-level security article, arriving from the other side: a reporting role with access to such a view reads every tenant through it, whatever the policy says.
To let a reporting role read all tenants on purpose there are two ways, and they differ in reach. A policy for select to reporting using (true) on each table gave the role its 3,000 rows on that table and nothing on a table without such a policy. alter role … bypassrls gave it every row of every table it has SELECT on, now and later. Who else passes a policy without being named in it is in who bypasses row-level security.
Can a read-only user run pg_dump?
Not a full dump with table grants alone, and not silently past row-level security. With SELECT on tables only, pg_dump -n app stopped at permission denied for sequence invoices_id_seq: the dump reads the sequence behind an identity column, and SELECT on tables does not cover sequences. That is why the recipe above includes them. After grant select on all sequences in schema app it got as far as the table with row-level security and stopped again: query would be affected by row-level security policy for table "invoices". pg_dump turns row_security off for its session so that a filtered dump is an error and not a surprise.
The member of pg_read_all_data passed the sequence and stopped at the same message. With --enable-row-security its dump completed, with no rows for that table. A backup role under row-level security therefore needs BYPASSRLS, or a policy of its own together with --enable-row-security: with a using (true) policy for the role, the plain dump still stopped at the same message and the one with the flag carried all rows; with BYPASSRLS the plain dump did. how to export one tenant on purpose is in pg_dump for one tenant.
Can a read-only user see other databases?
It can connect to them. CONNECT is granted to PUBLIC on every new database, and roles belong to the cluster, so the read-only role we created for one database opened a session in another, listed its catalog (510 entries in pg_class) and created a temporary table there. It could not read the application tables in it: SELECT grants are per database.
revoke connect on database … from public, followed by a grant to the roles that should connect, closed it: FATAL: permission denied for database "rodb", with the detail User does not have CONNECT privilege. The revoke is per database, and a database created later starts with CONNECT for PUBLIC again.
What we did not measure
Managed services, where the administrator role is not a superuser and some of these grants are pre-arranged. A read-only role on a streaming replica, where writes fail for every role. Whether a long read-only transaction holds back vacuum on a busy table; what a job that holds its transaction costs is in our article on job workers. Column-level grants for a reporting role; permission denied for table has a short section on how those fail. Versions other than 17.11.
Sources: PostgreSQL 17 documentation, Predefined Roles and Privileges, read on 13 October 2026.