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