Contents

• disable_set_password
• Installation
  • Compiling, Installing and testing with PGXS
  • Validated PostgreSQL versions
  • Examples

disable_set_password

disable_set_password is a PostgreSQL module which forbids usage of clear 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.

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

When disable_set_passwordreports 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

This module can be built using the standard PGXS infrastructure for example with pgenv :

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

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

shared_preload_libraries = 'disable_set_password'

Validated PostgreSQL versions

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

Examples

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: try \password.

You can still create accounts without passwords and use psql \password command to change password: (provided that pg_hba.conf allows local connections in trust mode):

pierre=# create user test;
CREATE ROLE
pierre=# 

$ psql -U test postgres
psql (18.6)
Type "help" for help.

postgres=> \password
Enter new password for user "test": 
Enter it again: 
postgres=>