Howto Set Up Postgres For Rails Development

EDIT: Cleaned up and rewritten for Postgres 15 in Debian, but should work everywhere else.

This article describes how to install PostgreSQL 15, allow to use password authentication for unix socket connections and to create database user and create database from Rails task.

You can skip “Allow Password Authentication” section if you do not use unix sockets, but go through TCP/IP stack (psql --user postgres -h localhost). Password authentication works there by default.

Steps:

• install
• create user and permissions
• run rails db:setup

Install

# OpenSuse
zypper install postgresql postgresql-contrib postgresql-devel postgresql-server

# Debian
apt-get install postgresql-15 libpq-dev

# MacOS
brew install postgresql@15

Create User and Permissions

Allow Password Authentication

By default Postgres only allows login for postgres user and only from shell - through peer authentication which requires a valid system user. That is not practical for web development. We want to allow password authentication for postgres and all other users.

While it is possible to keep postgres user with peer authentication and other with password auth, I don’t want to switch shells all the time.

There is no root user by default, but there is postgres user with superuser privileges:

root@rapthalia:~# psql --user root
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

And we cannot login as postgres user from root shell (or any other than postgres) by default:

root@rapthalia:~# psql --user postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

To get into the postgres console we need to switch to postgres user and force bash:

root@rapthalia:~# su - postgres
# the line above should work, but if some distro sets disabled postgres use this:
root@rapthalia:~# sudo su -l postgres -s /bin/bash
postgres@rapthalia:~$ psql
psql (15.2 (Debian 15.2-2))
Type "help" for help.

postgres=#

Using peer authentication is not comfortable, because it requires a database user to exist in the system. So we change it to password authentication to get standard user password login. But first we need to set the password to postgres user, because it will ask for password:

ALTER USER postgres WITH ENCRYPTED PASSWORD 'my password';

Then we switch to password authentication. Edit /var/lib/pgsql/data/pg_hba.conf (OpenSuse) or /etc/postgresql/$version$/main/pg_hba.conf (Debian) or find / -iname 'pg_hba.conf' (to find it):

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
# comment this out
#local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
# add this line
local   all             all                                     password
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Comment out peer auth for postgres user and add a line which enables password auth for all users. Peer line on the line below will not work, because password line above catches all users. Allows for unix socket connections. Add password entry for host lines too if you want to use TCP/IP connections.

Save the file, restart the server and now log in with freshly set password (to get into the postgres shell from postgres user will not work anymore!).

root@rapthalia:~# systemctl restart postgresql
root@rapthalia:~# psql --user postgres
# enter 'my password'
Password for user postgres: 
psql (15.2 (Debian 15.2-2))
Type "help" for help.

postgres=#

Create a User For Rails Application

Great! Now we can log in into Postgres superuser with a password. Now we create a superuser for our application to use. Superuser permissions are needed to create the database and to allow extensions (if you use uuid or anything else). It is possible to create database with a superuser and keep it regular, extensions need superuser anyways and everything is just comfortable. Do not use for production systems.

CREATE USER oni_sorceress WITH ENCRYPTED PASSWORD 'my password';
ALTER USER oni_sorceress WITH SUPERUSER;

Then exit shell so changes take place.

Create a Database

damon@rapthalia:~/oni_sorceress$ psql --user oni_sorceress --password
Password: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "oni_sorceress" does not exist
damon@rapthalia:~/oni_sorceress$ psql --user oni_sorceress --password -d template1
Password: 
psql (15.2 (Debian 15.2-2))
Type "help" for help.

template1=> 

We can now use out freshly created user to log into a Postgres shell. This proves all is good. Just remember to specify a database (template1) since we haven’t created a database yet. You can log out now.

damon@rapthalia:~/oni_sorceress$ bin/rails db:create
Created database 'oni_sorceress_development'
Created database 'oni_sorceress_test'

We were able to create a database, so everything works now. And that’s all.

Old Notes

Postgres 13.1 in MacOS Big Sur homebrew:

psql -d template1

Older versions something like this (use TCP/IP instead of unix sockets):

sudo -u postgres psql -h localhost

in Linux (OpenSuse with Postgres 14):

sudo su -l postgres -s /bin/bash
psql

For Rails development the superuser is needed. Since db:drop removes also extensions on database which need a superuser to be created on a database.

Like this:

rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create extension "pgcrypto"
HINT:  Must be superuser to create this extension.