From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kristo Marijo <M.Kristo@rewe-group.at>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18389: pg_database_owner not recognized with alter default privileges
Date: Wed, 13 Mar 2024 10:07:30 +0100
Message-ID: <27c4e95e0c3472ef291f6ba72ef403c3d2076451.camel@cybertec.at> (raw)
In-Reply-To: <VI1PR06MB6398EA3A079649BA9AD25CF6CD2A2@VI1PR06MB6398.eurprd06.prod.outlook.com>
References: <18389-8ad7f569523f579b@postgresql.org>
<5367558c2cfc467d6462c8962c57282bba9c0321.camel@cybertec.at>
<VI1PR06MB6398EA3A079649BA9AD25CF6CD2A2@VI1PR06MB6398.eurprd06.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, M.Kristo@rewe-group.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18389: pg_database_owner not recognized with alter default privileges
In-Reply-To: <27c4e95e0c3472ef291f6ba72ef403c3d2076451.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox