{"id":12472,"date":"2019-05-31T05:25:53","date_gmt":"2019-05-31T03:25:53","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/securely-store-passwords-in-postgresql\/"},"modified":"2019-05-31T05:25:53","modified_gmt":"2019-05-31T03:25:53","slug":"securely-store-passwords-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/securely-store-passwords-in-postgresql\/","title":{"rendered":"Securely store passwords in PostgreSQL"},"content":{"rendered":"

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.<\/p>\n

<\/p>\n

You might be already aware that PostgreSQL comes with a lot of additional modules<\/a> by default. One of these modules is pgcrypto<\/a> 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: <\/p>\n

\npostgres=# create table app_users ( id int generated always as identity ( cache 10 ) primary key\npostgres(#                        , username text not null unique\npostgres(#                        , password text not null\npostgres(#                        );\nCREATE TABLE\npostgres=# d app_users\n                         Table \"public.app_users\"\n  Column  |  Type   | Collation | Nullable |           Default            \n----------+---------+-----------+----------+------------------------------\n id       | integer |           | not null | generated always as identity\n username | text    |           | not null | \n password | text    |           | not null | \nIndexes:\n    \"app_users_pkey\" PRIMARY KEY, btree (id)\n    \"app_users_username_key\" UNIQUE CONSTRAINT, btree (username)\npostgres=# \n<\/pre>\n
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:<\/p>\n
\npostgres=# create extension pgcrypto;\nCREATE EXTENSION\npostgres=# dx\n                   List of installed extensions\n    Name    | Version |   Schema   |         Description          \n------------+---------+------------+------------------------------\n pg_prewarm | 1.2     | public     | prewarm relation data\n pgcrypto   | 1.3     | public     | cryptographic functions\n plpgsql    | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(3 rows)\n<\/pre>\n
Btw: There is a catalog view which you can use to list all available extensions:<\/p>\n
\npostgres=# d pg_available_extensions;\n         View \"pg_catalog.pg_available_extensions\"\n      Column       | Type | Collation | Nullable | Default \n-------------------+------+-----------+----------+---------\n name              | name |           |          | \n default_version   | text |           |          | \n installed_version | text | C         |          | \n comment           | text |           |          | \n\npostgres=# select * from pg_available_extensions limit 3;\n  name   | default_version | installed_version |                comment                 \n---------+-----------------+-------------------+----------------------------------------\n plpgsql | 1.0             | 1.0               | PL\/pgSQL procedural language\n plperl  | 1.0             |                   | PL\/Perl procedural language\n plperlu | 1.0             |                   | PL\/PerlU untrusted procedural language\n(3 rows)\n<\/pre>\n
The function to use (provided by the pgcrypto module) for encrypting strings is crypt(). This function takes two arguments:<\/p>\n