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?