Looks like nor postgres dsn nor ldap uri settings in ldappg.yml are applied

[zurikus]

Hi, I'm in process of migrating from ldap2pg 5.9 to ldap2pg 6.5 and hit an issue with currently latest available 6.5 at the moment ( version=6.5.1 commit=fe04c0e4 runtime=go1.25.1 ) on Rocky 8.10 for Postgres 18.
Look like it's not applying nor postgres dsn nor ldap uri config setting from ldap2pg.yml.

ldap2pg.yml

version: 6

ldap:
  uri: ldap://freeipa.mango.com:389
  binddn: uid=pgsqlfreeipasync,cn=users,cn=accounts,dc=mango,dc=com
  password: **********
  sasl_mech: ''

postgres:
  dsn: host=localhost user=postgres dbname=egs_core
  databases_query: [egs_core, egs_customer_limit, egs_gateway]
  managed_roles_query: |
    SELECT 'public'
    UNION
    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
        LEFT OUTER JOIN pg_auth_members AS ms ON ms.member = role.oid
        LEFT OUTER JOIN pg_roles AS ldap_roles
            ON ldap_roles.rolname = 'ldap_roles' AND ldap_roles.oid = ms.roleid
    WHERE role.rolname IN ('ldap_roles', 'acl_read', 'acl_write', 'acl_owner')
      OR ldap_roles.oid IS NOT NULL
    ORDER BY 1;
  roles_blacklist_query: [postgres, pg_*, replifoo, pgbouncer]
  schemas_query: |
    SELECT nspname FROM pg_namespace
    WHERE nspname !~ '^pg_' AND nspname <> 'information_schema';

privileges:

  fe:
    - __execute_on_all_functions__

  ro:
    - __connect__
    - __select_on_tables__
    - __default_select_on_tables__
    - __select_on_sequences__
    - __default_select_on_sequences__
    - __usage_on_schemas__

  rw:
    - __connect__
    - __usage_on_schemas__
    - __select_on_tables__
    - __default_select_on_tables__
    - __insert_on_tables__
    - __default_insert_on_tables__
    - __update_on_tables__
    - __default_update_on_tables__
    - __delete_on_tables__
    - __default_delete_on_tables__
    - __select_on_sequences__
    - __default_select_on_sequences__
    - __usage_on_sequences__
    - __default_usage_on_sequences__

  ddl:
    - __connect__
    - __all_on_schemas__
    - __all_on_sequences__
    - __all_on_tables__

rules:
  - roles:
    - name: egs_core
      options: LOGIN
    - name: egs_customer_limit
      options: LOGIN
    - name: egs_gateway
      options: LOGIN
    - names:
      - ldap_roles
      - acl_read
      - acl_write
      - acl_owner
      options: NOLOGIN

  - grant:
    - privilege: fe
      role:
      - egs_core
      schema: public
      database: egs_core
    - privilege: ro
      role:
      - acl_read
      - acl_write
      schema: __all__
    - privilege: ro
      role: egs_core
      schema: __all__
      database: egs_core
    - privilege: ro
      role: egs_customer_limit
      schema: __all__
      database: egs_customer_limit
    - privilege: ro
      role: egs_gateway
      schema: __all__
      database: egs_gateway
    - privilege: rw
      role: acl_write
      schema: public
    - privilege: ddl
      role: egs_core
      schema: public
      database: egs_core
    - privilege: ddl
      role: egs_customer_limit
      schema: public
      database: egs_customer_limit
    - privilege: ddl
      role: egs_gateway
      schema: public
      database: egs_gateway
    - privilege: ddl
      role: acl_owner
      schema: public
      database: __all__

  - ldapsearch:
      base: dc=mango,dc=com
      scope: sub
      filter: |
        (&
        (objectClass=person)
        (!(nsaccountlock=TRUE))
        (memberOf=cn=pgsql-egs-leonbets-dev-acl-admin,cn=groups,cn=accounts,dc=mango,dc=com)
        )
    roles:
      - name: "{uid}"
        options: LOGIN SUPERUSER
        parents:
          - ldap_roles
        comment: "Managed by LDAP: {dn}"

  - ldapsearch:
      base: dc=mango,dc=com
      scope: sub
      filter: |
        (&
        (objectClass=person)
        (!(nsaccountlock=TRUE))
        (memberOf=cn=pgsql-egs-leonbets-dev-acl-owner,cn=groups,cn=accounts,dc=mango,dc=com)
        )
    roles:
      - name: "{uid}"
        options: LOGIN
        parents:
          - ldap_roles
          - acl_owner
        comment: "Managed by LDAP: {dn}"

Test case 1:

-ldap searches are uncommented I'm see such output in log:

13:00:47 INFO   Running as superuser.                            user=postgres super=true server="PostgreSQL 18.1" cluster="" database=postgres
13:00:47 DEBUG  Fallback owner configured.                       role=postgres
13:00:47 DEBUG  Inspecting roles blacklist.                      config=roles_blacklist_query
13:00:47 DEBUG  Reading values from YAML.
13:00:47 DEBUG  Roles blacklist loaded.                          patterns="[postgres pg_* replifoo pgbouncer]"
13:00:47 DEBUG  LDAP dial.                                       uri=ldap://localhost try=1
13:00:47 DEBUG  Retrying.                                        err="LDAP Result Code 200 \"Network Error\": dial tcp [::1]:389: connect: connection refused" attempt=0
13:00:47 DEBUG  LDAP dial.                                       uri=ldap://localhost try=2
13:00:47 DEBUG  Retrying.                                        err="LDAP Result Code 200 \"Network Error\": dial tcp [::1]:389: connect: connection refused" attempt=1
13:00:47 DEBUG  LDAP dial.                                       uri=ldap://localhost try=3
13:00:47 DEBUG  Retrying.                                        err="LDAP Result Code 200 \"Network Error\": dial tcp [::1]:389: connect: connection refused" attempt=2
13:00:48 DEBUG  LDAP dial.                                       uri=ldap://localhost try=4

Ldap2pg trying to connect to ldap on localhost.

Test case 2:

-ldap searches commented I'm seeing output in log:

13:07:04 INFO   Running as superuser.                            user=postgres super=true server="PostgreSQL 18.1" cluster="" database=postgres
13:07:04 DEBUG  Fallback owner configured.                       role=postgres
13:07:04 DEBUG  Inspecting roles blacklist.                      config=roles_blacklist_query
13:07:04 DEBUG  Reading values from YAML.
13:07:04 DEBUG  Roles blacklist loaded.                          patterns="[postgres pg_* replifoo pgbouncer]"
13:07:04 DEBUG  Processing sync map item.                        item=0
13:07:04 DEBUG  Wants role.                                      name=egs_core options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Wants role.                                      name=egs_customer_limit options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Wants role.                                      name=egs_gateway options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Wants role.                                      name=ldap_roles options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Wants role.                                      name=acl_read options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Wants role.                                      name=acl_write options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Wants role.                                      name=acl_owner options="" parents=[] comment="Managed by ldap2pg"
13:07:04 DEBUG  Processing sync map item.                        item=1
13:07:04 DEBUG  Stage 1: roles.
13:07:04 DEBUG  Inspecting managed databases.                    config=databases_query
13:07:04 DEBUG  Reading values from YAML.
13:07:04 DEBUG  Inspecting database owners.
13:07:04 DEBUG  Executing SQL query:
SELECT datname, rolname
FROM pg_catalog.pg_database
JOIN pg_catalog.pg_roles
  ON pg_catalog.pg_roles.oid = datdba
ORDER BY 1;

13:07:04 DEBUG  Found database.                                  name=egs_core owner=postgres
13:07:04 DEBUG  Ignoring unmanaged database.                     name=egs_currency
13:07:04 DEBUG  Found database.                                  name=egs_customer_limit owner=postgres
13:07:04 DEBUG  Found database.                                  name=egs_gateway owner=postgres
13:07:04 DEBUG  Ignoring unmanaged database.                     name=postgres
13:07:04 DEBUG  Ignoring unmanaged database.                     name=template0
13:07:04 DEBUG  Ignoring unmanaged database.                     name=template1
13:07:04 DEBUG  Closing Postgres global connection.              database=""
13:07:04 ERROR  Fatal error.                                     err="databases: connected to an unmanaged database"

Test case 3:

Change of

databases_query: [egs_core, egs_customer_limit, egs_gateway]

on to

databases_query: [postgres, egs_core, egs_customer_limit, egs_gateway]

Ldap searches still commented, fixing "databases: connected to an unmanaged database" issue:

...
13:16:49 INFO   Comparison complete.                             searches=0 roles=7 queries=636 grants=225 elapsed=1.203097756s mempeak=1.2MiB ldap=0s inspect=207.608275ms sync=0s
13:16:49 INFO   Use --real option to apply changes.
13:16:49 DEBUG  Closing Postgres global connection.              database=egs_gateway

Test case 4:

Uncommenting ldap searches and providing ldap parameters using variables make ldap2pg to connect to defined ldap server:

LDAPURI=ldap://freeipa.mango.com:389 \
LDAPBINDDN="uid=pgsqlfreeipasync,cn=users,cn=accounts,dc=mango,dc=com" \
LDAPPASSWORD=****** \
ldap2pg -c /etc/ldap2pg/ldap2pg.yml

...
13:12:54 INFO   Comparison complete.                             searches=2 roles=8 queries=1001 grants=225 elapsed=1.098513694s mempeak=1.2MiB ldap=19.002079ms inspect=280.859012ms sync=0s
13:12:54 INFO   Use --real option to apply changes.

Expectations

I'm expecting ldap2pg treat postgres dsn and ldap settings from ldap2pg.yml with highest priority and is overriding system settings.

[bersace]

Hi @zurikus

As of ldap2pg 6.0, connections is not configured in ldap2pg.yml. We have been striked by GitHub because user exposes password in their YAML.

Use a .env file along ldap2pg.yml to save credentials to a file.

The documentation is light on this. Please keep this issue opened so that we update the documentation.

Regards,
Étienne

[zurikus]

Hi @bersace , thank you for such fast response.

Ok, I then waiting for documentation update, as I tried to move those postgres and ldap sections into .env file, but looks like it have it's own format.

Thank you!

[zurikus]

Can you please provide proper way to provide postgres dsn as well.

Thanks in advance.

[zurikus]

If this will be useful for someone managed to get it working.
Located file .env besides ldap2pg.yml
Changed it's permission to 600

LDAPURI=ldap://freeipa.mango.com:389
LDAPBINDDN="uid=pgsqlfreeipasync,cn=users,cn=accounts,dc=mango,dc=com"
LDAPPASSWORD=*****

PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGDATABASE=egs_core

PGDATABASE is one from the databases_query list.

Both connections are working fine now. Will dive deep onto grants now.
Thanks.