Managing users and authentication in Apache with PostgreSQL

Before you begin

In this tutorial, we’ll learn about managing users and authentication in Apache using Apache modules (bdb, authn_dbd, authn_socache and socache_shmcb) and PostgreSQL table. A Linux machine and Docker will be required to follow this tutorial.

Install Apache server and modules on Linux

Commands to install Apache:

$ sudo apt-get update
$ sudo apt-get install apache2

Commands to install required Apache modules bdb, authn_dbd, authn_socache and socache_shmcb:

# Enable required modules
sudo a2enmod dbd
sudo a2enmod authn_dbd
sudo a2enmod authn_socache
sudo a2enmod socache_shmcb

# Restart server
sudo service apache2 restart

# List enabled Apache modules
apache2ctl -M

Create an auth PostgreSQL database, user and table

# Run PostgreSQL local instance
$ docker run --name local-postgres -p 5432:5432 -d postgres

# Create my_auth_db database
$ docker exec -it local-postgres psql -h 0.0.0.0 -U postgres -c 'CREATE DATABASE my_auth_db;'

# Create my_auth_user user
$ docker exec -it local-postgres psql -h 0.0.0.0 -U postgres -c "CREATE USER my_auth_user WITH PASSWORD 'my_auth_pass';"
$ docker exec -it local-postgres psql -h 0.0.0.0 -U postgres -c 'GRANT ALL PRIVILEGES ON DATABASE my_auth_db to my_auth_user;'

# Create table auth
$ docker exec -it local-postgres psql -h 0.0.0.0 -U my_auth_user my_auth_db -c " \
  create table auth ( \
  username varchar(255) not null, \
  passwd varchar(255), \
  groups varchar(255), \
  primary key (username) \
  ); \
"

# Check table creation
$ docker exec -it local-postgres psql -h 0.0.0.0 -U my_auth_user my_auth_db -c '\dt'
$ docker exec -it local-postgres psql -h 0.0.0.0 -U my_auth_user my_auth_db -c '\d auth'

Create test auth user

Create password for user test:

$ htpasswd -bns test  test
> test:{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=

Insert test user on previous auth table:

$ docker exec -it local-postgres psql -h 0.0.0.0 -U my_auth_user my_auth_db -c "INSERT INTO auth (username, passwd, groups) VALUES('test', '{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=', 'testgroup');"

# Check user insertion
$ docker exec -it local-postgres psql -h 0.0.0.0 -U my_auth_user my_auth_db -c "SELECT * FROM auth;"

Configure Apache dbd modules and configure restricted folder test/

Backup existing 000-default.conf file:

sudo cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/000-default.conf.bkp

Edit 000-default.conf file adding dbd params (Adding code below inside of section):

# mod_dbd configuration
DBDriver pgsql
DBDParams "host=0.0.0.0 dbname=my_auth_db user=my_auth_user password=my_auth_pass"

DBDMin 4
DBDKeep 8
DBDMax 20
DBDExptime 300

<Directory "/var/www/html/test">
 # mod_authn_core and mod_auth_basic configuration
 # for mod_authn_dbd
 AuthType Basic
 AuthName "My Server"

 # Rquired for caching dbd lookups
 AuthBasicProvider socache dbd
 AuthnCacheProvideFor dbd
 AuthnCacheContext my-server

 # mod_authz_core configuration
 Require valid-user

 # mod_authn_dbd SQL query to authenticate a user
 AuthDBDUserPWQuery "SELECT passwd FROM auth WHERE username = %s"
</Directory>

Restart Apache server:

$ service apache2 restart

Finally check website http://[YOUR_DOMAIN]/test/ and make sure that user and password is requested and you can easily access to the resource using test/test. Check also that Apache log file doesn’t contain any errors: