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

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(at)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(at)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(dot)albe(at)cybertec(dot)at>
Gesendet: Mittwoch, 13. März 2024 10:07
An: Kristo Marijo <M(dot)Kristo(at)rewe-group(dot)at>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)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(dot)albe(at)cybertec(dot)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