System: Authenticating Apache Logins with PostgreSQL
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
- the name of our PostgreSQL database.
- username for connecting to the database.
- 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 hashed 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.
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!
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.
Related Articles - Apache 2.4
- Access authorization in Apache 2.4 [SYSTEM]
- Apache authorization with dynamic DNS [SYSTEM]
- Re-naming vhost files to *.conf for Apache 2.4 [SYSTEM]
- Authenticating Apache Logins with PostgreSQL [SYSTEM]
- Managing users and authentication in Apache with PostgreSQL
- How to use pgcrypto to further protect your data
- Hashed Passwords with PostgreSQL's pgcrypto
- Apache Module mod_dbd
- Apache Module mod_authn_dbd - Configuration Example
- Apache Server Password Formats
- Apache Module mod_authn_socache
Send a message to The Art of Web:
press <Esc> or click outside this box to close