Howto Set Up Postgres For Rails Development

OpenSuse:

zypper install postgresql postgresql-contrib postgresql-devel postgresql-server

The first step and always fucking frustrating is to get to postgres console and then create database user:

Postgres 13.1 in MacOS Big Sur homebrew:

psql -d template1

Older versions something like this:

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.

I tried to do it with regular user with privileges, but didn’t succeed and was such a pain.

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

And you are done, just run:

bin/rails db:create db:migrate db:seed

Additional Problems in Default Configuration on Linux

By default only postgres user can enter the psql shell

su - postgres
psql

Now you can create users and run other commands.

But authentication won’t still wouldn’t work.

psql: error: FATAL:  Peer authentication failed for user "oni_sorceress"
psql: error: FATAL:  Ident authentication failed for user "oni_sorceress"

ident authentication is naive

ident

The ident authentication method works by obtaining the client’s operating system user name from an ident server and using it as the allowed database user name (with an optional user name mapping). This is only supported on TCP/IP connections.

peer authentication is naive too, but using unix sockets

peer

The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

and we need password authentication

password

There are several password-based authentication methods. These methods operate similarly but differ in how the users’ passwords are stored on the server and how the password provided by a client is sent across the connection.

Edit /var/lib/pgsql/data/pg_hba.conf (openSuse) or /etc/postgresql/$version$/main/pg_hba.conf (Debian) and add this line BEFORE local .. peer like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     password
# "local" is for Unix domain socket connections only
local   all             all                                     peer

Add password entries for host lines too if you want to use TCP/IP connections.

Now you can login:

psql --user oni_sorceress --password template1
Password: 
psql (13.3)
Type "help" for help.

Now we are talking…

bin/rails db:create
Database 'db/development.sqlite3' already exists
Created database 'oni_sorceress_development'
Database 'db/test.sqlite3' already exists
Created database 'oni_sorceress_test'