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

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(dot)albe(at)cybertec(dot)at>
Gesendet: Wednesday, March 13, 2024 9:44:16 AM
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 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