Package: postgresql-common Version: 190 Severity: wishlist Tags: patch Some user accounts might run a daemon (a "user service") that wants its own postgresql cluster without relying on the system cluster.
For example, there are daemons that handle sensitive data that they don't want another system account to access, or that they want to be able to tightly control the backup schedule for. Alternately, the system cluster might be configured and in use for a different purpose; so the user service might not be able to easily get access to it. The attached patch makes it easier to support these use cases on Debian systems that run systemd. In particular, a user .service named "exampled" which keeps its state in `~/.config/example`. An `exampled.service` unit might be configured with: Requires=pgcluster@example.service After=pgcluster@example.service So that if the user service is ever started, the cluster will be created on demand (in `~/.config/example/data`) and the "example" database will be ready for use (via unix-domain sockets in `$XDG_RUNTIME_DIR/example/`) before the user service is started. Regards, --dkg -- System Information: Debian Release: buster/sid APT prefers testing-debug APT policy: (500, 'testing-debug'), (500, 'testing'), (500, 'oldstable'), (200, 'unstable-debug'), (200, 'unstable'), (1, 'experimental-debug'), (1, 'experimental') Architecture: amd64 (x86_64) Foreign Architectures: i386 Kernel: Linux 4.14.0-3-amd64 (SMP w/4 CPU cores) Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8), LANGUAGE=en_US.UTF-8 (charmap=UTF-8) Shell: /bin/sh linked to /bin/dash Init: systemd (via /run/systemd/system) Versions of packages postgresql-common depends on: ii adduser 3.117 ii debconf [debconf-2.0] 1.5.65 ii lsb-base 9.20170808 ii postgresql-client-common 190 ii procps 2:3.3.12-4 ii ssl-cert 1.0.39 ii ucf 3.0037 Versions of packages postgresql-common recommends: ii e2fsprogs 1.43.9-1 ii logrotate 3.11.0-0.1 Versions of packages postgresql-common suggests: ii libjson-perl 2.97001-1 -- Configuration Files: /etc/postgresql-common/createcluster.conf changed [not included] -- debconf information excluded
>From 6db58c09704dc61310e4dda9607afcfc9f1dab65 Mon Sep 17 00:00:00 2001 From: Daniel Kahn Gillmor <d...@fifthhorseman.net> Date: Tue, 27 Feb 2018 19:26:46 -0500 Subject: [PATCH] Implement easily-managed user clusters under systemd Some user services might want their own local postgresql cluster. This makes it easy for those services to spin up a dedicated cluster + db on demand. --- Makefile | 1 + debian/postgresql-common.install | 3 + debian/postgresql-common.links | 1 + debian/postgresql-common.manpages | 1 + pgcluster@.service | 8 +++ postgres-user-cluster | 59 ++++++++++++++++++ psql-user-cluster | 5 ++ psql-user-cluster.pod | 128 ++++++++++++++++++++++++++++++++++++++ 8 files changed, 206 insertions(+) create mode 100644 pgcluster@.service create mode 100755 postgres-user-cluster create mode 100755 psql-user-cluster create mode 100644 psql-user-cluster.pod diff --git a/Makefile b/Makefile index 6ee9adf..402c2ed 100644 --- a/Makefile +++ b/Makefile @@ -9,6 +9,7 @@ POD1PROGS = pg_conftool.1 \ pg_wrapper.1 POD1PROGS_POD = pg_buildext.1 \ pg_virtualenv.1 \ + psql-user-cluster.1 \ dh_make_pgxs/dh_make_pgxs.1 POD8PROGS = pg_updatedicts.8 diff --git a/debian/postgresql-common.install b/debian/postgresql-common.install index ebd0c50..caacc79 100644 --- a/debian/postgresql-common.install +++ b/debian/postgresql-common.install @@ -12,7 +12,10 @@ pg_renamecluster usr/bin pg_updatedicts usr/sbin pg_upgradecluster usr/bin pg_virtualenv usr/bin +pgcluster@.service usr/lib/systemd/user pgdg usr/share/postgresql-common +postgres-user-cluster usr/share/postgresql-common +psql-user-cluster usr/bin run-upgrade-scripts usr/share/postgresql-common t/* usr/share/postgresql-common/t testsuite usr/share/postgresql-common diff --git a/debian/postgresql-common.links b/debian/postgresql-common.links index 2bf1bda..41d67c8 100644 --- a/debian/postgresql-common.links +++ b/debian/postgresql-common.links @@ -1 +1,2 @@ usr/share/postgresql-common/pg_wrapper usr/bin/pg_archivecleanup +usr/share/man/man1/psql-user-cluster.1 usr/share/man/man1/pgcluster@.service.1 diff --git a/debian/postgresql-common.manpages b/debian/postgresql-common.manpages index 4b21c26..1778484 100644 --- a/debian/postgresql-common.manpages +++ b/debian/postgresql-common.manpages @@ -7,3 +7,4 @@ pg_renamecluster.1 pg_updatedicts.8 pg_upgradecluster.1 pg_virtualenv.1 +psql-user-cluster.1 diff --git a/pgcluster@.service b/pgcluster@.service new file mode 100644 index 0000000..a8f5a3f --- /dev/null +++ b/pgcluster@.service @@ -0,0 +1,8 @@ +[Unit] +Description=User PostgreSQL cluster %I +Documentation=man::postgres(1) +Documentation=man::postgres-user-cluster(1) + +[Service] +Type=notify +ExecStart=/usr/share/postgresql-common/postgres-user-cluster %I %S/%I %t/%I diff --git a/postgres-user-cluster b/postgres-user-cluster new file mode 100755 index 0000000..f89666e --- /dev/null +++ b/postgres-user-cluster @@ -0,0 +1,59 @@ +#!/bin/bash + +# Author: Daniel Kahn Gillmor <d...@fifthhorseman.net> +# Date: 2018-02-27 + +# /usr/share/postgresql-common/postgres-user-cluster DBNAME DBPATH SOCKETDIR + +# Launch a private postgresql cluster (setting it up first if +# necessary), which listens solely on a unix-domain socket. It runs +# in the foreground, and is capable of notifying a user service +# manager (e.g. systemd --user) that it is ready for connections. + +# most typically controlled as a user service. See the +# pgcluster@.service(1) manpage. + +dbname="$1" +mkdir -p "$(dirname "$2")" 2>&1 +dir=$(realpath "$2") +socketdir="$3" +shift 3 + +set -e + +latest_pgversion=$(ls /usr/lib/postgresql | sort -n | tail -n1) + +if [ -d "$dir" ]; then + if ! [ -r "$dir/psql/PG_VERSION" ]; then + printf >&2 "%s does not appear to be a postgresql database\n" "$dir/postgres " + exit 1 + fi +else + "/usr/lib/postgresql/$latest_pgversion/bin/initdb" -D "$dir/postgres" --auth=peer + cat >> "$dir/postgres/postgresql.conf" <<EOF +# no AF_INET or AF_INET6 listeners: +listen_addresses = '' + +# logging is going to be handled by journald; we do not need the date +# (interestingly, journald appears to be confused about the PID, so we +# leave that in): +log_line_prefix = '[%p] %q%u%d' +EOF + # FIXME: can we detect whether this CREATE DATABASE failed or not? Currently this + # can fail without returning an error code (it will log the error, though). + echo create database "$(basename "$dir")" | NOTIFY_SOCKET= "/usr/lib/postgresql/$latest_pgversion/bin/postgres" --single -D "$dir/postgres" postgres +fi +pgversion=$(cat "$dir/postgres/PG_VERSION") + +if [ ! -x "/usr/lib/postgresql/$pgversion/bin/postgres" ]; then + printf >&2 "version %s of postgresql is not installed\n" "$pgversion" + exit 1 +fi + +if [ "$pgversion" != "$latest_pgversion" ]; then + printf >&2 "Database at '%s' is version %s, but version %s is available.\nConsider upgrading!" "$dir/postgres" "$pgversion" "$latest_pgversion" + # FIXME: automate the upgrade for the user? +fi + +mkdir -p "$socketdir" +exec "/usr/lib/postgresql/$pgversion/bin/postgres" -D "$dir/postgres" -c "unix_socket_directories=$socketdir" "$@" diff --git a/psql-user-cluster b/psql-user-cluster new file mode 100755 index 0000000..8df44c8 --- /dev/null +++ b/psql-user-cluster @@ -0,0 +1,5 @@ +#!/bin/bash + +dbname="${@: -1}" +export PGHOST=${XDG_RUNTIME_DIR:-/run/user/"$(id -u)"}/"$dbname" +exec psql "$@" diff --git a/psql-user-cluster.pod b/psql-user-cluster.pod new file mode 100644 index 0000000..4c4d5b8 --- /dev/null +++ b/psql-user-cluster.pod @@ -0,0 +1,128 @@ +=head1 NAME + +pgcluster@.service, psql-user-cluster - PostgreSQL Clusters managed as systemd user services + +=head1 SYNOPSIS + +Ensure that a user-controlled cluster and database exist for another +user service: + + # /usr/lib/systemd/user/example.service + [Unit] + Requires=pgcluster@example.service + After=pgcluster@example.service + ... + +Or, set up and launch a cluster directly from the command-line: + +B<systemctl> B<--user> B<start> B<pgcluster@>I<example>B<.service> + +Connect to such a cluster: + +B<psql-user-cluster> I<example> + +=head1 DESCRIPTION + +Some user accounts might run a daemon (a "user service") that wants +its own postgresql cluster without relying on the system cluster. + +For example, there are daemons that handle sensitive data that they +don't want another system account to access, or that they want to be +able to tightly control the backup schedule for. + +Alternately, the system cluster might be configured and in use for a +different purpose; so the user service might not be able to easily get +access to it. + +For these one-off user-controlled clusters, we can also generally +assume that there will be a single database running. By default, we +can name the databsae the same name as the cluster. + +This framework makes it easier to support these use cases on Debian +systems that run systemd. + +If the local user account wants to start up a cluster named I<foo>, +they just do: + + $ systemctl --user start pgcluster@foo.service + +Then, to access its default I<foo> database from a shell, they can do: + + $ psql-user-cluster foo + +or: + + $ PGHOST=$XDG_RUNTIME_DIR/foo psql foo + +Alternately, consider a user .service named I<exampled> which keeps +its state in B<~/.config/example>. An B<exampled.service> unit might +be configured with: + + # /usr/lib/systemd/user/example.service + [Unit] + Requires=pgcluster@example.service + After=pgcluster@example.service + ... + +So that if the user service is ever started, the cluster will be +created on demand and the I<example> database in that cluster will be +ready for use before `exampled.service` is started. + +=head1 FILES + +B<pgcluster@.service> uses the XDG defaults to figure out where to +place the database server's listening socket (B<XDG_RUNTIME_DIR>) and +database files (B<XDG_CONFIG_HOME>). + +So by default for user 1000, B<pgcluster@foo.service> would open a +listening socket in I</run/user/1000/foo/.s.PSQL.5432>, and would put +its database files in I<~/.config/foo/postgres>. + +Why have an additional B</postgres/> in the path for the database +files? Because a service that needs its own postgresql backend may +have additional configuration or state that it wants to store. It's +cleaner to give the postgresql database a directory to itself. + +=head1 BUGS + +=over 2 + +=item + +B<psql-user-cluster> always assumes the last argument is the +database name. To send a database username, use B<-U> explicitly +(before the database name). + +=item + +This framework assumes that postgresql's multi-version setup is +installed the way it is commonly installed in Debian. If that +changes, it will probably break. + +=item + +B</usr/share/postgresql-common/postgres-user-service> does not upgrade +the user's cluster automatically across postgresql version upgrades. +The user needs to do that manually. + +=item + +If the initial database creation fails, it will be noted in the +journald logs for the service, but it will not cause an explicit error +state. + +=item + +journalctl is apparently confused about which process is emitting +errors or warnings, so we ask postgres to self-report process ID +information in its logs. + +=back + +=head1 SEE ALSO + +postgres(1), initdb(1) + +=head1 AUTHOR + +Daniel Kahn Gillmor L<E<lt>d...@fifthhorseman.nete<gt>> -- 2.16.1