A read-only role gets permission denied on a table it should read
ERROR: permission denied for table ordersShort answer
Reading a table needs two grants: USAGE on its schema and SELECT on the table itself. A GRANT on all tables covers only the tables that exist when it runs, so tables created later stay closed. Grant both, then use ALTER DEFAULT PRIVILEGES for future tables.
Fix it fast
1Find out which cause you have
See what the role may do on that table, and who owns it. An empty Access privileges column means only the defaults apply, so the owner alone can use it.
psql -X -d mydb -c '\dp public.orders'2Match what you see, then jump to its fix
- 1
Other tables of the role work; this one fails, and \dp shows no entry for the role.SELECT was never granted on the tableFix - 2
Every table in the schema fails, even ones with SELECT granted.The role has no USAGE on the schemaFix - 3
Old tables work and new tables fail, after a migration or a new deploy.The table was created after the grant ranFix
- 1
What does the error look like?
ERROR: permission denied for table ordersERROR: permission denied for relation orders
Cause 1 of 3
SELECT was never granted on the table
1Check
When a table is created, only its owner (or a superuser) can do anything with it. A role reads it only after a grant.
psql -X -d mydb -c '\dp public.orders'2Fix
Grant SELECT on the table, or on every existing table in the schema.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;3Verify
Connect as that role and read a row.
psql -X -U reporting -d mydb -c 'SELECT 1 FROM public.orders LIMIT 1;'Confirmed by postgresql.org
Cause 2 of 3
The role has no USAGE on the schema
1Check
USAGE on a schema lets the role look up objects inside it, and the objects' own privileges must be met as well. Without it, a table grant is not enough.
psql -X -d mydb -c '\dn+'2Fix
Grant USAGE on the schema.
GRANT USAGE ON SCHEMA public TO reporting;3Verify
The same SELECT now returns rows.
psql -X -U reporting -d mydb -c 'SELECT count(*) FROM public.orders;'Confirmed by postgresql.org
Cause 3 of 3
The table was created after the grant ran
1Check
ALTER DEFAULT PRIVILEGES sets what objects created in the future receive. It does not change tables that already exist, and it applies to objects created by the named role.
psql -X -d mydb -c '\ddp'2Fix
Set a default for the role that creates the tables (the migration user), and grant on the tables already there.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT SELECT ON TABLES TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;3Verify
Create a test table as the owner and read it as the read-only role.
psql -X -d mydb -c '\ddp'Confirmed by postgresql.org
How do I stop it coming back?
- Put the grants and the default privileges in the same migration that creates the role.
- Always run ALTER DEFAULT PRIVILEGES for the exact role your migrations run as.
- Check access with the real read-only login after each schema change.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
A role cannot create tables in the public schema after upgrading to PostgreSQL 15
ERROR: permission denied for schema public
Since PostgreSQL 15, ordinary users no longer have CREATE on the public schema in new databases.
3 causes, each with a fixDatabases
INSERT fails with permission denied for sequence
permission denied for sequence when calling nextval
The role has INSERT on the table but not on the sequence behind its id column.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fix