This document describes how to configure Postgres so user accounts are managed from Active Directory. This will improve security by allowing you to rotate database passwords on a regular basis.
Client applications can be configured to retrieve the password from the Centrify Privilege Service and used to connect to the database.
All configuration and testing for this guide was performed using Postgres 9.6.
First, an understanding of the term "role" as it applies to Postgres.
PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.
You must create an operating system user to match the database role user in PostgreSQL.
Postgres can be configured to allow role user authentication using a Pluggable Access Module (PAM) that reaches through to the operating system layer. Centrify provides PAM based authentication to Active Directory.
An Active Directory user can be linked to a Postgres role user. The password for the AD user can be stored in the Centrify Privilege Service and checked out/rotated as needed.
Join the PostgreSQL server to Active Directory using Centrify DirectControl
Follow the standard instructions for installing DirectControl on your linux server.
It might be a good practice to create a special zone with a name such as "Postgres Servers" to identify these computers.
Configure PostgreSQL to perform PAM authentication for your Active Directory users.
Edit the pg_hba.conf file.
Here the file path in version 9.6 : /var/lib/pgsql/9.6/dta/pg_hba.conf
Add a line to the pg_hba.conf file to enable PAM authentication.
# TYPE DATABASE USER ADDRESS METHOD
[... this part redacted for brevity ...]
host all all all pam pamservice=postgresql96
The pamservice option specifies the PAM file located at /etc/pam.d/postgresql96. This file is created by default in /etc/pam.d/ when PostgreSQL is installed. This PAM configuration passes through to standard operating system authentication.
Since Centrify is configured as a PAM module, authentication will be managed using Centrify to Active Directory.
Notes : Here are some necessary tasks that are required, but not documented, in this guide.
1) PostgreSQL requires you to restart the database server after changes are made to the pg_hba.conf file.
2) To enable network access to PostgreSQL, you will need to configure the database to listen on port 5432.
Consult the PostgreSQL documentation for details on performing these steps.
Disclaimer from the author : This host configuration record is set to allow all users to connect to all databases from all IP addresses, and was constructed for testing purposes. Obviously you would want to configure this to meet the necessary security requirements of your environment, such as a limited set of users, to specific databases, from a subset of IP addresses. Such settings are documented in the PostgreSQL documentation.
Add the following lines to the /etc/pam.d/postgresql file:
auth include system-auth
account include system-auth
session include system-auth
auth required pam_unix.so nullok
account required pam_unix.so
session required pam_unix.so
Create your role (user) in the PostgreSQL database.
Create your Active Directory user with the same username as the database role name.
Note : In our example, the PostgreSQL role (user) must match the Active Directory user. However PostgreSQL supports the mapping of usernames to database name that are not identical. This mapping exercise is outside the scope of this guide.
Configure Centrify to allow the Active Directory user to authenticate using only the PSQL client.
Centrify allows you to control the authentication method used by a user, using methods such as ssh, telnet, ftp, etc.
We can create a custom PAM authentication method that will only allow PostgreSQL users to authenticate through the psql client. We would prevent users from logging in to the box using ssh for example.
In the Centrify DirectManage console, create a new PAM Access right called "postgresql96". See image.
The PAM Access right is named "postgresql96" to correspond to the PAM name specified in the pg_hba.conf file above.
Create a new Role Definition called "PostgreSQL Client Access".
Configure the System Rights for this new role. Enable the checkbox "Password login and non-password (SSO) login are allowed".
Assign the PostgreSQL Client Access role to your Active Directory database user. (Diagram not included)
In order to test quickly, you may run adgpupdate on your database server to update the Centrify cache.
Testing from a client machine:
The client machine must have the Postgres client installed.
Run the following command from a terminal :
> psql -h -U -W
When prompted for the password, enter the Active Directory password to log in to the database.
The -W option tells Postgres to prompt for the password on the first authentication attempt. By default, the psql command will attempt to connect to the database with no password, fail, then re-attempt and prompt for password. This -W option eliminates the automatic authentication failure on the initial attempt.
Configuring your client to check out the Active Directory password for the database user.
Using Centrify Privilege Service you can configure your database client to check out the Active Directory password and pass it to your Postgres client application.
The Privilege Service can also automatically rotate the password and keep it secure.
Register your Active Directory database user in Centrify Privilege Service
Install the Centrify Agent for AAPM on your Postgres client machine.
Bind your Postgres client machine to the Centrify Privilege Service.
Grant password checkout privileges to your Postgres client machine for the Active Directory/database user account.
Here is a sample script that will check out the password. This could be extended so the password is written into a database file or other temporary storage.
>cgetaccount -s /
There are sample scripts included with the Centrify agent in /usr/share/centrifycc/samples.
Using the Postgres .pgpass file for storing the database password.
By default, a script that connects to a Postgres database uses for a file called .pgpass for the database password.
The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise).
This file should contain lines of the following format:
ref : https://www.postgresql.org/docs/9.3/static/libpq-pgpass.html