Re: BUG #16124: Altering default privileges problem

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> Bug reference: 16124
> Logged by: John Doe
> Email address: john(dot)doe(at)example(dot)com

... so, effectively an anonymous report. Hope the submitter reads
pgsql-bugs, else he'll get no answer.

> 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.

Perhaps an explicit example too?

regards, tom lane