nethserver-postgres

PostgreSQL is a database server and it listens on standard port 5432.

Access policy

From localhost:

  • the postgres user can access without password
  • all other users must use password authentication

From any other network:

  • all users must use password authentication

Example for accessing with postgres user:

su - postgres
psql

Backup

Backup and restore is not implemented system-wide: every application should provide its own scripts.

Backup actions must be linked inside the pre-backup-data event. Example of backup action:

#!/bin/bash

su - postgres -c "pg_dump myapp > /var/lib/nethserver/myapp/myapp.sql"

Restore actions must be linked inside the post-restore-data event. Example of restore action:

#!/bin/bash

if [ -f /var/lib/nethserver/myapp/myapp.sql ]; then
  drop_sql=`mktemp`
  chown postgres:postgres $drop_sql
  # drop all existing connections to the db and block new ones
  echo "UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'myapp';" >> $drop_sql
  echo "SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'myapp';" >> $drop_sql
  # drop the db, then recreate it
  echo "DROP DATABASE myapp;" >> $drop_sql
  password=`perl -e "use NethServer::Password; print NethServer::Password::store('myapp');"`
  echo "CREATE database myapp; CREATE USER sonicle WITH PASSWORD '$password'; GRANT ALL PRIVILEGES ON DATABASE myapp to myuser;" >> $drop_sql
  # allow new connections to db
  echo "UPDATE pg_database SET datallowconn = 'true' WHERE datname = 'myapp';" >> $drop_sql
  su - postgres -c "psql < $drop_sql"
  su - postgres -c "psql myapp < /var/lib/nethserver/myapp/myapp.sql"
  rm -f $drop_sql
fi