permission denied for table

What it means

PostgreSQL checks privileges object by object. The role you’re connected as (or switched to with SET ROLE) doesn’t have the privilege this statement needs on the object named in the message, so the statement didn’t run. The SQLSTATE is 42501 (insufficient_privilege).

The object type tells you what’s missing:

To read a table you need both: USAGE on its schema and SELECT on the table. The schema is checked first, so you can fix one error and meet the next.

Common causes

1. Nothing was granted to a new role. Creating a role gives it no access to existing tables.
2. The table is newer than the grant. GRANT … ON ALL TABLES IN SCHEMA covers the tables that exist when you run it, not ones created later.
3. USAGE on the schema is missing, so table grants don’t help yet.
4. A serial column. Inserting calls nextval() on its sequence, which needs its own grant. Identity columns (GENERATED … AS IDENTITY) don’t.
5. PostgreSQL 15 or later, and schema public. Ordinary roles can no longer create tables in public by default.
6. The statement changes structure. Migrations run as an app role that isn’t the table’s owner fail with must be owner of table.

How to fix it

See what the role has

SELECT has_schema_privilege('<role>', 'app', 'USAGE')            AS schema_usage,
       has_table_privilege('<role>', 'app.orders', 'SELECT')     AS can_select,
       has_table_privilege('<role>', 'app.orders', 'UPDATE')     AS can_update;

In psql, \dp app.orders lists a table’s grants. In its output, r is SELECT, a is INSERT, w is UPDATE and d is DELETE.

Grant what’s missing

As the owner or a superuser:

GRANT USAGE ON SCHEMA app TO <role>;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO <role>;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO <role>;

Grant only what the role needs: a reporting role needs SELECT, not the rest.

Cover tables created later

ALTER DEFAULT PRIVILEGES FOR ROLE <owner> IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <role>;
ALTER DEFAULT PRIVILEGES FOR ROLE <owner> IN SCHEMA app
  GRANT USAGE ON SEQUENCES TO <role>;

Default privileges apply to objects created by <owner> (the role your migrations run as), from now on. They don’t change existing tables, and tables created by a different role don’t get them.

For read-only access to everything, PostgreSQL 14 and later have a built-in role:

GRANT pg_read_all_data TO <role>;

It acts like SELECT on every table and USAGE on every schema, but doesn’t bypass row-level security.

Schema public in PostgreSQL 15 and later

PostgreSQL 15 removed the CREATE privilege on schema public from everyone (PUBLIC), and made the database’s owner (through the pg_database_owner role) the owner of public. An app role that could create its tables in public on PostgreSQL 14 gets permission denied for schema public on a new PostgreSQL 15 database. The change applies to new clusters and new databases; a cluster upgraded with pg_upgrade, or a restored dump, keeps the old permissions.

Pick one fix:

-- Give the app its own schema (the cleanest).
CREATE SCHEMA app AUTHORIZATION <role>;

-- Or make the app role the database owner, which makes it owner of public.
ALTER DATABASE <database> OWNER TO <role>;

-- Or grant the old behaviour back for this role.
GRANT CREATE ON SCHEMA public TO <role>;

With its own schema, put it first on the role’s search path: ALTER ROLE <role> SET search_path = app, public;.

“must be owner of table”

Run migrations as the role that owns the tables, or transfer ownership:

ALTER TABLE app.orders OWNER TO <migration role>;

Reproduce it

On PostgreSQL 18.6, as a superuser: a table with a serial column, and a new role with nothing granted. SET ROLE switches to that role. With \set VERBOSITY verbose (LOCATION lines trimmed):

SET ROLE seo_pgconn_app;
SELECT * FROM seo_pgconn.orders;
ERROR:  42501: permission denied for schema seo_pgconn
LINE 1: SELECT * FROM seo_pgconn.orders;
                      ^

After GRANT USAGE ON SCHEMA seo_pgconn TO seo_pgconn_app:

ERROR:  42501: permission denied for table orders

After GRANT SELECT, INSERT ON seo_pgconn.orders TO seo_pgconn_app, an insert needs the sequence:

INSERT INTO seo_pgconn.orders (total) VALUES (10);
ERROR:  42501: permission denied for sequence orders_id_seq

After GRANT USAGE ON SEQUENCE seo_pgconn.orders_id_seq, the insert works; an update and a schema change still fail:

UPDATE seo_pgconn.orders SET total = 11;
ERROR:  42501: permission denied for table orders
ALTER TABLE seo_pgconn.orders ADD COLUMN note text;
ERROR:  42501: must be owner of table orders

GRANT SELECT ON ALL TABLES IN SCHEMA seo_pgconn didn’t cover a table created a moment later (permission denied for table refunds); after ALTER DEFAULT PRIVILEGES IN SCHEMA seo_pgconn GRANT SELECT ON TABLES TO seo_pgconn_app, the next new table was readable.

The PostgreSQL 15 change. In a new database on each server, as a role with no grants:

SET ROLE seo_pgconn_app;
CREATE TABLE public.seo_t (id int);

PostgreSQL 14.24 creates the table. PostgreSQL 15.19 and 18.6 refuse:

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.seo_t (id int);
                     ^

The schema’s owner and privileges show the difference:

-- PostgreSQL 14.24
 nspname | owner |           nspacl           
---------+-------+----------------------------
 public  | inlet | {inlet=UC/inlet,=UC/inlet}

-- PostgreSQL 15.19
 nspname |       owner       |                            nspacl                             
---------+-------------------+---------------------------------------------------------------
 public  | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}

=UC means everyone (PUBLIC) has USAGE and CREATE; =U means USAGE only. On 15.19, both GRANT CREATE ON SCHEMA public TO seo_pgconn_app and making that role the database’s owner let it create the table.

In Inlet

Inlet’s roles and grants view shows what each role can do, so you can see which privilege is missing. In the query editor, the error appears at the position the server reports, and Ask Claude (with your own Anthropic API key) can explain it and write the GRANT you need.