Use Postgresql without Root

In my last post , I explained how to use MaraiDB without root. Now I’ll show you how to do that with PostgreSQL with vanilla postgresql package from arch’s repo. This may also apply to other Linux distros, but I haven’t tried it.

I’m going to use ~/.data/postgres as my data location. You can replace it with something else if you want.

Now Install PostgreSQL from repo

❯ sudo pacman -S postgresql                

then Initialize it like this

❯ initdb -D .data/postgres/data                                            

Now add following line to .data/postgres/data/postgresql.conf

unix_socket_directories = '/run/user/1000'

This will set Unix Socket location for PostgreSQL server. I choose $XDG_RUNTIME_DIR but you can choose whatever you like /tmp

Now you start the PostgreSQL server

❯ pg_ctl -D .data/postgres/data start

Now connect to server

❯ psql -h $XDG_RUNTIME_DIR -d postgres

-h specified the Unix socket Dir and -dspecifies database to which connect to.

Now set password

\password smit

Also create database with your username so you don’t have to mention which database to connect to each time

create database smit

Now you can connect to your PostgreSQL server but it will not ask for password. By default, it will trust all Unix socket and localhost connection. While developing, we connect to database with remote connection which expects the password. So I like to have my PostgreSQL server ask for password each time I connect from network.

# "local" is for Unix domain socket connections only                           
local   all             all                                     trust          
# IPv4 local connections:                                                      
host    all             all             127.0.0.1/32            md5            
# IPv6 local connections:                                                      
host    all             all             ::1/128                 md5            
# Allow replication connections from localhost, by a user with the             
# replication privilege.                                                       
local   replication     all                                     trust          
host    replication     all             127.0.0.1/32            md5   
host    replication     all             ::1/128                 md5

md5 means it will ask for password. I don’t know why they call it unstrust or something else.

Now to connect, without password,

❯ psql -h $XDG_RUNTIME_DIR            

and with password

❯ psql -h localhost