PostgreSQL Permissions – Implicit Privileges for Schema Owner

permissionspostgresql

I am confused about schema owners in Postgres.

When I create a schema with the owner defined by the AUTHORIZATION keyword, owner can create objects in that schema (no explicit privileges are shown on that schema in pgAdmin4).

But when I alter the schema owner to another role, this role cannot create objects in that schema (until I explicitly grant USAGE and CREATE).

I suppose, that schema owner has implicit privileges to do anything with schema. What is the right way to change schema ownerschip?

Best Answer

I suppose, that schema owner has implicit privileges to do anything with schema.

No, that is not the case. The manual on GRANT:

PostgreSQL allows an object owner to revoke their own ordinary privileges: for example, a table owner can make the table read-only to themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE privileges. This is not possible according to the SQL standard. The reason is that PostgreSQL treats the owner's privileges as having been granted by the owner to themselves; therefore they can revoke them too. In the SQL standard, the owner's privileges are granted by an assumed entity "_SYSTEM". Not being "_SYSTEM", the owner cannot revoke these rights.

Bold emphasis mine.

There are also no default privileges for public:

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. (...)

So even the owner needs privileges for a schema - which are given by default! If all you do is:

CREATE SCHEMA test AUTHORIZATION dummy;

Or:

CREATE SCHEMA test;

Then default privileges are NULL, i.e. default to system defaults (which is USAGE and CREATE for the owner of a schema). The manual on GRANT:

If the "Access privileges" column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the specified request. Similarly, entries are shown in "Column access privileges" only for columns with nondefault privileges. (Note: for this purpose, "default privileges" always means the built-in default privileges for the object's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)

If you revoke privileges from the owner (can be done by owner himself or superuser), then the role does not have these privileges. (But the owner can always grant these privileges to himself as well.) And if you assign a new owner, the status is inherited:

-- as user postgres:
CREATE SCHEMA test1;
REVOKE ALL ON SCHEMA test1 FROM postgres;
ALTER schema test1 owner TO dummy;

You'll now see an empty array ({}) instead of NULL:

SELECT nspname AS schema, nspacl AS privileges
FROM   pg_namespace
WHERE  nspname LIKE 'ow%';

(pgAdmin4 reads from the same system table.)

If you did not explicitly revoke USAGE and CREATE, then the new owner has these privileges, too. That's what's confusing in your description. Did you revoke privileges from the old or new owner?

Related Question