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)?

• Thilo: If you want to run things a system service you need root. But you can run Postgresql locally under your own user account just fine. And createdb does not need root, either (just access to Postgres as a Postgres admin user, which can be done by any OS user or even a remote user)
• cstjh: @Thilo Can you explain how to run Postgresql locally?

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.

• Jordan Kasper: Alternate idea for the port configuration: change the ownership of /var/run/postgresql to yourself: sudo chown -R jordan:jordan /var/run/postgresql However, only do that if you don't plan to ever run postgres as that user on your system, because then it can't run on port 5432.

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)

• Krusty the Clown: 2023 disclaimer: The binaries only go up to version 10.23