Thread

1. BUG #18389: pg_database_owner not recognized with alter default privileges

   PG Bug reporting form <noreply@postgresql.org> — 2024-03-12T19:04:11Z

   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
   
   

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

   Noah Misch <noah@leadboat.com> — 2024-03-12T19:55:17Z

   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.
   
   
   
   
   

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

   Kristo Marijo <m.kristo@rewe-group.at> — 2024-03-13T07:16:53Z

   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.
   
   

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

   Laurenz Albe <laurenz.albe@cybertec.at> — 2024-03-13T08:44:16Z

   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
   

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

   Kristo Marijo <m.kristo@rewe-group.at> — 2024-03-13T08:47:24Z

   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
   

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

   Laurenz Albe <laurenz.albe@cybertec.at> — 2024-03-13T09:07:30Z

   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
   

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

   Kristo Marijo <m.kristo@rewe-group.at> — 2024-03-13T09:22:42Z

   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