9.7. Working with PostgreSQL

This section of the appendix documents administrative tasks for the embedded PostgreSQL database configured by the LOCKSS 2.x system.

9.7.1. Changing the PostgreSQL Database Password

Changing the password of the embedded PostgreSQL database must done while the LOCKSS stack is stopped, requiring an interruption of service. To perform the change, perform the following steps as the lockss user [1] in the LOCKSS Installer Directory:

Ensure the LOCKSS stack is stopped by running:
```
scripts/stop-lockss
```
Ensure the Kubernetes service definitions reflect the current state of the LOCKSS configuration by running:
```
scripts/assemble-lockss
```

Start only the PostgreSQL database container of the LOCKSS stack by running:

k3s kubectl apply -n lockss --filename=config/configs/lockss-stack/svcs/lockss-postgres-service.yaml

Run the following command to store the name of the PostgreSQL database container into the variable postgres_pod:

postgres_pod=$(k3s kubectl get pod -n lockss --selector=io.kompose.service=lockss-postgres-service --output=jsonpath="{.items[0].metadata.name}")

Run the following command to store the IP of the PostgreSQL database container into the variable postgres_ip:

postgres_ip=$(k3s kubectl get pod -n lockss --selector=io.kompose.service=lockss-postgres-service --output=jsonpath="{.items[0].status.podIP}")

Execute the following command to alter the LOCKSS database user's password, taking care to replace the placeholder newpassword below with your intended new PostgreSQL database password:
```
echo "ALTER USER \"LOCKSS\" WITH PASSWORD 'newpassword'" | k3s kubectl exec $postgres_pod -n lockss -i -- psql --username=LOCKSS --dbname=postgres
```
Successful execution of the command results in the output ALTER ROLE.
To verify that the password change worked, run the following command:
```
k3s kubectl exec $postgres_pod -n lockss -it -- psql --username=LOCKSS --dbname=postgres --host=$postgres_ip
```
and enter your new password (represented here as newpassword) at the Password for user LOCKSS prompt.
- If the password change was successful and you enter your new password correctly, you will see a PostgreSQL prompt similar to:
```
psql (9.6.12)
Type "help" for help.

postgres=#
```
  which you can now exit by entering \quit (or \q) or hitting Ctrl+D.
- If the password change was unsuccessful or you do not enter newpassword correctly, you will see output similar to:
```
psql: FATAL:  password authentication failed for user "LOCKSS"
command terminated with exit code 2
```

Stop the PostgreSQL database container by running this command:

k3s kubectl -n lockss delete service,deployment lockss-postgres-service &&
    k3s kubectl -n lockss wait --for=delete pod $postgres_pod --timeout=60s

Re-run configure-lockss so that you can record the new PostgreSQL database password into the configuration of the LOCKSS stack:
```
scripts/configure-lockss
```
See the PostgreSQL and Embedded PostgreSQL Database sections of Configuring the LOCKSS System for details.
You can then restart your LOCKSS stack if applicable:

scripts/start-lockss
See Starting the LOCKSS System for details.

Footnotes