Re: BUG #18389: pg_database_owner not recognized with alter default privileges

On Tue, 2024-03-12 at 19:04 +0000, PG Bug reporting form wrote:
> i am precreating schemas in template1 database and setting specific
> privileges.
> When performing "alter default privileges for role pg_database_owner ..."
> the group is recognized and the default privilege is also set. No error is
> thrown.
>
> When i create a new database the pg_database_owner role is not working
> correctly. I created then the same privilege with specifying the db owner
> and then the privilege works.

It is working here:

ALTER DEFAULT PRIVILEGES FOR ROLE pg_database_owner
GRANT SELECT ON TABLES TO laurenz;

SET ROLE pg_database_owner;

CREATE TABLE mumble ();

\dp mumble
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies
════════╪════════╪═══════╪═════════════════════════════════════════════╪═══════════════════╪══════════
public │ mumble │ table │ pg_database_owner=arwdDxt/pg_database_owner↵│ │
│ │ │ laurenz=r/pg_database_owner │ │
(1 row)

Note that you have to be role "pg_database_owner" (see the SET ROLE statement).
It is not sufficient to be the owner of the database (the only implicit member
of that role).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com