--- tags: SET --- # Postgresql hardening There are two main solutions for achieving network encryption and password enhancement - Use SSL in `verify-full` or `verify-ca` modes to encrypt communication, and use checkpassword extension and password valid time to enhanch password security - Use SSL in `verify-full` mode to do both encryption and authentication without passwords ## Upgrade password-based authentication Postgresql default authentication method is MD5 which is not recommended for production so we would use a stronger method to enhance security 1. Update `$PGDATA/postgresql.conf` to change method ```vim=== password_encryption = 'scram-sha-256' ``` 2. And change method in file`$PGDATA/postgresql.conf` from `md5` to `scram-sha-256` ```vim=== host all all <ip>/32 scram-sha-256 ``` 3. Reset password all users ```psql=== \password <user> or ALTER USER <user> WITH PASSWORD '<plaintext>'; ``` NOTE: to enable this feature we have to make sure all clients support. ## use password checker `passwordcheck` is an extension that check password following criteria. - length have to be larger than 8 characters - password have to combined with both numbers and aphabets - username could not appear in password Steps to enable the extension 1. update ponstgresql.conf ```vim=== shared_preload_libraries='$libdir/passwordcheck' ``` 2. Restart postgresql ```bash=== systemctl restart postgresql-13.service ``` NOTE: - Checking would not be applied when changing password with `\password`. ### ~~or to use cracklib to check password~~ Caution: there are some issue on centos 8 1. install extension ```bash=== dnf install passwordcheck_cracklib_13 ``` 2. update `postgresql.conf` ```vim=== shared_preload_libraries='$libdir/passwordcheck_cracklib' ``` 3. Restart postgresql ```bash=== systemctl restart postgresql-13.service ``` ### Set password expiration To avoid using password too many long time we could set expiration time like the query below ```psql=== ALTER USER foo WITH PASSWORD 'a12345678' VALID UNTIL '2022-03-31 19:30:27.161232+00'; ``` And before expiration time admin have to change passwords ## Secure TCP/IP Connections with SSL #### setup server key/cert 1. Generate root key and certificate ```bash=== openssl req -new -nodes -text -out root.csr \ -keyout root.key -subj "/CN=ca-root" # Allow server cert for 10 years openssl x509 -req -in root.csr -text -days 3650 \ -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \ -signkey root.key -out root.crt ``` 2. Generate server key and certificate ```bash=== openssl req -new -nodes -text -out server.csr \ -keyout server.key -subj "/CN=<postgresql ip address>" chmod og-rwx server.key # Allow server cert for a year openssl x509 -req -in server.csr -text -days 365 \ -CA root.crt -CAkey root.key -CAcreateserial \ -out server.crt ``` 3. Copy server key/cert to $PGDATA ```bash=== cp server.key $PGDATA cp server.crt $PGDATA ``` 4. Backup root.key and remove from host 5. Make sure `pg_hba.conf` is append a rule for access via ssl ```vim=== hostssl all all 0.0.0.0/0 scram-sha-256 ``` 6. Restart server and then secure connection in `verify-ca` mode is ready to be used ```bash=== systemctl restart postgresql-13.service ``` 7. Then test connection on other server with password ```bash=== mkdir .postgresql cp /path/to/root.crt .postgresql # Connect docker run -it --rm -v ${PWD}/.postgresql:/root/.postgresql postgres:13 psql -U postgres -h <postgres_server> "sslmode=verify-ca" ``` ### Certificate Authentication Steps to use certification instead of passwords are below 8. Set CA file to allow Postgresql to accept request from cert that sign by root ca ```bash=== cp path/to/root.cert $PGDATA ``` Postgresql.conf ```vim=== ssl_ca_file = 'root.crt' # this specific which CA certificate to trust ``` 9. Generate key and certification for client ```bash=== openssl req -new -nodes -out client.csr \ -keyout client.key -subj "/CN=<username>" chmod og-rwx client.key # generate cerification openssl x509 -req -in client.csr -days 365 \ -CA root.crt -CAkey root.key -CAcreateserial \ -out client.crt ``` 10. Add pg_hbg.conf an entry to allow connect by cert ```vim=== hostssl all all 0.0.0.0/0 cert ``` 11. Restart postgresql 12. Try to connect by certification without password required By docker ```bash=== mkdir .postgresql cp root.crt .postgresql cp client.crt .postgresql/postgresql.crt cp client.key .postgresql/postgresql.key docker run -it --rm -v ${PWD}/.postgresql:/root/.postgresql postgres:13 psql -U <user> -d <db> -h <hostname> "sslmode=verify-full" ``` Reference: - https://blog.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers