# 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_dtcp`reports 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](https://github.com/theory/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` <br>
`cd pg_dctp` <br>
`export USE_PGXS=1`<br>
`make` <br>
`make install` <br>

This module can be tested with:
`make installcheck`. <br>

This module must be loaded at server level with `shared_preload_libraries` parameter:
<br><br>
`shared_preload_libraries = 'pg_dctp'`
<br><br>

## 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;
```