Two factor authentication for PostgreSQL using PAM RADIUS module

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.

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:
  • 1024 MB RAM
  • 8 GB disk size

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. Unzip the file
3. Import the virtual appliance your computer virtualization software
   • Instructions for Hyper-V
4. Ensure that the LoginTC RADIUS Connector has a virtual network card
5. Start the virtual appliance
6. You will be presented with a console dashboard:
7. The first thing you must do is set the logintc-user password:
8. By default the appliance network is configured by DHCP. If you wish to manually configure the network, use the Down arrow key to navigate to Network Configuration and DNS Configuration
9. For access to the web interface select Web Server and then Start:It may take 5-10 seconds to start the first time:
10. Once started, access the web interface by navigating to the URL mentioned in the information box:

The LoginTC RADIUS Connector runs CentOS 7.7 with SELinux. A firewall runs with the following open ports:

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

1. LoginTC

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.

2. First Factor

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.

3. Passthrough

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.

4. Client and Encryption

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 a configuration 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 Configuration

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 configuration file by clicking + Create your first configuration:

LoginTC Settings

Configure which LoginTC Application to us:

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:

First Authentication Factor

Configure the first authentication factor to be used 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 / LDAP Option

Select Active Directory if you have an AD Server. For all other LDAP-speaking directory services, such as OpenDJ or OpenLDAP, select 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.

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.

No Passthrough (default)

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

Static 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

Active Directory / LDAP Group

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 and Encryption

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

Client configuration values:

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 and then click the /var/log/logintc/authenticate.log tab to view the log file and troubleshoot:

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 CentOS. 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 CentOS / 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 3.X

Follow these instructions to upgrade your LoginTC RADIUS virtual appliance to the latest version (3.0.7):

1. SSH into the virtual appliance or open the console (use same username / password as web GUI)
2. cd /tmp
3. curl -O https://www.logintc.com/downloads/logintc-radius-connector-3.0.7-upgrade.sh
4. sudo sh logintc-radius-connector-3.0.7-upgrade.sh

The upgrade script will restart your appliance after upgrading.

From 2.X

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