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.
- create user and permissions
# 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
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.
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.
rails aborted! ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to create extension "pgcrypto" HINT: Must be superuser to create this extension.