System: Authenticating Apache Logins with PostgreSQL

Tweet 0 Shares 0 Tweets 0 Comments

This article presents instructions for enabling Basic Auth authentication in Apache against a PostgreSQL database to secure a specific website directory.

Our test server is running Apache 2.4 and PostgreSQL 11. Some features may not be supported in earlier versions. Basic Auth should only be used in combination with HTTPS in order to protect user credentials.

List of Variables

db_name: the name of our PostgreSQL database.
db_user: username for connecting to the database.
db_passwd: password for connecting to the database.

Creating the users TABLE

Really the only fields you need are username and password, but it doesn't hurt to include some identifying information, and an on/off switch.

CREATE TABLE auth_users (
  id serial,
  name varchar UNIQUE NOT NULL,
  email varchar,
  username varchar(32) UNIQUE NOT NULL,
  password varchar NOT NULL,
  active bool NOT NULL DEFAULT TRUE,
  PRIMARY KEY (id)
);
CREATE TABLE

The serial data type actually creates a sequence starting from 1 and incrementing for each INSERTed row.

Don't forget to set ownership of the table to your database user:

ALTER TABLE auth_users OWNER TO db_user;
ALTER TABLE

Creating user accounts

Now we can set up accounts for our authorised users:

INSERT INTO auth_users (name, username, password) VALUES ('Joe Citizen', 'joe_cool', crypt('secret_password', gen_salt('bf', 10)));
INSERT 0 1
INSERT INTO auth_users (name, username, password) VALUES ('Jane Citizen', 'calamity', crypt('secret_password', gen_salt('bf', 10)));
INSERT 0 1

If you see this error:

ERROR: function gen_salt(unknown, integer) does not exist

it means you first need to install the pgcrypto extension:

CREATE EXTENSION pgcrypto;
CREATE EXTENSION

We can now confirm that the user records have been created and are flagged as 'active' in the database:

SELECT username, password FROM auth_users WHERE active;
-[ RECORD 1 ]----------------------------------------------------------
username | joe_cool
password | $2a$10$esCOnR5ZD7mO30uwfqdnOePA5/fNVWevPbjxlZAdKiitdCpdh0yN6
-[ RECORD 2 ]----------------------------------------------------------
username | calamity
password | $2a$10$zTmzl.e0jhR8Vu5Fc15iNuby7hYg8KvVItc6wj7hn.oqvc8L2crh6
(2 rows)

You may notice that pgcrypto has created blowfish hashes using the deprecated $2a$ prefix instead of $2y$ . This is not ideal, and if it worries you you can generate the password elsewhere and then insert or update them into the database directly.

If you have a legacy .htpasswd list of usernames and passwords you should, in most cases, be able to copy them directly from that file into the database, and they will work even if they are using an older encryption method such as md5 or crypt.

Here's a sample script to generate the SQL:

awk -v xxx="'" -F\: '{print "INSERT INTO auth_users (name, username, password) VALUES ("xxx$1xxx", "xxx$1xxx", "xxx$2xxx");"}' /path/to/.htpasswd

Password hashes via CLI

To generate an equivalent blowfish password from the command line you can use the htpasswd command as follows:

htpasswd -b -n -BC10 joe_cool secret_password
joe_cool:$2y$10$eo5DFnjqGcaSbenRLurBc.b6MtDr01FWnembijJChmDdYrUcXFwbu

If you generate the password using one of the above approaches, be aware that it may persist in plain text in your database logs or command line history. A more secure approach would be:

htpasswd -n -BC10 joe_cool
New password: ********
Re-type new password: ********
joe_cool:$2y$10$1hQQdhBcsqLLRi0EqUtKbuXV1r5yex7iiooiUVbtbuFrLGFSG6F0a

This accepts a password from the command line, without it being displayed, and displays the resulting hash which we can take an use in our INSERT statement.

If you use this approach rather than the PostgreSQL crypt function you no longer need the pgcrypto extension and can DROP EXTENSION pgcrypto;.

We also have code for blowfish encryption in PHP.

Apache DBD Authentication

The first step is to ensure that the required modules are activated:

sudo a2enmod authn_dbd
sudo a2enmod authn_socache

And we musn't forget the APR encryption library:

sudo apt-get install libaprutil1-dbd-pgsql

If any of the above are missing, you will receive the following errors running apache2ctl configtest:

AH00526: Syntax error on line XXX of XXX:
Unknown Authn provider: socache
Unknown Authn provider: dbd
Can't load driver file apr_dbd_pgsql.so

Now we need to add the database connection and authentication details in our Apache server or VirtualHost directives. This cannot be done in .htaccess so needs to be either in httpd.conf or the relevant virtual host definition.

<VirtualHost ...>

  ...

  <IfModule mod_dbd.c>

    DBDriver pgsql
    DBDParams "host=localhost port=5432 dbname=db_name user=db_user password=db_passwd"
    DBDMin 1
    DBDKeep 8
    DBDMax 16
    DBDExptime 300

  </IfModule>

  <Directory "/path/to/secure/directory/">

    AuthType Basic
    AuthName "My Secure Directory"
    AuthBasicProvider socache dbd
    AuthnCacheProvideFor dbd
    AuthnCacheContext my-server

    Require valid-user

    AuthDBDUserPWQuery "SELECT password FROM auth_users WHERE username = %s AND active"

  </Directory>

</VirtualHost>

The AuthDBDUserPWQuery setting defines a prepared SQL statement to find the user record and return the (encrypted) password. We've included AND active so that we can easily disable a user account by updating the database. As it's a prepared statement do not add quotes around the %s variable.

Make sure to test the query before restarting Apache as if there are any problems you will see only errors:

AH00632: failed to prepare SQL statements: ERROR ...
AH00633: failed to initialise
AH00636: child init failed!

We've included socache to reduce load on the database after the user has already been authenticated. Apache authorisation applies to all files, including images, CSS and JavaScript, under the secure directory which can result in a lot of separate queries. With socache the cached result is used first if it exists.

There are other options relating to user groups and digest authentication. See the links under References below for details.

All you need to do now is restart Apache:

sudo apache2ctl configtest
sudo apache2ctl graceful

If everything goes well the selected directory should be accessible only to logged in users with credentials matching the auth_users database.

Otherwise, you can debug any errors using the Apache LogLevel directive, and the PostgreSQL log_statement configuration setting.

Similar solutions exist for MySQL and other databases.

Why we aren't using DBD Authentication

What wasn't clear from the start is that when using DBD authentication in Apache, the prepared statement (AuthDBDUserPWQuery) is created for every web server process which in turn uses and ties up database connections.

You can see this in pg_stat_activity in the 'query' column:

...
PREPARE authn_dbd_1 (varchar) AS SELECT password FROM auth_users WHERE username = $1 AND active
PREPARE authn_dbd_1 (varchar) AS SELECT password FROM auth_users WHERE username = $1 AND active
PREPARE authn_dbd_1 (varchar) AS SELECT password FROM auth_users WHERE username = $1 AND active
...

On a busy web server this can add up to hundreds of connections.

So if you do want to use DBD authentication you should think about setting up a dedicated Apache instance on its own port with only a limited number of processes.

That way the main web server won't be piling up prepared statements, and won't even need the dbd and socache modules to be activated.

References

System Access authorization in Apache 2.4
System Apache authorization with dynamic DNS
System Authenticating Apache Logins with PostgreSQL
System Blocking Fake Googlebot and bingbot spiders
System Re-naming vhost files to *.conf for Apache 2.4

< System

Post your comment or question