Create PostgreSQL Database without root privilege

Question

Currently, I use

$ sudo service postgresql start    

to start the PostgreSQL server and

$ sudo -u postgres createdb testdb --owner ownername

to create a database. However, these commands need root privilege. How can I do these without root privilege/sudo on Linux (Ubuntu)?

Answer 1

You can run PostgreSQL without root privs by creating a new instance (which PostgreSQL calls a "cluster") and starting it.

You can't use the Ubuntu init scripts, wrapper tools like pg_ctlcluster, etc if you do this. You must use only PostgreSQL's own tools.

To create the new PostgreSQL instance with the superuser equal to your username, data directory in your home directory, and md5 auth enabled by default, use:

initdb -D $HOME/my_postgres -A md5 -U $USER

Adjust as desired; see initdb --help.

You'll then need to edit postgresql.conf to change the port to a non-default one, since your system probably runs its own postgres on the default port 5432. (If you want to limit access strictly to you, you can instead set listen_addresses = '' and unix_socket_directories = /home/myuser/postgres_socket or whatever. But it's simpler to just use a different port.)

To start it:

pg_ctl -D $HOME/my_postgres -w start

To connect to it, specify the port you chose:

psql -p 5434 ...

(If you changed unix_socket_directories you'll also want to specify the path you gave, like -h /home/myuser/postgres_socket.)

To make psql etc connect to your postgres by default, edit your ~/.bashrc to add something like

export PGPORT=5434

but note that'll also affect the default port for connections to other hosts.

To stop it:

pg_ctl -D $HOME/my_postgres -w stop

but you can also just shut down without stopping it, it doesn't care and will recover safely when you start it next.

To autostart it when you log in when it's set up in your home directory you'd have to use your desktop environment's run-at-startup features. They vary depending on environment and version so I can't give details here; it's different for GNOME 3, Unity (ubuntu), KDE, XFCE, etc.

Note that this approach still uses the system packages for PostgreSQL. This is important because if you uninstall (say) PostgreSQL 9.4 and install 9.6, your copy in your home dir will stop working. If you want it entirely independent of system packages, as you probably do if you don't control the system, you should compile PostgreSQL from source or use the binary installer to install inside your home directory.

Answer 2

Postgres can run without root permission. Just download from https://www.enterprisedb.com/download-postgresql-binaries

and run

Init database

./initdb -D /data

Run postgres

./bin/postgres -D /data

Create database

./bin/createdb mydb

Connect with psql

./bin/psql mydb

(https://www.golery.com/pencil/vU)