ERROR: role "postgres_admin" already exists (PG::DuplicateObject)

[larskanis]

Chris Roberts asked per mail:

First off thank you for creating this tool. and thank you in advance for any help.

I've been tasked with standing up a PostgreSQL instance and integrate it with our AD. The initial sync worked just fine. However when we re-run pg_ldap_sync we run into an error. I've sanitized the .yaml file and the error we're getting. There is not a lot of documentation out there for this addon and would appreciate any help.

We have a group made for postgres (postgress_admin). First time running pg_ldap_sync the group (along with users) synced with postgres. However additional runs result in the error below

I, [2018-03-12T15:20:27.452143 #23276]  INFO -- : SQL: CREATE ROLE "postgres_admin" NOLOGIN
/var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:217:in `exec': ERROR:  role "postgres_admin" already exists (PG::DuplicateObject)
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:217:in `pg_exec_modify'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:228:in `create_pg_role'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:237:in `block in sync_roles_to_pg'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:236:in `each'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:236:in `sync_roles_to_pg'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:332:in `start!'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/lib/pg_ldap_sync/application.rb:353:in `run'
        from /var/lib/gems/2.3.0/gems/pg-ldap-sync-0.1.1/bin/pg_ldap_sync:6:in `<top (required)>'
        from /usr/local/bin/pg_ldap_sync:23:in `load'
        from /usr/local/bin/pg_ldap_sync:23:in `<main>'

pg_ldap_sync (1).txt

[larskanis]

If the second run tries to create a user or group twice, then is the filter wrong for retrieving the users/groups from the database. In you case pg_group => create_option sets NOLOGIN but not SUPERUSER. On the other hand side, the query retrieves only groups which are superusers, so that the created group is not recognized.

In general it's better to use the sample-config2 as a base point. This way you can distinguish between AD and non-AD users without mis-using postgres-internal attributes.

[larskanis]

This is fixed in version 0.2.0