PostgreSQL LDAP authentication

Question

We are setting up a PostgreSQL 9.1 database at work. There are no classified data in the database, but we want to know who is doing what. We are using LDAP authentification on the network, so we would like to use that for the database as well, but according to the documentation, all users anyhow need to be defined as database users.

We could of course do a "dump" of users to the database, but we would prefer if it could be possible to define users in the database as soon as they have authenticated in LDAP. This would not be too difficult using a wrapper script, but is it somehow possible to do this directly? (Most users log in using psql.)

Answer 1

There is two independant steps with Postgres and LDAP:

1. tells Postgres how to query LDAP to authenticate one user, it's documented here . You must use ldap authentication method in pg_hba.conf.
2. You must create the roles in Postgres, with options etc. You can use a tool like ldap2pg or a custom script.

That way, you can manage roles and ACL just as usual. You just don't need to store passwords in cluster.

Answer 2

I'm not very familiar with LDAP (beware of understatement!), but I'm pretty sure there is no way to do this automatically on the PostgreSQL side. If you create your LDAP users in a GUI then probably you want to set up a cron script to export the users to the database (I don't know if you can define trigger-like behaviour there, I mean something which would fire a user-creation DB script once a user is created for LDAP). If you do this from a script then it is quite easy to solve the problem, as you already mentioned.