Quickly setting up PostgreSQL for running without admin permissions

February 15, 2018 -
Tags: databases, postgresql, sysadmin

It’s very convenient to run service processes (for development purposes!) without admin permissions, rather starting it as system service.

This guide will show how to easily setup PostgreSQL (both via package and binary tarball) to be run by an unprivileged user, with the data in any directory owned by him/her.


Setup notes/requirements

The binary package section applies to Ubuntu, but it can be trivially adapted to other O/Ss; the binary tarball procedure is essentially universal.

The procedure is run as the local user, in a modern Bash shell; sudo is provided where required. The standard Unix tools (and Ruby) are used.

The $PGDATA directory name must not contain quotes (which would complicate the commands).

Installation via apt package (Ubuntu)

First, set a few variables, for convenience:

$ export PGSQL_VERSION=10
$ export PGDATA="$HOME/databases/pgsql_data"

Both can be adjusted to the user preference.

Install the package, if not done already:

$ wget -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

$ sudo apt-get update
$ sudo apt-get install -y postgresql-$PGSQL_VERSION

Prevent the service from starting on boot (Ubuntu 16.04+ version):

$ sudo systemctl stop postgresql
$ sudo systemctl disable postgresql

Now, initialize the data/configuration/run directory:

$ "/usr/lib/postgresql/$PGSQL_VERSION/bin/initdb"

Set the run path in the main configuration file:

$ ruby -i -pe "sub /^#(unix_socket_directories = ).*/, %q(\1'$PGDATA')" "$PGDATA/postgresql.conf"

Note that we use Ruby for using slashes and quotes without having to perform complex quoting (with Perl/Sed, we would have to).

Now PostgreSQL can be started via:

$ "/usr/lib/postgresql/$PGSQL_VERSION/bin/pg_ctl" start

Ready to connect!:

$ psql -h localhost postgres

Check out the last section for persisting the data location, and adding the binaries to the $PATH!

Installation via binary tarball (universal)

First, variables setup:

$ export PGDATA="$HOME/databases/pgsql_data"
$ export LOCAL_LIB="$HOME/local"
$ export PGSQL_VERSION=10
$ export PGSQL_TARBALL_LINK="https://get.enterprisedb.com/postgresql/postgresql-10.2-1-linux-x64-binaries.tar.gz"

The $LOCAL_LIB path is the preferred location for software used by the current user only.

For the latest PGSQL version, check the download page.

Download and uncompress the tarball:

$ tar xv -C "$LOCAL_LIB" -f !$

Initialize the data/configuration/run directory:

$ "$LOCAL_LIB/pgsql/bin/initdb"

With the binary tarball version, we don’t need to change any configuration!

Now, start PostgreSQL:

$ "$LOCAL_LIB/pgsql/bin/pg_ctl" start

and connect via client:

$ "$LOCAL_LIB/pgsql/bin/psql" -h localhost postgres

Check out the last section for persisting the data location, and adding the binaries to the $PATH!

Persisting the PGSQL data location, and adding the binaries to the $PATH

In order to persist the PGSQL data location, and add the binaries to the $PATH for convenience, we need to extend the shell init scripts.

Shell init scripts vary slightly dependign on the login conditions/configuration. For simplicity, extend $HOME/.bashrc.

The env variable $PGDATA tells PGSQL where the data resides:

echo 'export PGDATA="$HOME/databases/pgsql_data"' >> "$HOME/.bashrc"

It’s convenient to have the PGSQL binaries in the path as well, in order to avoid having to write the full path:

# For apt package installations
echo "export PATH=/usr/lib/postgresql/$PGSQL_VERSION/bin:\$PATH" >> "$HOME/.bashrc"

# For binary tarball installations
$ export LOCAL_LIB="$HOME/local"
$ echo "export PATH=$LOCAL_LIB:\$PATH" >> "$HOME/.bashrc"


Have fun with PostgreSQL!