GLOBAL DEFAULT privileges always seen as changed

[dani]

What do you want?

I'm trying to manage all my postgres permissions with ldap2pg. It's a simple use case, one database = one role with full privs.

Things seem to work, but the GLOBAL DEFAULT privileges are always printed as being modified by ldap2pg, while I'd expect it to be changed only on first run

ldap2pg.yml

Here's a simplified ldap2pg config with only the relevant part, and with which I can reproduce the problem

ldap2pg.yml

postgres:               
  managed_roles_query: |
    VALUES                      
      ('public'),               
      ('managed_roles')                               
                                                      
    UNION                                                           
                                                                    
    SELECT DISTINCT role.rolname                                    
    FROM pg_roles AS role                                           
    JOIN pg_auth_members AS ms ON ms.member = role.oid              
    JOIN pg_roles AS parent                                         
      ON parent.rolname = 'managed_roles' AND parent.oid = ms.roleid
    ORDER BY 1;

privileges:                                                         
  owner:                                                            
    - writer                                                        
    - __create_on_schemas__                                         
    - __truncate_on_tables__                                        
  reader:                   
    - __connect__           
    - __usage_on_schemas__  
    - __select_on_tables__  
    - __select_on_sequences__
    - __usage_on_sequences__ 
  writer:                    
    - reader                 
    - __temporary__          
    - __insert_on_tables__   
    - __update_on_tables__   
    - __delete__on_tables__ 
    - __update_on_sequences__
    - __execute_on_functions__
    - __trigger_on_tables__   
version: 6                    
                              
                              
rules:                        
  - roles:                    
      name: managed_roles  
      options: NOLOGIN   
  - roles:               
      name: vaultwarden  
      options: LOGIN     
      parents:           
        - managed_roles  
  - grant:             
      databases: vaultwarden
      privileges: owner     
      role: vaultwarden

I have an empty database named vaultwarden already created.

Now, each time I execute ldap2pg --real

ldap2pg --real -c /secrets/ldap2pg.yml

/ # ldap2pg --real -c /secrets/ldap.yml 
09:37:43 INFO   Starting ldap2pg                                 version=v6.0 runtime=go1.20.5 commit=023e6933
09:37:43 INFO   Using YAML configuration file.                   path=/secrets/ldap.yml
09:37:44 INFO   Running as superuser.                            user=postgres super=true server="PostgreSQL 15.4" cluster=postgres database=postgres
09:37:44 INFO   Real mode. Postgres instance will modified.     
09:37:44 INFO   All roles synchronized.                         
09:37:44 INFO   All privileges configured.                       database=postgres
09:37:44 INFO   All default privileges configured.               database=postgres
09:37:44 INFO   All privileges configured.                       database=template1
09:37:44 INFO   All default privileges configured.               database=template1
09:37:44 INFO   All privileges configured.                       database=test
09:37:44 INFO   All default privileges configured.               database=test
09:37:44 INFO   All privileges configured.                       database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden TRUNCATE ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden TRUNCATE ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden EXECUTE ON FUNCTIONS TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden EXECUTE ON FUNCTIONS TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden INSERT ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden INSERT ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden TRIGGER ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden TRIGGER ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden UPDATE ON SEQUENCES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden UPDATE ON SEQUENCES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden UPDATE ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden UPDATE ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden SELECT ON SEQUENCES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden SELECT ON SEQUENCES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden SELECT ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden SELECT ON TABLES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden USAGE ON SEQUENCES TO vaultwarden" database=vaultwarden
09:37:44 CHANGE Grant privilege.                                 grant="GLOBAL DEFAULT FOR vaultwarden USAGE ON SEQUENCES TO vaultwarden" database=vaultwarden
09:37:44 INFO   Comparison complete.                             elapsed=340.914337ms mempeak=0.7MiB postgres=18.210816ms queries=18 ldap=0s searches=0

And I can run ldap2pg as many times I want, it'll always print those GLOBAL DEFAULT as CHANGE.

Am I missing something or is there a bug somewhere ?

[bersace]

Hi @dani , can you share verbose output ? Use --verbose to get it.

[dani]

Here it is ldap2pg.log.txt

[bersace]

Hi, I confirm that grants to self are not inspected. Actually, access to own objects is implicit. Why do you want to grant select on self ?

[dani]

Self ? I'm trying to GRANT privileges on the vaultwarden database (which has been created by the postgres user, and so is owned by postgres) to the vaultwarden role. Am I doing it wrong ?

[bersace]

This:

ALTER DEFAULT PRIVILEGES FOR ROLE "vaultwarden" GRANT USAGE ON SEQUENCES TO "vaultwarden";

This query configures default privilege to grant usage to vaultwarden user on the sequences it will create itself. This is granting to self on own objects.

[dani]

OK. But I only used builtin privileges and a simple grant rule. I guess it has to do the the default scope of the builtin privileges (which is "global" ?). Should I create my own privileges with default: schema instead of using the builtin ones ?

[bersace]

hmm, I think ldap2pg should skip configure default privileges for self. Let's fix this.

[bersace]

I think I got the point.

Default privileges on self are the hard-wire value of pg_default_acl.

Thus, granting on self is a noop. pg_default_acl is always empty.

> ALTER DEFAULT PRIVILEGES FOR ROLE "alizée" GRANT TRUNCATE ON TABLES TO "alizée" ;
ALTER DEFAULT PRIVILEGES
> select * from pg_default_acl ;
 oid │ defaclrole │ defaclnamespace │ defaclobjtype │ defaclacl
─────┼────────────┼─────────────────┼───────────────┼───────────
(0 ligne)
> ALTER DEFAULT PRIVILEGES FOR ROLE "alizée" REVOKE TRUNCATE ON TABLES FROM "alizée" ;
ALTER DEFAULT PRIVILEGES
> select * from pg_default_acl ;
  oid  │ defaclrole │ defaclnamespace │ defaclobjtype │            defaclacl
───────┼────────────┼─────────────────┼───────────────┼──────────────────────────────────
 16443 │      16398 │               0 │ r             │ {"\"alizée\"=arwdxt/\"alizée\""}
(1 ligne)

ldap2pg failed to determine that the privilege is effectively granted.

There is no acldefault for default privileges. ldap2pg failed to return the hard-wired default privileges.