Articles sur SQL

PostgreSQL and pam_ldap

This section will discuss how to setup PostgreSQL to authenticate against an LDAP system. It will also discuss how to configure certain accounts to be authenticated through PostgreSQL's internal account/authentication mechanism.

Things you should know before progressing through this document:

1) How to install and configure pam_ldap on your unix machine. (and how to install unix on a machine, what a machine is, and all the other dependencies that result from this statement)
2) How to install PostgreSQL on your machine.

Now that you have passed the requirements, let's get started...

Creating Accounts

The first thing you will need to do is create your accounts. Due to the way postgres is coded, you will have to create accounts on the actual database system with usernames that match the ones in your LDAP repository. This is done with the createuser statement. I will not spend time describing how to use createuser as a simple man createuser and/or google will reveal more than I possibly can.

pam.d/postgres

It is necessary to create a separate file that contains the pam settings postgres should use. As of yet, I have not found a way to make the postgresql work with anything except pam_ldap modules. When combining these modules with pam_unix or others, I would continously get a PAM conversation error. To work around this, I made the changes below to my pg_hba.conf file. But to make my postgresql pam file work, I made it contain the following:

/etc/pam.d/postgresql (On Debian)

auth    required        /lib/security/pam_ldap.so 
account required        /lib/security/pam_ldap.so

This tells postgresql, when it uses pam to authenticate an account, to use the features of the pam_ldap.so library for both authentication and account information. I found the latter to be unecessary considering that the db administrator must still add the accounts to postgresql, but it needed to be in the file for postgresql to authenticate properly (i.e. without generating an error).

pg_hba.conf

Now we need to edit our pg_hba.conf file. The version 8.0 package of debian places this file in /etc/postgresql/8.0/<clustername>/. Add the following entries:

local   all     ldapuser1                                               pam postgresql 
local   all     ldapuser2                                               pam postgresql
...
local   all     all                                                     md5
host    all     ldapuser1       IPAddress       IPMask                  pam postgresql
host    all     ldapuser2       IPAddress       IPMask                  pam postgresql
...
host    all     postgres        0.0.0.0         0.0.0.0                 deny
host    all     all             IPAddress       IPMask                  md5

Let's review what this does:

It configures postgres to authenticate usernames that are 'ldapuser1' or 'ldapuser2' against the systems listed in /path/to/pam.d/postgresql. It will do this for all local logins. All other local logins that are authenticated against the localized postgresql accounts using MD5 encryption.

For remote hosts, I spiced things up a bit. Like before I want to allow my ldapusers to authenticate through pam. From my understanding, remote logins work like this: A user connects and postgres compares its IPAddress to the one in pg_hba.conf. As long as the IP address is in the range of IPs allowed by the bitwise comparison of the IPAdress and IPMask entries, they are allowed to authenticate. If not, postgres throws an error. If the ldap user is accessing from a machine that is allowed to authenticate, they are then passed to the pam module. If this is the case and the password matches the LDAP entry, the client is allowed to connect.

I also deny remote logins from ANY host other than local to the database using the postgres superuser account. All other remote logins that are authenticated against the localized postgresql accounts using MD5 encryption. like before.

Conclusion

This setup allows the database administrator to seperate between local and ldap accounts. For example, if the database had these accounts:

ldapuser1
ldapuser2
postgres
localuser1
localuser2

The postgres database would authenticate the first two using LDAP, and the last three using it's local password system. In the event of remote logins, the postgres account would be denied access.

Source: techdocs.postgresql.org