Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A read-only role gets permission denied on a table it should read

ErrorERROR: permission denied for table orders

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

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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'
  2. 2Match what you see, then jump to its fix

    1. 1Other tables of the role work; this one fails, and \dp shows no entry for the role.SELECT was never granted on the tableFix
    2. 2Every table in the schema fails, even ones with SELECT granted.The role has no USAGE on the schemaFix
    3. 3Old tables work and new tables fail, after a migration or a new deploy.The table was created after the grant ranFix

What does the error look like?

  • ERROR: permission denied for table orders
  • ERROR: 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.