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

To change the password of the embedded PostgreSQL database, perform the following steps as the lockss user [1] in the lockss user's lockss-installer directory:

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

Start the PostgreSQL database container 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 newpassword with your new embedded 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 newpassword at the Password for user LOCKSS prompt. If the password change was successful and you enter newpassword correctly, you will see a PostgreSQL prompt similar to:
```
psql (9.6.12)
Type "help" for help.

postgres=#
```
which you can exit by entering 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 embedded 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.

Footnotes