No article I could find on the web touches the “PostgreSQL-PAM” topic specifically for Mac OS X users, so I decided to put all the pieces together. Thanks to Adrian Nida for the excellent article that provided the basic guidelines for configuring PAM.
No article I could find on the web touches the “PostgreSQL-PAM” topic specifically for Mac OS X users, so I decided to put all the pieces together. Thanks to Adrian Nida for the excellent article that provided the basic guidelines for configuring PAM.
PAM (Pluggable Authentication Modules) are, according to Wikipedia, “a mechanism to integrate multiple low-level authentication schemes into a high-level API, which allows for programs that rely on authentication to be written independently of the underlying authentication scheme”.
Mac OS X comes with built-in support for PAM. You can find the support libraries at /usr/lib/pam/.
The advantage of configuring PostgreSQL to use PAM is that it allows a user to use his/her system credentials (username/password defined for the OS where the database is running) to authenticate to PostgreSQL. In other words, if an administrator changes the password for his users in the Accounts pane of the System Preferences on Mac OS X or Mac OS X Server, those changes will automatically affect the PostgreSQL authentication, there’s no need to re-enter the password information.
But still, you need to list the user accounts that will be authorized to access your databases in your pg_hba.conf file.
Here’s how I made it work:
1) Configure PostgreSQL with PAM support. Usually the binary installers you will find on the web don’t come with this option enabled by default, so the best thing to do is get the source and build it yourself. Download the latest version (8.1.4 as of today) and configuring it with the --with-pam option. My configure command looks like this:
./configure --bindir=/usr/local/bin --mandir=/usr/local/share/man/ --enable-recode --with-CXX --enable-syslog --enable-unicode-conversion --enable-multibyte=UNICODE --with-bonjour --with-pam
2) Build and install it with make and sudo make install, respectively.
3) Create a file called postgresql into /etc/pam.d/ with the following content:
auth required /usr/lib/pam/pam_nologin.so auth sufficient /usr/lib/pam/pam_securityserver.so auth sufficient /usr/lib/pam/pam_unix.so auth required /usr/lib/pam/pam_deny.so account required /usr/lib/pam/pam_permit.so password required /usr/lib/pam/pam_deny.so session required /usr/lib/pam/pam_permit.so
4) Create a user on System Preferences to run the database server. This user should not be an administrator. In my case, I called it postgres. Change the owner of the pgsql folder to be this user.
sudo chown -R postgres /usr/local/pgsql
5) su as this new user, and create the cluster:
su - postgres
/usr/local/bin/initdb -E UTF8 -D /usr/local/pgsql/data
6) Edit the contents of your cluster’s pg_hba.conf file so that it requires pam authentication. Ex:
<code> TYPE DATABASE USER CIDR-ADDRESS METHOD ----------------------------------------------------------- local all tiago pam postgresql local all postgres pam postgresql </code>
That should be enough. Create your databases and start the server as usual. When connecting to a database, the user will be prompted to enter the account password. This is the same password used by Mac OS X to authenticate users.
Some notes:
-
The user still must have privileges on the database in order to be able to access it. As the database administrator, you must execute the
CREATE USER username;command at thepsqlterminal for creating a role for that particular user. -
I’ve not tested this solution with Open Directory. Would love to know if it works, please let me know!
