pg_dctp

pg_dctp (which stands for PG Disable Clear Text Passwords) is a PostgreSQL module which forbids usage of clear text password in CREATE USER and ALTER USER statements. This is a workaround for PostgreSQL default behavior that might display non encrypted passwords in PostgreSQL logs for example if log_statement is set to àll.

When pg_dtcpreports an error, log_min_messages is temporarily changed to avoid PostgreSQL to log the error message with the corresponding password.

Installation

Compiling, installing and testing with PGXS

Note that pg_dctp is not an extension because it does not install any SQL object: it only loads new C routines in the PostgreSQL server.

This module can be built using the standard PGXS infrastructure for example with pgenv. Because TAP testing is used you need to enable TAP tests when building PostgreSQL. For example with pgenv, use: PGENV_CONFIGURE_OPTIONS=([0]="--enable-debug" [1]="--with-uuid=e2fs" [2]="--with-openssl" [3]="--enable-tap-tests") Compile module with:

git clone https://github.com/pierreforstmann/pg_dctp.git
cd pg_dctp
export USE_PGXS=1
make
make install

This module can be tested with: make installcheck.

This module must be loaded at server level with shared_preload_libraries parameter:

shared_preload_libraries = 'pg_dctp'

Validated PostgreSQL versions

This module has been validated with PostgreSQL 14, 15, 16, 17, and 18.

Usage

After installation, using clear test password should fail:

pierre=# create user test password 'abc123';
ERROR:  CREATE USER ... PASSWORD is not allowed with non encrypted password.

pierre=# alter user test password 'abc123';
ERROR:  ALTER USER ... PASSWORD is not allowed with non encrypted password.

How to use encrypted password

To change password using encryption for an existing account you can use: - \password in psql - chpasswd and resetpasswd from https://github.com/pierreforstmann/pgpasswd.

You can also create new account using encrypted password with createuser:

createuser -P -s -e test
Enter password for new role: 
Enter it again: 
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE test PASSWORD 'SCRAM-SHA-256$4096:R3UBBT6VhhuzG81+6evX/g==$B87w8MCrMkQw/QM1tz4ii8djgBjtA+WkoO/XfburWi4=:6qc1InGYYKY9ZhZZOFzgHXd/sSH370BmYLjBjXwOA1o=' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS;