PostgreSQL Client Provisioning

This host publishes the database provisioning script and the PostgreSQL operational files used on user2804.openweb.direct.

Use the provisioning script on the server to create one database and one admin login per client. Use the operational files here as reference and for manual maintenance.

Host: user2804.openweb.direct:5432

Downloads

provision-postgresql-client.sh Server-side script to create a client database and admin login. postgresql-nightly-backup.sh Nightly logical backup script. postgresql-nightly-backup.service Systemd unit that runs the backup job. postgresql-nightly-backup.timer Systemd timer that schedules backups daily at 02:15. postgresql-cert-refresh.sh Certbot deploy hook that refreshes PostgreSQL TLS files. postgresql-remote-access.md Connection reference for apps, admins, and client roles.

Provisioning Script

Use this on the server to create a new client database and a database owner login with its own password.

  1. SSH into this server as an administrative user.
  2. Run the script with a database name, admin username, and password.
  3. Copy the returned host, port, database, and username values into the client application.
  4. Tell client apps to connect over TLS with hostname verification.
/usr/local/bin/provision-postgresql-client.sh client_db client_admin 'StrongPasswordHere'

Identifier rule: use letters, numbers, and underscores only, and start with a letter or underscore.

Connection Pattern

Give apps one role per database. Do not use the cluster admin role in application code.

postgresql://USERNAME:PASSWORD@user2804.openweb.direct:5432/DATABASE_NAME?sslmode=verify-full

For psql and other libpq clients on Linux, add the CA bundle:

sslrootcert=/etc/ssl/certs/ca-certificates.crt

Detailed Usage: provision-postgresql-client.sh

  1. Download the script if you want a local reference: 
    curl -O https://user2804.openweb.direct/downloads/provision-postgresql-client.sh
  2. Run it on the PostgreSQL server itself: 
    sudo /usr/local/bin/provision-postgresql-client.sh acme_app acme_admin 'ChangeThisPasswordNow!'
  3. Read the output. It prints the host, port, database, admin username, SSL mode, and a ready-to-use psql example.
  4. Share the client-specific credentials with the application owner, not the cluster admin role.
  5. Use the returned database and username in the app connection settings.
  6. Test from the client app or another server with: 
    PGPASSWORD='ChangeThisPasswordNow!' psql "host=user2804.openweb.direct port=5432 dbname=acme_app user=acme_admin sslmode=verify-full sslrootcert=/etc/ssl/certs/ca-certificates.crt"

Detailed Usage: Operational Files

postgresql-nightly-backup.sh

Runs a full logical backup using pg_dumpall, compresses it, and keeps 14 days.

sudo /usr/local/bin/postgresql-nightly-backup.sh
ls -lh /var/backups/postgresql

postgresql-nightly-backup.service

Runs the backup script once on demand through systemd.

sudo systemctl start postgresql-nightly-backup.service
sudo journalctl -u postgresql-nightly-backup.service -n 50 --no-pager

postgresql-nightly-backup.timer

Schedules the backup job daily at 02:15 server time.

sudo systemctl status postgresql-nightly-backup.timer --no-pager
sudo systemctl list-timers postgresql-nightly-backup.timer

postgresql-cert-refresh.sh

Triggered by certbot after certificate renewal. It copies the refreshed certificate into PostgreSQL's TLS path and restarts PostgreSQL.

sudo sh /etc/letsencrypt/renewal-hooks/deploy/postgresql-cert-refresh.sh

postgresql-remote-access.md

Reference guide for remote applications, client roles, SSL mode, and connection examples.

curl -O https://user2804.openweb.direct/downloads/postgresql-remote-access.md