Howto Set Up Postgres For Rails Development
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.
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
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
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
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
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.
/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'