PostgreSQL: why altering default privileges in specific schema to revoke all privileges on types from PUBLIC does nothing?

Question

The question can pretty much translate into: why the following PostgreSQL command does nothing?

ALTER DEFAULT PRIVILEGES
    FOR ROLE ownerrole
    IN SCHEMA myschema
    REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;

It is a valid command which is executed successfully but it has no impact. Indeed, USAGE privileges is still granted to PUBLIC on domains created after the command has been executed. Nothing changed and the PostgreSQL default privileges still apply:

PostgreSQL grants default privileges on some types of objects to PUBLIC. […] the default privileges granted to PUBLIC are as follows: […] USAGE privilege for languages and data types (including domains).

Source: https://www.postgresql.org/docs/current/static/sql-grant.html

Testing

Setup

Create roles as a superuser

CREATE ROLE ownerrole WITH INHERIT LOGIN PASSWORD 'ownerrole';
CREATE ROLE approle WITH INHERIT LOGIN PASSWORD 'approle';

Create database test1 as a superuser

CREATE DATABASE test1 ENCODING = 'UTF8' OWNER = ownerrole;

Create database test2 as a superuser

CREATE DATABASE test2 ENCODING = 'UTF8' OWNER = ownerrole;

Test 1

Connect to test1 as ownerrole and execute these commands:

CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO ownerrole;
GRANT ALL PRIVILEGES ON SCHEMA myschema TO approle;

ALTER DEFAULT PRIVILEGES
    FOR ROLE ownerrole
    IN SCHEMA myschema
    REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;

CREATE DOMAIN myschema.comment AS text;
ALTER DOMAIN myschema.comment OWNER TO ownerrole;

Now, connect to test1 as approle and execute the command:

CREATE TABLE myschema.table(
    comment myschema.comment
);

Unfortunately, this execute successfully and a check with psql \ddp command shows nothing:

$ psql -d test1 -h localhost -p 5432 -U ownerrole
psql (10.3 (Ubuntu 10.3-1.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test1=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

test1=> \q

Test 2

This new test is identical to the first one except that the ALTER DEFAULT PRIVILEGES is not specific to any schema.

Connect to test2 as ownerrole and execute these commands:

CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO ownerrole;
GRANT ALL PRIVILEGES ON SCHEMA myschema TO approle;

ALTER DEFAULT PRIVILEGES
    FOR ROLE ownerrole
    REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;

CREATE DOMAIN myschema.comment AS text;
ALTER DOMAIN myschema.comment OWNER TO ownerrole;

Now, connect to test2 as approle and execute the command:

CREATE TABLE myschema.table(
    comment myschema.comment
);

This time, the command fails as expected

ERROR:  permission denied for type myschema.comment
SQL state: 42501

And a check with psql \ddp command shows the altered default privileges:

$ psql -d test2 -h localhost -p 5432 -U ownerrole
psql (10.3 (Ubuntu 10.3-1.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test2=> \ddp
             Default access privileges
   Owner   | Schema | Type |   Access privileges
-----------+--------+------+-----------------------
 ownerrole |        | type | ownerrole=U/ownerrole
(1 row)

test2=> \q

Answer 1

Because it is the way PostgreSQL works.

This was not clear at the time the question was posted but this was answered in the BUG #16124: Altering default privileges problem pgsql-bugs thread:

Altering default privileges in specific schema to revoke all privileges on types from PUBLIC does nothing.

AFAICT, this is operating as designed. SetDefaultACL() says:

 * The default for a global entry is the hard-wired default ACL for the
 * particular object type.  The default for non-global entries is an empty
 * ACL.  This must be so because global entries replace the hard-wired
 * defaults, while others are added on.

Hence, "ALTER ... IN SCHEMA whatever REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC" is a no-op because the privilege was never granted at the schema level in the first place.

This seems kind of unfortunate, and it's certainly not adequately documented. The man page does say "Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type" but the implication that you can't revoke privileges at that level isn't obvious.

I don't see any way to change the actual behavior, at least not within the existing representation of pg_default_acl, but we ought to improve the documentation. I'm inclined to split out the just-quoted sentence to a new para along the lines of

Default privileges that are specified per-schema are added to whatever
the global default privileges are for the particular object type.
This means you cannot revoke privileges per-schema if they are granted
globally (either by default, or according to a previous ALTER DEFAULT
PRIVILEGES command that did not specify a schema).  Per-schema REVOKE
is only useful to reverse the effects of a previous per-schema GRANT.

PostgreSQL documentation now mentions this:

Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type. This means you cannot revoke privileges per-schema if they are granted globally (either by default, or according to a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema). Per-schema REVOKE is only useful to reverse the effects of a previous per-schema GRANT.