Private PostgreSQL instance

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


Create the database

Let's create directory for our database:

mkdir ~/mydb
cd ~/mydb

We'll 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.

PGDATA="$(pwd)" ; export PGDATA # for initdb, postgresql, pg_ctl
PGHOST="$(pwd)" ; export PGHOST # for psql

We can now create the PostgreSQL database:


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

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


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;

Now we can connect to this database with:


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  md5
hostnossl   all    $USER  ::1/128       md5

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