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