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'
Add Comment