Is it possible to manage the roles of (Non-ldap) database users through ldap2pg?

[arjan-saly-tfs]

Hi ldap2pg community,

Sorry if this question has already been asked before, but I then can't find the answer. Also, I do understand my question goes beyond the goal of ldap2pg, but it seems to me this might be possible. Hence my question.

So, here's my case: I have two types of users in my database:

1. "Application users": These are typically created manually in the database, connect by providing a userid and password (method MD5) and cannot be found in the LDAP server. They are used by applications only and come in three forms: a. Maintaining the content/data in the database (Read/write on all tables or specific schema's, doing mainly inserts and updates). b. Installing/updating DDL as part of the install or update of the application c. Reading data with the goal of additional processing and storing elsewhere.
2. "In person" users: persons that have a need to query or manage the data. These users are all found on the LDAP server and thus follow the normal ldap2pg process.

My question is about the first, the "application users". Would it be possible to manage the roles assignment by ldap2pg?

Looking at the postgres:managed_roles_query example (https://ldap2pg.readthedocs.io/en/latest/postgres/#synchronize-a-subset-of-roles), it seems to me like database users can be included in the managed roles.

But then, How do I link them to roles? Is that even possible? Or am I crossing the ldap2pg borders here?

Thanks in advance!

Regards, Arjan

[bersace]

Hi @arjan-saly-tfs ,

Yes, ldap2pg can create static roles and configure them just like roles created from LDAP searches. Just skip the ldapsearch stanza:

sync_map:
- description: "Static application roles"
  roles:
  - name: myapprole
  grant:
  - privilege: ddl
    to: myapprole

You can't user curly substitution in such roles.

Is it clearer for you ?

[arjan-saly-tfs]

Hi @bersace,

Thanks for explaining. Tried it and this works very well.

One follow up question though: In your example, no password is mentioned. By result the role is created without a password being set. Is it possible to set an MD5 password for this role "myapprole" using ldap2pg?

(Not sure if I would really want to have such a password in a config file like this. I'm exploring to what extent I could use this and where the boundaries are, preparing for questions I expect to be asked by our developers... ;-) .

[bersace]

Hi @arjan-saly-tfs that's the purpose of using LDAP for authentication. You have to configure pg_hba.conf for these roles. ldap2pg does not manage passwords.

[arjan-saly-tfs]

Hi @bersace,

Thanks for confirming, fits my expectations.

When looking back to my original question however, the answer actually is "no" as I mentioned in my first bullet these "application users":

connect by providing a userid and and password (method MD5) and cannot be found in the LDAP server

Thus:

• Yes, ldap2pg can create users (roles with connect privilege)
• No, ldap2pg cannot set passwords for them

Hence, using ldap2pg to create and maintain non-ldap user-roles can be useful when Authorization is done externally. Could be peer, could be any of the other external methods. It becomes counter-productive though for the methods "password" and "MD5" as the password can't be set and thus must be set manually afterwards. For those it works better to create them manually or by script and configure ldap2pg to ignore them.

Thanks again for clarifying!

[bersace]

Hi @arjan-saly-tfs yes. static roles in ldap2pg are usualy groups.

[bersace]

Closing stale issue.