<img src="https://ws.zoominfo.com/pixel/6169bf9791429100154fc0a2" width="1" height="1" style="display: none;">

PAM Was Dead. StrongDM Just Brought it Back to Life. ✨  An important message from StrongDM's CEO!

Close icon
Search bar icon

Connecting Postgres to Active Directory for Authentication

PostgreSQL is an open-source database system that is a popular choice for managing data and building applications.  

While primarily geared towards developers, PostgreSQL is also designed to help system administrators safely and robustly store information in databases.  And because many networks use Active Directory to manage users and their resource permissions, it makes sense to tie PostgreSQL into this authentication configuration as well.

In this post, we will demonstrate how to install a PostgreSQL database and then configure Active Directory users to authenticate to it.

Install PostgreSQL

First, you will install PostgreSQL on a server of your choice. I’m using Ubuntu Linux 18.10, which makes the install easy by using the apt system. Open a terminal and issue these commands:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Once installed, PostgreSQL uses ident authentication, which means the database wants to authenticate users with a matching Linux system account. By default, PostgreSQL automatically creates a system account called postgres, as well as a database user also called postgres. Together these accounts are referred to as a “role,” which PostgreSQL uses instead of users and groups.

To login to this Postgres account, type:

sudo -i -u postgres

Your terminal prompt should change to a postgres@ prefix.  

Type psql and hit Enter.  Your terminal prompt should change to postgres=#.  You can now interact directly with the PostgreSQL database.  

Configure Active Directory User Accounts

In order to properly configure authentication with Active Directory, we need to create an AD user that has a one-to-one relationship with a PostgreSQL role.  In other words, we need to create a user on each system with the same login name.

Let’s start on the Active Directory side. Create a new user in the Users container and name it whatever you like.  I’ve got a test AD environment with a domain name of samplecompany.local, and I’ve created a test user named tommy.

Additionally, we need to create an AD user that will be used to perform LDAP lookups. To keep things simple, I created a user named ldap and placed it in the Users container. Whatever you name your account and wherever you place it in your AD structure, be sure to make note of it as we will need that information in future steps.

Configure PostgreSQL role and database

Next, we’ll create a matching tommy user in the PostgreSQL environment. From your postgres=# prompt, type \q and press Enter to get back to a postgres@ prompt. Then, create a new user:

createuser --interactive

You will be prompted with Enter name of role to add. Call this role whatever the name of your AD user is. In my example, I’ll call him tommy by typing tommy and pressing Enter.

When you’re asked Shall the new role be a superuser? answer Y and press Enter.

By default, PostgreSQL assumes that for any role named X, that role will also have a database with name X. In my example, that means my user named tommy will have access to a database named tommy, so I will create it:

createdb tommy

To verify this database was created properly, type psql and hit Enter to get to the PostgreSQL prompt, then issue this command:


You should see a database with the name you specified with the createdb command.

Configure PostgreSQL to allow remote connections

By default, your PostgreSQL install is likely configured to only allow connections from your local Linux server. To change this, edit the PostgreSQL configuration file (usually in /etc/postgresql/10/main/postgresql.conf) and look for a line that looks like this:

#listen_addresses = 'localhost'

Uncomment that line by removing the first “#” and change the ‘localhost’ to ‘*’.  Save and exit the file, then restart PostgreSQL:

service postgresql restart

Configure PostgreSQL to query LDAP

Now we need to install some tools and make a few configuration file changes so LDAP is queried when someone tries to authenticate to PostgreSQL.

sudo apt-get install ldap-utils -y

You can double check your LDAP lookups are working correctly by using the ldapsearch tool in the following syntax:

ldapsearch -x -LLL -h -D ldap@samplecompany -w 'myPASSword!' -b "dc=samplecompany,dc=local"

This command is broken down as follows:

  • ldapsearch is the tool that conducts the search
  • -x specifies simple authentication
  • -LLL prints responses in LDIF format without comments and version
  • -h specifies the host to connect to
  • -D indicates the user that will bind to AD
  • ldap@samplecompany is the LDAP bind user and NETBIOS name of the domain
  • -w specifies the password for the user
  • -b is the base dn for the search

If the command runs successfully, you should get several pages of text in the output with details on the users, computers and other objects in your domain.  If the command fails, check out the ldapsearch tool’s help page.

Lastly, you need to open the pg_hba.conf file, usually located at /etc/postgresql/10/main/pg_hba.conf, which controls client authentication methods.  On an empty line, paste the following:

host all all ldap ldapserver= ldapbasedn="cn=Users,dc=samplecompany,dc=local" ldapbinddn="CN=ldap,CN=Users,dc=samplecompany,dc=local" ldapbindpasswd="'myPASSword!" ldapsearchattribute="sAMAccountName"

The configuration is broken down as follows (you will need to change the following settings to match your environment):

  • host all all specifies which subnets (in this case, only the subnet) should be allowed to connect remotely to this PostgreSQL database
  • ldap is the authentication type
  • ldapserver= points to the IP address of one of the domain controllers
  • ldapbasedn="cn=Users,dc=samplecompany,dc=local" is the starting point in the domain’s hierarchy for your search
  • ldapbinddn="CN=ldap,CN=Users,dc=samplecompany,dc=local" specifies the ldap user, which will authenticate to Active Directory to perform the searches
  • ldapbindpasswd="'myPASSword!" sets the password for the ldap user
  • ldapsearchattribute="sAMAccountName" indicates the Active Directory attribute that will be searched for.

Save and exit the file, then restart PostgreSQL to make the changes take effect:

service postgresql restart

Connect to PostgreSQL remotely

From another machine, attempt to connect to the PostgreSQL server using the following syntax:

psql -h -U tommy -W

In this example:

  • is the IP address of the PostgreSQL server
  • -U tommy indicates the user to authenticate as
  • -W requires a password prompt upon connecting to the server

After entering the password for the Active Directory user you want to use for PostgreSQL authentication (tommy in my example), your Terminal prompt should change to tommy=#, indicating that you have successfully authenticated to Active Directory using the tommy user, and are now ready to start working with data in the tommy database.

Securing and Auditing the PostgreSQL Databases

At this point, the server is configured to allow connections from Active Directory users who have a corresponding PostgreSQL role and database.  This configuration, as you can imagine, can get a bit unruly to manage - especially as you increase the number of users and databases in the environment.  Additionally, you will want to enforce security controls around the PostgreSQL configuration so that all user permissions and any database changes are captured for future auditing.

An easier way to integrate your database access with Active Directory is with StrongDM. StrongDM streamlines and simplifies database management challenges by keeping a detailed log of every user login and interaction with the PostgreSQL environment. StrongDM integrates with nearly every system, database and MFA/SSO solution under the sun to make adoption quick and easy for your company. Get started free for 14 days and see for yourself.

About the Author

, Security Engineer / Podcaster, is the president of 7 Minute Security, an information security consultancy in the Minneapolis area. Brian spends most of his days helping companies defend their networks.

Since 2004, Brian has also run the blog/podcast called 7 Minute Security, where he shares what he has learned about information security into short, 7-minute chunks.

StrongDM logo
💙 this post?
Then get all that StrongDM goodness, right in your inbox.

You May Also Like

PostgreSQL Drop Database (15+ Methods)
PostgreSQL Drop Database (15+ Methods)
The DROP DATABASE command in PostgreSQL is a powerful command that is used to delete a database along with all its associated objects, such as tables, views, indexes, and other database-specific elements. It is often a good practice to clean up your workspace by removing unused databases. However, keep in mind that deleting an existing PostgreSQL database deletes all objects and data within that database. This command should be used with caution as it irreversibly removes the specified database and its contents.
How to Create a Postgres User (Step-by-Step Tutorial)
How to Create a Postgres User (Step-by-Step Tutorial)
Creating Postgres users isn't just a routine step in the complicated world of database management; it's a critical strategy that has a significant impact on how PostgreSQL databases operate and remain secure. An increasing number of organizations depend on sophisticated data systems, so it's critical to recognize the value of Postgres users. This blog post walks you through the steps of creating a Postgres user, as well as, explores the significance of these users in database administration, emphasizing their function in maintaining security, limiting access, and ensuring efficient data management.
Pain in the Access: Databases
Are Your Databases a Pain in the Access?
The number and complexity of databases that every organization must manage has skyrocketed. If you need access - or need to provide it - it can sure be a pain in the access to manage.
Just-in-time Access (JIT)
What is Just-in-Time Access (JIT)? Benefits, Types & More
Today, we’ll take a look at what just-in-time access (JIT) means and what types there are. You’ll also learn about what a JIT access solution can do for your organization. By the end of this article, you’ll understand how just-in-time access works, the best practices to ensure secured implementation, and how strongDM comes to the rescue.
Blue key with half circle with writing and strongdm logo
Automating Database Credentialing Guide for 2024
Database sprawl is a lot like expanding into the suburbs: your house may be empty at first, but before you know it, you’re having to stuff things into your attic.