ALTER DEFAULT PRIVILEGES IN SCHEMA vs ALTER DEFAULT PRIVILEGES FOR ROLE, what is the difference?

Question

What is the functional difference between these two commands?

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE ALL ON FUNCTIONS FROM old_role;

ALTER DEFAULT PRIVILEGES FOR ROLE old_role IN SCHEMA schema_name 
    REVOKE ALL PRIVILEGES ON FUNCTIONS FROM old_role

Do they achieve the same thing, or is each one specifically doing something different? If so, what?

Thanks!

Answer 1

The documentation says:

If FOR ROLE is omitted, the current role is assumed.

So the first statement affects default privileges on objects created by the user that ran the ALTER DEFAULT PRIVILEGES statement, while the second statement affects default privileges on objects created by old_role.

The first statement will undo default privileges previously granted with

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name 
   GRANT EXECUTE ON FUNCTIONS TO old_role;

while the second statement will undo privileges granted with

ALTER DEFAULT PRIVILEGES FOR ROLE old_role IN SCHEMA schema_name 
   GRANT EXECUTE ON FUNCTIONS TO old_role;

Note that both statements will do nothing unless such privileges have previously been granted:

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.