Default privileges for self cannot be ignored when multiple sets are present

[mhagander]

It looks to me that #615 is incorrect, in that it does not create a default privilege for the owner role itself. But this is needed if there are other default privileges granted. Example:

First, no default privileges on role:

postgres=# \ddp user1
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

Second, let's grant all to a different user. Note how PostgreSQL in this case implicitly adds the default privilege to the owning role itself:

postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE user1 GRANT ALL ON TABLES TO user2;
ALTER DEFAULT PRIVILEGES
postgres=# \ddp
            Default access privileges
 Owner | Schema | Type  |    Access privileges    
-------+--------+-------+-------------------------
 user1 |        | table | user1=arwdDxt/user1    +
       |        |       | user2=arwdDxt/user1

But let's remove those. And in fact, this is what ldap2pg does today AFAICT (since it removes all privileges granted to the same role, by excluding it from the list of wanted ones. As soon as I grant them back explicitly to user1, ldap2 removes them again).

postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE user1 REVOKE ALL ON TABLES FROM user1;
ALTER DEFAULT PRIVILEGES
postgres=# \ddp
            Default access privileges
 Owner | Schema | Type  |    Access privileges    
-------+--------+-------+-------------------------
 user1 |        | table | user2=arwdDxt/user1

At this point, user1 no longer gets permissions on its own tables:

postgres=# SET ROLE user1;
SET
postgres=> CREATE TABLE testschema.testtable(a int);
CREATE TABLE
postgres=> SELECT * FROM testschema.testtable;
ERROR:  permission denied for table testtable

If the default privileges are not granted to user2 it all works because a NULL default ACL gives user1 permissions. But when I grant privileges to user2, it also requires the privileges to user1 to remain.

[bersace]

Thanks Magnus ! That's an obscur part of default privileges that requires such use.

I'll fix this in next release. We plan a new ldap2pg release in a few weeks with PostgreSQL 18 supprt.

In the meantime, you may workaround this by granting reader group to owner or handle default privileges manually.