Private PostgreSQL instance

How to create a private on-demand PostgreSQL instance accessible only for the local user over UNIX socket.

Setup

Create the database

Let's create directory for our database:

mkdir ~/mydb
cd ~/mydb

We will use some environment variables:

• the PGDATA environment variable can be used to avoid the -D option of initdb, postgres, pg_ctl, etc;
• the PGHOST environment variable can be used to avoid the -h option of psql to choose the base path fox UNIX sockets. This must be an absolute path.

PATH="/usr/lib/postgresql/9.4/bin:$PATH"
PGDATA="$(pwd)" ; export PGDATA # for initdb, postgresql, pg_ctl
PGHOST="$(pwd)" ; export PGHOST # for psql

We can now create the PostgreSQL database:

initdb

Paranoid UNIX socket configuration

Only allow the current user to connect with UNIX socket (and disable IP sockets):

cat >> postgresql.conf <<EOF
# Get off my lawn:
listen_addresses = ''
unix_socket_directories = '.'
unix_socket_permissions = 0700
EOF

On Linux, the unix_socket_permissions prevents any other local user to connect to the socket. On other *nix system, this does not work.

Let's reject any connection from other users (in pg_hba.conf):

echo "local   all    $USER    ident" > pg_hba.conf

Usage

Start the database server:

pg_ctl start

For convenience, let's create a $USER database (because default psql connects to the $USER database with the $USER login):

psql postgres <<EOF
create database $USER;
EOF

Now we can connect to this database with:

psql

Stop the database server:

pg_ctl stop

IP socket support

Many services can only connect over IP. If this is needed, we have to relax the security of the service and allow connections over IP:

Let's listen on the loopback device:

listen_addresses = 'localhost'

Allow connection using the loopback device:

cat << EOF >> pg_hba.conf
hostnossl   all    $USER  127.0.0.1/32  md5
hostnossl   all    $USER  ::1/128       md5
EOF

Set a password for the user:

alter user foo with password 'secret'

Another solution is to use a local ident server with the ident method. This should be safe for the loopback interface if the configuration of the ident server is suitable.

Bonus: CSV import

With SQL syntax (using the server filesystem and working directory):

create table if not exists test(int a, int b);
delete from test;
copy test from 'test.csv' with csv header;

STDIN or PROGRAM 'cat test.csv' can be used as well.

With psql commands (using the client filesystem and working directory):

\copy test from 'test.csv' csv header

Appendix: using Podman

Update 2022-12-06: nowadays you can just use Docker or Podman.

Example:

podman create --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 127.0.0.1:5432:5432 \
  docker.io/library/postgres
podman start postgres
PGPORT=5432 PGHOST=127.0.0.1 PGUSER=postgres psql

Or with Unix socket only reachable from the user:

podman create --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  docker.io/library/postgres
mkdir -p $XDG_RUNTIME_DIR/postgres
podman start postgres
podman unshare sh -c '
  dir="$(podman mount postgres)"
  # Workaround for Unix socket address max length:
  mount --bind "$dir" /mnt/
  socat UNIX-LISTEN:$XDG_RUNTIME_DIR/postgres/.s.PGSQL.5432,fork,umask=077,user=root UNIX:/mnt/run/postgresql/.s.PGSQL.5432
'
PGPORT=5432 PGHOST=$XDG_RUNTIME_DIR/postgres PGUSER=postgres psql