Same role is created multiple times - ERROR role already exists SQLSTATE 42710) - bug with 6.4.2 ? works with 6.3 #743

F-S-T · 2025-08-11T09:31:19Z

F-S-T
Aug 11, 2025

Hi,

With version 6.4.2, ldap2pg tries to create same role multiple times, so I get "ERROR role already exists (SQLSTATE 42710)".

Please find details below.

ldap2pg.yml

version: 6

postgres:
  databases_query: "SELECT datname FROM pg_catalog.pg_database WHERE NOT datistemplate;"
  #roles_blacklist_query: [postgres, pg_*, public]
  managed_roles_query: |
    SELECT rolname
    FROM pg_catalog.pg_roles
    WHERE rolname LIKE 'GRP_%'
       OR rolname LIKE 'usr_%'
    ORDER BY 1;

privileges:
  # Define `ro` privilege group with read-only grants
  ro:
  - __connect__
  - __select_on_tables__

rules:
- description: "Setup static roles and grants."
  roles:
  - names:
    - GRP_POSTGRESQL_RO
    - GRP_POSTGRESQL_RW
    options: NOLOGIN
  grant:
  - privilege: ro
    role: GRP_POSTGRESQL_RO
  - privilege: rw
    role: GRP_POSTGRESQL_RW
    
- description: "Search LDAP"
  ldapsearch:
    base: OU=GRP_POSTGRESQL,OU=Groups,DC=myad,DC=net 
    scope: sub
    filter: >
      (|
        (cn=GRP_POSTGRESQL_RO)
        (cn=GRP_POSTGRESQL_RW)
      )
  role:
    name: '{member.sAMAccountName}'
    options: LOGIN
    parent: "{cn}"

With version 6.4.2

As you can see ldap2pg wants to create roles GRP_POSTGRESQL_RO et GRP_POSTGRESQL_RW multiple times.

17:35:34 INFO   Starting ldap2pg                                 version=6.4.2 commit=07ed9548 runtime=go1.24.2 pid=1636347
17:35:34 INFO   Using YAML configuration file.                   path=/etc/postgresql/17/main/ldap2pg.yml
17:35:34 WARN   Dry run. Postgres instance will be untouched.
17:35:34 INFO   Running as superuser.                            user=postgres super=true server="PostgreSQL 17.5" cluster=17/main database=postgres
17:35:34 INFO   Connected to LDAP directory.                     uri=ldap://myad.net
17:35:34 INFO   Setup static roles and grants.
17:35:34 INFO   Search LDAP
17:35:34 WARN   Reusing unmanaged role. Ensure managed_roles_query returns all wanted roles. role=postgres_service_account
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RW database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RW database=postgres
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RW database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RW database=postgres
17:35:34 CHANGE Would Grant missing parents.                     role=postgres_service_account parents="[GRP_POSTGRESQL_RO GRP_POSTGRESQL_RW]" database=postgres
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Create role.                               role=usr_employee1 parents=[GRP_POSTGRESQL_RO] database=postgres
17:35:34 CHANGE Would Set role comment.                          role=usr_employee1 database=postgres
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RO database=postgres
17:35:34 CHANGE Would Create role.                               role=usr_employee2 parents=[GRP_POSTGRESQL_RO] database=postgres
17:35:34 CHANGE Would Set role comment.                          role=usr_employee2 database=postgres
17:35:34 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RW database=postgres
17:35:34 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RW database=postgres
17:35:34 CHANGE Would Create role.                               role=usr_employee3 parents=[GRP_POSTGRESQL_RW] database=postgres
17:35:34 CHANGE Would Set role comment.                          role=usr_employee3 database=postgres
17:35:34 CHANGE Would Grant privileges.                          grant="CONNECT ON DATABASE db_ldap_tests TO GRP_POSTGRESQL_RO" database=db_ldap_tests
17:35:34 CHANGE Would Grant privileges.                          grant="CONNECT ON DATABASE postgres TO GRP_POSTGRESQL_RO" database=postgres
17:35:34 INFO   All default privileges configured.               database=postgres
17:35:34 INFO   All privileges configured.                       database=db_ldap_tests
17:35:34 INFO   All default privileges configured.               database=db_ldap_tests
17:35:34 INFO   Comparison complete.                             searches=6 roles=6 queries=23 grants=4
17:35:34 INFO   Use --real option to apply changes.
17:35:34 INFO   Done.                                            elapsed=122.982624ms mempeak=1.2MiB ldap=7.239958ms inspect=28.958714ms sync=0s

With version 6.4.2, --real execution

As excepted I got an error:

09:33:53 INFO   Starting ldap2pg                                 version=6.4.2 commit=07ed9548 runtime=go1.24.2 pid=2058847
09:33:53 INFO   Using YAML configuration file.                   path=/etc/postgresql/17/main/ldap2pg.yml
09:33:53 INFO   Real mode. Postgres instance will be modified.
09:33:53 INFO   Running as superuser.                            user=postgres super=true server="PostgreSQL 17.5" cluster=17/main database=postgres
09:33:53 INFO   Connected to LDAP directory.                     uri=ldap://myad.net
09:33:53 INFO   Setup static roles and grants.
09:33:53 INFO   Search LDAP
09:33:53 WARN   Reusing unmanaged role. Ensure managed_roles_query returns all wanted roles. role=postgres_service_account
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RW database=postgres
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RW database=postgres
09:33:53 CHANGE Create role.                                     role=usr_employee3 parents=[GRP_POSTGRESQL_RW] database=postgres
09:33:53 CHANGE Set role comment.                                role=usr_employee3 database=postgres
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RO database=postgres
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RO database=postgres
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RW database=postgres
09:33:53 ERROR  Synchronisation error.                           err="ERREUR: le rôle « GRP_POSTGRESQL_RW » existe déjà (SQLSTATE 42710)"
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RW database=postgres
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RO database=postgres
09:33:53 ERROR  Synchronisation error.                           err="ERREUR: le rôle « GRP_POSTGRESQL_RO » existe déjà (SQLSTATE 42710)"
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RO database=postgres
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RW database=postgres
09:33:53 ERROR  Synchronisation error.                           err="ERREUR: le rôle « GRP_POSTGRESQL_RW » existe déjà (SQLSTATE 42710)"
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RW database=postgres
09:33:53 CHANGE Grant missing parents.                           role=postgres_service_account parents="[GRP_POSTGRESQL_RO GRP_POSTGRESQL_RW]" database=postgres
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RO database=postgres
09:33:53 ERROR  Synchronisation error.                           err="ERREUR: le rôle « GRP_POSTGRESQL_RO » existe déjà (SQLSTATE 42710)"
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RO database=postgres
09:33:53 CHANGE Create role.                                     role=usr_employee1 parents=[GRP_POSTGRESQL_RO] database=postgres
09:33:53 CHANGE Set role comment.                                role=usr_employee1 database=postgres
09:33:53 CHANGE Create role.                                     role=GRP_POSTGRESQL_RO database=postgres
09:33:53 ERROR  Synchronisation error.                           err="ERREUR: le rôle « GRP_POSTGRESQL_RO » existe déjà (SQLSTATE 42710)"
09:33:53 CHANGE Set role comment.                                role=GRP_POSTGRESQL_RO database=postgres
09:33:53 CHANGE Create role.                                     role=usr_employee2 parents=[GRP_POSTGRESQL_RO] database=postgres
09:33:53 CHANGE Set role comment.                                role=usr_employee2 database=postgres
09:33:53 CHANGE Grant privileges.                                grant="CONNECT ON DATABASE db_ldap_tests TO GRP_POSTGRESQL_RO" database=db_ldap_tests
09:33:53 CHANGE Grant privileges.                                grant="CONNECT ON DATABASE postgres TO GRP_POSTGRESQL_RO" database=postgres
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR GRP_POSTGRESQL_RO SELECT ON TABLES TO GRP_POSTGRESQL_RO" database=postgres
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR GRP_POSTGRESQL_RW SELECT ON TABLES TO GRP_POSTGRESQL_RW" database=postgres
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR usr_employee3 SELECT ON TABLES TO usr_employee3" database=postgres
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR usr_employee1 SELECT ON TABLES TO usr_employee1" database=postgres
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR usr_employee2 SELECT ON TABLES TO usr_employee2" database=postgres
09:33:53 INFO   All privileges configured.                       database=db_ldap_tests
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR GRP_POSTGRESQL_RO SELECT ON TABLES TO GRP_POSTGRESQL_RO" database=db_ldap_tests
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR GRP_POSTGRESQL_RW SELECT ON TABLES TO GRP_POSTGRESQL_RW" database=db_ldap_tests
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR usr_employee3 SELECT ON TABLES TO usr_employee3" database=db_ldap_tests
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR usr_employee1 SELECT ON TABLES TO usr_employee1" database=db_ldap_tests
09:33:53 CHANGE Revoke privileges.                               grant="GLOBAL DEFAULT FOR usr_employee2 SELECT ON TABLES TO usr_employee2" database=db_ldap_tests
09:33:53 ERROR  Some errors occurred. See above for more details. err="synchronization errors" count=5
09:33:53 ERROR  Run ldap2pg with --verbose to get more informations.

With version 6.3

After downgrade to version 6.3, it works as expected:

17:33:49 INFO   Starting ldap2pg                                 version=6.3 runtime=go1.23.1 commit=b0c6b313 pid=1636010
17:33:49 INFO   Using YAML configuration file.                   path=/etc/postgresql/17/main/ldap2pg.yml
17:33:49 WARN   Dry run. Postgres instance will be untouched.
17:33:49 INFO   Running as superuser.                            user=postgres super=true server="PostgreSQL 17.5" cluster=17/main database=postgres
17:33:49 INFO   Connected to LDAP directory.                     uri=ldap://myad.net
17:33:49 INFO   Setup static roles and grants.
17:33:49 INFO   Search LDAP
17:33:49 WARN   Reusing unmanaged role. Ensure managed_roles_query returns all wanted roles. role=postgres_service_account
17:33:49 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RO database=postgres
17:33:49 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RO database=postgres
17:33:49 CHANGE Would Create role.                               role=usr_employee1 parents=[GRP_POSTGRESQL_RO] database=postgres
17:33:49 CHANGE Would Set role comment.                          role=usr_employee1 database=postgres
17:33:49 CHANGE Would Create role.                               role=usr_employee2 parents=[GRP_POSTGRESQL_RO] database=postgres
17:33:49 CHANGE Would Set role comment.                          role=usr_employee2 database=postgres
17:33:49 CHANGE Would Create role.                               role=GRP_POSTGRESQL_RW database=postgres
17:33:49 CHANGE Would Set role comment.                          role=GRP_POSTGRESQL_RW database=postgres
17:33:49 CHANGE Would Create role.                               role=usr_employee3 parents=[GRP_POSTGRESQL_RW] database=postgres
17:33:49 CHANGE Would Set role comment.                          role=usr_employee3 database=postgres
17:33:49 CHANGE Would Grant missing parents.                     role=postgres_service_account parents="[GRP_POSTGRESQL_RO GRP_POSTGRESQL_RW]" database=postgres
17:33:49 CHANGE Would Grant privileges.                          grant="CONNECT ON DATABASE postgres TO GRP_POSTGRESQL_RO" database=postgres
17:33:49 INFO   All default privileges configured.               database=postgres
17:33:49 INFO   All privileges configured.                       database=db_ldap_tests
17:33:49 INFO   All default privileges configured.               database=db_ldap_tests
17:33:49 INFO   Comparison complete.                             searches=6 roles=6 queries=12 grants=4
17:33:49 INFO   Use --real option to apply changes.
17:33:49 INFO   Done.                                            elapsed=97.536341ms mempeak=1.2MiB ldap=7.700706ms inspect=29.128784ms sync=0s

Thanks,
FST

bersace · 2025-09-26T13:30:40Z

bersace
Sep 26, 2025
Maintainer

Bonjour, pouvez-vous partager les traces verbeuses ?

0 replies

bersace · 2025-09-26T13:34:10Z

bersace
Sep 26, 2025
Maintainer

Note, l'exemple 6.3 n'est pas avec --real.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Same role is created multiple times - ERROR role already exists SQLSTATE 42710) - bug with 6.4.2 ? works with 6.3 #743

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Uh oh!

Same role is created multiple times - ERROR role already exists SQLSTATE 42710) - bug with 6.4.2 ? works with 6.3 #743

Uh oh!

F-S-T Aug 11, 2025

ldap2pg.yml

With version 6.4.2

With version 6.4.2, --real execution

With version 6.3

Replies: 2 comments

Uh oh!

bersace Sep 26, 2025 Maintainer

Uh oh!

bersace Sep 26, 2025 Maintainer

F-S-T
Aug 11, 2025

bersace
Sep 26, 2025
Maintainer

bersace
Sep 26, 2025
Maintainer