Two factor authentication for PostgreSQL using PAM RADIUS module

 
Last Updated: December 20, 2024

LoginTC makes it easy for administrators to add multi-factor to PostgreSQL on their Unix systems. This document shows how to configure PostgreSQL to require two factor authentication for local and / or remote access via Pluggable Authentication Module (PAM). MySQL can be configured in a similar fashion.
 
Explore how LoginTC integrates with Postegre SQL below.
 

Authentication Flow

1. A user attempts access with username / password
2. A RADIUS authentication request is sent to the LoginTC RADIUS Connector
3. The username / password is verified against an existing first factor directory (LDAP, Active Directory or RADIUS)
4. An authentication request is made to LoginTC Cloud Services
5. Secure push notification request sent to the user’s mobile or desktop device
6. User response (approval or denial of request) sent to LoginTC Cloud Services
7. The LoginTC RADIUS Connector polls until the user responds or a timeout is reached
8. RADIUS Access-Accept sent back to PAM RADIUS module
9. User is granted access to PostgreSQL

Before proceeding, please ensure you have the following:

• LoginTC Admin Panel account
• Computer virtualization software such as VMware ESXi, VirtualBox, or Hyper-V
• Virtual Machine requirements:
  • 1 vCPU
  • 2 GiB RAM
  • 8 GB disk space

Start by creating a LoginTC Application for your deployment. An Application represents a service (e.g. An application is a service (e.g., VPN or web application) that you want to protect. e) that you want to protect with LoginTC.

Create a LoginTC Application in LoginTC Admin Panel, follow Create Application Steps.

If you have already created a LoginTC Application for your deployment, then you may skip this section and proceed to Installation.

1. Download the latest LoginTC RADIUS Connector:
   • OVF for ESXi, VirtualBox
   • VHD for Hyper-V
2. Import the virtual appliance your computer virtualization software
   • Instructions for Hyper-V
3. Ensure that LoginTC RADIUS CONNECTOR has a virtual network card
4. Start the virtual appliance
5. You will be with a console prompt:
6. Login using the username logintc-user and default password logintcradius:
7. Once logged in type setup:
8. Follow the on-screen prompt to setup a new password for logintc-user:
9. By default the appliance network is not configured. Manually configure the network by typing 1 and hit enter:
10. Follow the on-screen prompts to setup the network. When done, type 1 and enter to confirm the settings:
11. You will be presented with the network configuration which includes the URL to connect to the appliance from a web browser (example https://172.20.221.105:8443):
12. Navigate to the URL shown in the console dashboard (example: https://172.20.221.105:8443):
13. Login using the username logintc-user and the password that was set in the initial setup:
14. Link to your existing LoginTC organization. The 64-character Organization API Key is found on the LoginTC Admin Panel under Settings >page API >page Click to view, also see Organization API Key:
15. Confirm the LoginTC organization name and click Continue to LoginTC RADIUS Connector:
16. If you have an existing LoginTC RADIUS Connector your wish to import configurations then click Yes, import configurations from an existing LoginTC RADIUS Connector, otherwise click No, continue to the adminsitration panel:

    NOTE
    These instructions assume a new environment. For a complete 2.X / 3.X to 4.X upgrade guide: LoginTC RADIUS Connector Upgrade Guide

17. Now you are ready to use the LoginTC RADIUS Connector:

Endpoints describe how the appliance will authenticate your RADIUS-speaking device with an optional first factor and LoginTC as a second factor. Each endpoint has 4 Sections:

This section describes how the appliance itself authenticates against LoginTC Admin Panel with your LoginTC Application. Only users that are part of your organization and added to the domain configured will be able to authenticate.

This section describes how the appliance will conduct an optional first factor. Either against an existing LDAP, Active Directory or RADIUS server. If no first factor is selected, then only LoginTC will be used for authentication.

This section describes whether the appliance will perform a LoginTC challenge for an authenticating user. The default is to challenge all users. However with either a static list or Active Directory / LDAP Group you can control whom gets challenged to facilitate seamless testing and rollout.

This section describes which RADIUS-speaking device will be connecting to the appliance and whether to encrypt API Key, password and secret parameters.

The web interface makes setting up an endpoint simple and straightforward. Each section has a Test feature, which validates each input value and reports all potential errors. Section specific validation simplifies troubleshooting and gets your infrastructure protected correctly faster.

First Endpoint

Close the console and navigate to your appliance web interface URL. Use username logintc-user and the password you set upon initial launch of the appliance. You will now configure the LoginTC RADIUS Connector.

Create a new endpoint file by clicking + Create your first endpoint:

LoginTC Settings

A list of available Applications will be displayed from your LoginTC organization. Select which LoginTC Application to use:

Configure the application:

Configuration values:

The Application ID and Application API Key are found on the LoginTC Admin Panel.

Click Test to validate the values and then click Next:

User Directory

Configure the user directory to be used for first authentication factor in conjunction with LoginTC. You may use Active Directory / LDAP or an existing RADIUS server. You may also opt not to use a first factor, in which case LoginTC will be the only authentication factor.

Active Directory / Generic LDAP Option

Select Active Directory if you have an AD Server. For all other LDAP-speaking directory services, such as OpenDJ or OpenLDAP, select Generic LDAP:

Configuration values:

Click Test to validate the values and then click Next.

Existing RADIUS Server Option

If you want to use your existing RADIUS server, select RADIUS:

Configuration values:

Click Test to validate the values and then click Next.

Challenge Strategy / Passthrough

Configure which users will be challenged with LoginTC. This allows you to control how LoginTC will be phased in for your users. This flexibility allows for seamless testing and roll out.

For example, with smaller or proof of concept deployments select the Static List option. Users on the static list will be challenged with LoginTC, while those not on the list will only be challenged with the configured First Authentication Factor. That means you will be able to test LoginTC without affecting existing users accessing your VPN.

For larger deployments you can elect to use the Active Directory or LDAP Group option. Only users part of a particular LDAP or Active Directory Group will be challenged with LoginTC. As your users are migrating to LoginTC your LDAP and Active Directory group policy will ensure that they will be challenged with LoginTC. Users not part of the group will only be challenged with the configured First Authentication Factor.

Challenge All Users

Select this option if you wish every user to be challenged with LoginTC.

Challenge Users Based on Static Username List

Select this option if you wish to have a static list of users that will be challenged with LoginTC. Good for small number of users.

LoginTC challenge users: a new line separated list of usernames. For example:

            jane.doe
            jane.smith
            john.doe
            john.smith

Challenge Users Based on Group Membership

Select this option if you wish to have only users part of a particular Active Directory or LDAP group to be challenged with LoginTC. Good for medium and large number of users.

Configuration values:

Click Test to validate the values and then click Next.

Client Settings

Configure RADIUS client (e.g. your RADIUS-speaking VPN):

Client configuration values:

Under Authentication Mode select Direct

The LoginTC RADIUS Connector will directly and automatically perform the LoginTC second factor. See User Experience for more information.

Click Test to validate the values and then click Save.

When you are ready to test your configuration, create a LoginTC user (if you haven’t already done so). The username should match your existing user. Provision a token by following the steps:

1. In a new tab / window log into the LoginTC Admin Panel
2. Click Domains
3. Click on your domain
4. Click on Members
5. Click Issue Token button beside your user:
6. A 10-character alphanumeric activation code will appear beside the user:
7. Open the LoginTC mobile app.
8. Enter the 10-character alphanumeric activation code:
9. Load the token to complete the process

When you have loaded a token for your new user and domain, navigate to your appliance web interface URL:

Click Test Configuration:

Enter a valid username and password; if there is no password leave it blank. A simulated authentication request will be sent to the mobile or desktop device with the user token loaded. Approve the request to continue:

Congratulations! Your appliance can successfully broker first and second factor authentication. The only remaining step is to configure your RADIUS device!

If there was an error during testing, the following will appear:

In this case, click See logs (or click the Logs section):

The PAM RADIUS module from FreeRADIUS allows the use of RADIUS to PAM authentication. It can be leverage for almost any service that supports PAM-based authentication. If your system does not have pam_radius_auth package installed you will need to do so. Below are instructions for RedHat. For more information on pam_radius_auth and installing it on your system please see: FreeRADIUS PAM Authentication and Accounting module.

Install PAM RADIUS on RedHat

Step 1: Developer tools:

$ sudo yum install wget gcc pam pam-devel make -y

Step 2: Build PAM RADIUS module pre:

            $ cd /tmp
            $ sudo wget ftp://ftp.freeradius.org/pub/radius/pam_radius-1.4.0.tar.gz
            $ sudo tar xvzf pam_radius-1.4.0.tar.gz
            $ cd pam_radius-1.4.0
            $ sudo ./configure
            $ sudo make

Step 3: Copy shared object library to appropriate folder

32-bit

$ sudo cp pam_radius_auth.so /lib/security/

64-bit

$ sudo cp pam_radius_auth.so /lib64/security/

The PAM RADIUS library is installed and ready to be configured.

Step 1: Create or edit the /etc/raddb/server file to point to your LoginTC RADIUS Connector:

            $ sudo mkdir -p /etc/raddb
            $ sudo vi /etc/raddb/server

            # server[:port] shared_secret      timeout (s)
            # Example server (change to fit your needs):
            192.168.1.40    bigsecret          60

The server should match the IP Address of your LoginTC RADIUS Connector, while the shared_secret should match to one configured in the LoginTC RADIUS Connector. The corresponding settings are configured in Client and Encryption portion of the LoginTC RADIUS Connector.

Step 2: Edit /etc/pam.d/postgresql:

$ sudo vi /etc/pam.d/postgresql

Option 1: Use only LoginTC RADIUS Connector for authentication:

            #%PAM-1.0
            auth       required     pam_radius_auth.so
            account    include      system-auth 
            password   include      system-auth 
            session    include      system-auth

Option 2: Use local password authentication AND LoginTC RADIUS Connector for authentication:

            #%PAM-1.0
            auth       required     pam_radius_auth.so
            auth       include      password-auth
            account    include      system-auth 
            password   include      system-auth 
            session    include      system-auth

            $ sudo groupadd -r shadow
            $ sudo usermod -a -G shadow postgres
            $ sudo chown root:shadow /etc/shadow
            $ sudo chmod g+r /etc/shadow

            $ sudo vi /var/lib/pgsql/data/pg_hba.conf
            

Add the following line where you would like to enforce two-factor authentication for PostgreSQL:

local   all         all        [CIDR-ADDRESS]         pam pamservice=postgresql

Example which requires two-factor authentication for local access and remote access from any IP Address within 192.168.x.x:

            # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
            
            # "local" is for Unix domain socket connections only
            local   all         all                               pam pamservice=postgresql
            # IPv4 connections:
            host    all         all         192.168.0.0/24        pam pamservice=postgresql
            

Step 4: Restart postgresql:

$ sudo service postgresql restart

You are now ready to test two-factor authentication to PostgreSQL.

Test by accessing PostgreSQL. The username of the postgres user must match the username of the user created in your organization and added to the domain you have configured to authenticate against.

$ psql -d testdb -U john.doe

You will be prompted for a password and then challenged with LoginTC.

There are several options for managing your users within LoginTC:

• Individual users can be added manually in LoginTC Admin Panel
• Bulk operations using CSV Import
• Programmatically manage user lifecycle with the REST API
• One-way user synchronization of users to LoginTC Admin is performed using User Sync Tool.

Logs can be found on the Logs tab:

PAM RADIUS Module

For troubleshooting related to the PAM RADIUS module please refer to: FreeRADIUS PAM Authentication and Accounting module.

Not Authenticating

If you are unable to authenticate, navigate to your appliance web interface URL and click Status:

Ensure that all the status checks pass. For additional troubleshooting, click Logs:

Also make sure to check the PostgreSQL logs (/var/lib/pgsql/data/pg_log) and secure logs on the Linux machine hosting PostgreSQL (/var/log/secure).

From 4.X

1. Navigate to SETUP > Upgrade:
2. Click Upload and select your LoginTC RADIUS Connector upgrade file:
3. Click Upload and do not navigate away from the page:
4. Once upload is complete upgrade by clicking Install Now:
5. Wait 10-15 minutes for upgrade to complete:

From 3.X

Complete 3.X to 4.X upgrade guide: LoginTC RADIUS Connector Upgrade Guide