Securely store passwords in PostgreSQL

Every application somehow needs to deal with passwords. Some use external authentication methods such as ldap, others us the the framework the database already provides and create users and roles. But it is also not uncommon that applications implement their own concept for managing users. If an application does this it should be done the right way and passwords should never be stored in plain text in the database. PostgreSQL comes with a handy extension that supports you with that.

You might be already aware that PostgreSQL comes with a lot of additional modules by default. One of these modules is pgcrypto and it can be used for the use case described above: En- and decryption of strings so you do not have to implement that on your own. Lets start with a simple table which contains usernames and their passwords:

postgres=# create table app_users ( id int generated always as identity ( cache 10 ) primary key
postgres(#                        , username text not null unique
postgres(#                        , password text not null
postgres(#                        );
CREATE TABLE
postgres=# d app_users
                         Table "public.app_users"
  Column  |  Type   | Collation | Nullable |           Default            
----------+---------+-----------+----------+------------------------------
 id       | integer |           | not null | generated always as identity
 username | text    |           | not null | 
 password | text    |           | not null | 
Indexes:
    "app_users_pkey" PRIMARY KEY, btree (id)
    "app_users_username_key" UNIQUE CONSTRAINT, btree (username)
postgres=# 

Both, the username and password columns are implement as plain text. If you keep it like that and just insert data the password of course will be stored as plain text. So how can we use pgcrypto to improve that? Obviously the first step is to install the extension:

postgres=# create extension pgcrypto;
CREATE EXTENSION
postgres=# dx
                   List of installed extensions
    Name    | Version |   Schema   |         Description          
------------+---------+------------+------------------------------
 pg_prewarm | 1.2     | public     | prewarm relation data
 pgcrypto   | 1.3     | public     | cryptographic functions
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

Btw: There is a catalog view which you can use to list all available extensions:

postgres=# d pg_available_extensions;
         View "pg_catalog.pg_available_extensions"
      Column       | Type | Collation | Nullable | Default 
-------------------+------+-----------+----------+---------
 name              | name |           |          | 
 default_version   | text |           |          | 
 installed_version | text | C         |          | 
 comment           | text |           |          | 

postgres=# select * from pg_available_extensions limit 3;
  name   | default_version | installed_version |                comment                 
---------+-----------------+-------------------+----------------------------------------
 plpgsql | 1.0             | 1.0               | PL/pgSQL procedural language
 plperl  | 1.0             |                   | PL/Perl procedural language
 plperlu | 1.0             |                   | PL/PerlU untrusted procedural language
(3 rows)

The function to use (provided by the pgcrypto module) for encrypting strings is crypt(). This function takes two arguments:

• The actual string to encrypt
• The salt to use (a random value) for encrpyption

Adding a user with an encrypted password is as easy as:

postgres=# insert into app_users (username, password) 
postgres-#        values ( 'myuser', crypt('mypassword', gen_salt('bf')) );
INSERT 0 1

In this case we used the Blowfish algorithm to generate the salt. You can also use md5, xdes and des.

When we look at the password for our user we will see that it is not plain text anymore:

postgres=# select password from app_users where username = 'myuser';
                           password                           
--------------------------------------------------------------
 $2a$06$8wu4VWVubv/RBYBSuj.1TOojPm0q7FkRwuDSoW0OTOC6FzBGEslIC
(1 row)

This is for the encryption part. For comparing this encrypted string against the plain text version of the string we use the crypt() function again:

postgres=# select (password = crypt('mypassword', password)) AS pwd_match 
postgres-#   from app_users
postgres-#  where username = 'myuser';
 pwd_match 
-----------
 t
(1 row)

Providing the wrong password of course returns false:

postgres=# select (password = crypt('Xmypassword', password)) AS pwd_match 
  from app_users
 where username = 'myuser';
 pwd_match 
-----------
 f
(1 row)

So finally, it is not much you need to do for storing encrypted strings in PostgreSQL. Just use it.