pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18389: pg_database_owner not recognized with alter default privileges
5+ messages / 4 participants
[nested] [flat]
* BUG #18389: pg_database_owner not recognized with alter default privileges
@ 2024-03-12 19:04 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2024-03-12 19:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: m.kristo@rewe-group.at
The following bug has been logged on the website:
Bug reference: 18389
Logged by: Marijo Kristo
Email address: m.kristo@rewe-group.at
PostgreSQL version: 16.2
Operating system: Debian
Description:
Hello,
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.
Please, check this. Thank you !
Br
Marijo Kristo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18389: pg_database_owner not recognized with alter default privileges
@ 2024-03-12 19:55 Noah Misch <noah@leadboat.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 5+ messages in thread
From: Noah Misch @ 2024-03-12 19:55 UTC (permalink / raw)
To: m.kristo@rewe-group.at; pgsql-bugs@lists.postgresql.org
On Tue, Mar 12, 2024 at 07:04:11PM +0000, PG Bug reporting form wrote:
> Bug reference: 18389
> Logged by: Marijo Kristo
> Email address: m.kristo@rewe-group.at
> PostgreSQL version: 16.2
> Operating system: Debian
> Description:
>
> Hello,
>
> 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.
Please share the exact SQL commands you used to reproduce this.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18389: pg_database_owner not recognized with alter default privileges
@ 2024-03-13 08:44 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-03-13 08:44 UTC (permalink / raw)
To: m.kristo@rewe-group.at; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18389: pg_database_owner not recognized with alter default privileges
@ 2024-03-13 08:47 Kristo Marijo <M.Kristo@rewe-group.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Kristo Marijo @ 2024-03-13 08:47 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
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.
Br
Marijo Kristo
________________________________
Von: Laurenz Albe <laurenz.albe@cybertec.at>
Gesendet: Wednesday, March 13, 2024 9:44:16 AM
An: Kristo Marijo <m.kristo@rewe-group.at>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Betreff: Re: BUG #18389: pg_database_owner not recognized with alter default privileges
*** CAUTION: This email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. If you are unsure or already opened a link or attachment please contact your local IT helpdesk. ***
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18389: pg_database_owner not recognized with alter default privileges
@ 2024-03-13 09:07 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Kristo Marijo <M.Kristo@rewe-group.at>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2024-03-13 09:07 UTC (permalink / raw)
To: Kristo Marijo <M.Kristo@rewe-group.at>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-03-13 09:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-12 19:04 BUG #18389: pg_database_owner not recognized with alter default privileges PG Bug reporting form <noreply@postgresql.org>
2024-03-12 19:55 ` Noah Misch <noah@leadboat.com>
2024-03-13 08:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-13 08:47 ` Kristo Marijo <M.Kristo@rewe-group.at>
2024-03-13 09:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
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