Обсуждение: BUG #18389: pg_database_owner not recognized with alter default privileges

Поиск
Список
Период
Сортировка

BUG #18389: pg_database_owner not recognized with alter default privileges

От
PG Bug reporting form
Дата:
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

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

От
Noah Misch
Дата:
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.

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

От
Kristo Marijo
Дата:
Hello,

here is the exact sql-command.

ALTER DEFAULT PRIVILEGES IN SCHEMA general,extensions for role postgres,apl_admin,pg_database_owner grant SELECT ON TABLES to app_readonly;

BR
Marijo Kristo
Von: Noah Misch <noah@leadboat.com>
Gesendet: Dienstag, 12. März 2024 20:55
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, 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.

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

От
Laurenz Albe
Дата:
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

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

От
Kristo Marijo
Дата:
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

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

От
Laurenz Albe
Дата:
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

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

От
Kristo Marijo
Дата:
Thank you for the quick and efficient response.

I have tested it and it is as you said.
I though the pg_database_owner is a placeholder for the current db_owner ..

Thanks for the clarification !


\c template1

ALTER DEFAULT PRIVILEGES IN SCHEMA general,extensions for role pg_database_owner grant SELECT ON TABLES to app_readonly;

create user db_admin password '12345';

create USER readwrite_user password '12345';
GRANT app_readonly TO app_readwrite with Admin option;
grant app_readwrite to readwrite_user;

create database testdb owner db_admin;
grant connect on database testdb to readwrite_user;

postgres@autpgc16demo01-development-1:/$ psql -h localhost -U db_admin -d testdb
Password for user db_admin:
psql (16.2 (Debian 16.2-1.pgdg110+2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testdb=> create table general.mumble();
CREATE TABLE
testdb=> \dp general.mumble;
                              Access privileges
 Schema  |  Name  | Type  | Access privileges | Column privileges | Policies
---------+--------+-------+-------------------+-------------------+----------
 general | mumble | table |                   |                   |
(1 row)

testdb=> set role pg_database_owner;
SET
testdb=> create table general.mumble2();
CREATE TABLE
testdb=> \dp general.mumble2;
                                           Access privileges
 Schema  |  Name   | Type  |              Access privileges              | Column privileges | Policies
---------+---------+-------+---------------------------------------------+-------------------+----------
 general | mumble2 | table | pg_database_owner=arwdDxt/pg_database_owner+|                   |
         |         |       | app_readonly=r/pg_database_owner            |                   |
(1 row)

testdb=> exit
could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory
postgres@autpgc16demo01-development-1:/$ psql -h localhost -U readwrite_user -d testdb
Password for user readwrite_user:
psql (16.2 (Debian 16.2-1.pgdg110+2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testdb=> select * from general.mumble;
ERROR:  permission denied for table mumble
testdb=> select * from general.mumble2;
--
(0 rows)

Von: Laurenz Albe <laurenz.albe@cybertec.at>
Gesendet: Mittwoch, 13. März 2024 10:07
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 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