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

On Wed, 2024-03-13 at 08:47 +0000, Kristo Marijo wrote:
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> > 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).
>
> yes privileges are displayed correctly. But did you try to actually select from this table with the user?
>
> This did not work for me, because there is some internal stuff going on and its not correctly
> "replacing" the pg_database_owner with the db owner.

It works like a charm:

  RESET ROLE;

  SET ROLE laurenz;

  TABLE mumble;
  ══
  (0 rows)

There is no replacing going on anywhere, nor should there be.

Yours,
Laurenz Albe