postgres: must be owner of / permission denied for schema (tested)

ERROR: must be owner of table X and ERROR: permission denied for schema X are two different permission failures that look similar. One is about who owns an object; the other is about whether your role can even see inside a schema. We reproduced both on PostgreSQL 16.15 with two real roles and measured the exact fix for each.

1. ERROR: must be owner of table X

We created a table as role alice, then tried to modify it as a different role, bob:

-- as alice
CREATE TABLE secrets (id int);

-- as bob
ALTER TABLE secrets ADD COLUMN x int;
ERROR:  must be owner of table secrets

DROP TABLE secrets;
ERROR:  must be owner of table secrets

Why: structural changes to a table — ALTER TABLE, DROP TABLE, TRUNCATE in some configurations, ownership/permission changes — are restricted to the table's owner (or a superuser), regardless of what SELECT/INSERT/UPDATE grants bob has on it. This is deliberate: anyone could otherwise drop a table they only had read access to.

The fix we confirmed, run by a superuser or the current owner:

ALTER TABLE secrets OWNER TO bob;

After that single statement, bob's identical ALTER TABLE secrets ADD COLUMN x int; succeeded. If you don't actually want to transfer ownership (e.g. a shared service role should own everything, not individual users), the alternative is running the DDL as the actual owner or a superuser instead of changing ownership.

2. ERROR: permission denied for schema X

We created a second schema as alice with a table inside it, then had bob — who has no grants on that schema at all — try to use it:

-- as alice
CREATE SCHEMA priv_schema;
CREATE TABLE priv_schema.t2 (id int);

-- as bob
SELECT * FROM priv_schema.t2;
ERROR:  permission denied for schema priv_schema

CREATE TABLE priv_schema.t3 (id int);
ERROR:  permission denied for schema priv_schema

Why: a schema is itself a permissioned object, separate from the tables inside it. bob was blocked before Postgres even checked table-level grants — without USAGE on the schema, a role can't look inside it at all, even to find out a table exists. This is the single most common cause of "permission denied" surprises on a fresh role: table-level GRANT SELECT alone does nothing without schema-level USAGE too.

The fix we confirmed, run by the schema owner or a superuser:

GRANT USAGE ON SCHEMA priv_schema TO bob;
GRANT SELECT ON priv_schema.t2 TO bob;

Both grants were required — we tested that USAGE on the schema alone is not sufficient for bob to read t2's rows; the table-level SELECT grant above it is still checked separately. After both, bob's SELECT * FROM priv_schema.t2; returned successfully (0 rows, as expected for an empty table). For every table a role needs now and in the future inside a schema, consider ALTER DEFAULT PRIVILEGES IN SCHEMA priv_schema GRANT SELECT ON TABLES TO bob; so new tables don't repeat this error.

3. A related error we hit setting this up: CREATE DATABASE cannot run inside a transaction block

While preparing the test roles, running CREATE ROLE alice ...; CREATE ROLE bob ...; CREATE DATABASE testdb OWNER alice; as one multi-statement psql -c call failed:

ERROR:  CREATE DATABASE cannot run inside a transaction block

psql's -c flag wraps multiple semicolon-separated statements in a single implicit transaction, and CREATE DATABASE (like a few other commands: CREATE TABLESPACE, DROP DATABASE, VACUUM) refuses to run inside any transaction block, even an implicit one — and the whole batch rolled back, silently undoing the two CREATE ROLE statements too. Running each statement as its own separate psql -c call (or outside an explicit BEGIN) fixed it immediately.

Frequently asked questions

What's the difference between 'must be owner of table' and 'permission denied for schema'?

They check different layers. 'Permission denied for schema' means your role lacks USAGE on the containing schema, so Postgres won't even look at the table — it's checked first. 'Must be owner of table' means you can see and even query the table, but structural changes (ALTER, DROP) are reserved for its owner or a superuser regardless of your other grants. We reproduced both with two distinct roles on PostgreSQL 16.15.

I granted SELECT on the table but my role still can't query it — why?

You likely granted the table-level privilege but not schema-level USAGE. We confirmed both grants are required together: GRANT USAGE ON SCHEMA <schema> TO <role>, plus GRANT SELECT ON <schema>.<table> TO <role>. Without USAGE, the table-level grant alone does nothing — the role can't even resolve the schema to find the table.

Does granting SELECT on a schema's existing tables cover new tables created later?

No — grants in Postgres are not retroactive by default. A table created after your GRANT statement needs its own grant. Use ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <role> (run by the role that will own future tables) so new tables automatically inherit the grant.

Can I avoid 'must be owner of table' without transferring ownership to the other role?

Yes — run the DDL (ALTER TABLE, DROP TABLE, etc.) as the actual table owner or a superuser instead of ALTER TABLE ... OWNER TO. We only used OWNER TO here because the goal was to let bob run DDL himself going forward; if that's not the goal, just execute the statement under the owning role's connection.

Why did CREATE DATABASE fail when I ran it together with other CREATE statements via psql -c?

psql -c with multiple semicolon-separated statements runs them as one implicit transaction, and CREATE DATABASE refuses to execute inside any transaction block — we measured the exact error, and that the whole batch (including unrelated CREATE ROLE statements before it) rolled back as a result. Run CREATE DATABASE as its own separate statement/connection.

Written with AI assistance. Measured on 2026-10-03 with PostgreSQL 16.15 (Alpine) in a disposable Docker container, using psql with SET SESSION AUTHORIZATION to act as two distinct non-superuser roles against the same database. Every message and SQL below is copy-pasted output, not paraphrased. PlainHeaders is our extension; PostgreSQL is free.