Postgresql database create failure

whitemwco · February 13, 2024, 9:46pm

I have always be unable to execute rails db:create without getting:

** Invoke db:create (first_time) ** Invoke db:load_config (first_time) ** Invoke environment (first_time) ** Execute environment ** Execute db:load_config ** Execute db:create FATAL: Peer authentication failed for user “railstst” Couldn’t create ‘railstst_development’ database. Please check your configuration.

Changed ident to md5, as you can tell, I have tried about every combination.

“local” is for Unix domain socket connections only

local all all peer #local all all md5

IPv4 local connections:

#host all all 127.0.0.1/32 ident host all all 127.0.0.1/32 md5

IPv6 local connections:

#host all all ::1/128 ident host all all ::1/128 md5

Allow replication connections from localhost, by a user with the

replication privilege.

local replication all peer #host replication all 127.0.0.1/32 ident host replication all 127.0.0.1/32 md5 #host replication all ::1/128 ident host replication all ::1/128 md5

In the past, I have used trust because the project has been on my local server. I want some security now but can’t make it work. I used ruby pg gem connect.new which works with the pg_hba.conf above, but I have to have a database already created. I’m definitely missing something and can’t seem to find the answer anyware.

So as my last resort, I’m asking this group for help.

andatki · February 15, 2024, 5:47pm

Hi Mike. It sounds like you want to use the pg_hba.conf file locally versus not at all, and you do want to use a value with more security than “trust” (md5 or scram-sha-256). Are those both correct?

If it’s useful to you, as part of writing my book High Performance PostgreSQL for Rails, readers work through exercises setting up pg_hba.conf for replication between two instances. This is within a Rails app called “Rideshare” that’s public on GitHub and I’m happy to share the config files there hoping you can look through them and maybe identify your issue.

Feel free to explore the “postgresql” directory in that project, and the sample pg_hba.conf file.

github.com

andyatkinson/rideshare/blob/main/postgresql/pg_hba.sample.conf

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local         DATABASE  USER  METHOD  [OPTIONS]
# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#

This file has been truncated. show original

As some background on Rideshare: the application database has PostgreSQL users (roles with LOGIN privilege) “owner” and “admin” and they’re both able to connect. My local macOS user “andy” has a corresponding PostgreSQL user (I installed PostgreSQL via Postgres.app on macOS) and this user is configured in pg_hba.conf and can connect as well. This is not the exact file in use but a sample. In the book readers set up a replication user and generate a new version of the pg_hba.conf file, then put it into place on the file system (on a Docker PostgreSQL instance) and reload it.

As far as a general process to debug this that I’d follow, I’d probably roll back to no file at all, then a default file, then make customizations one at a time and reload the conf and confirm that I can connect. In my experience the connection error messages I get back when changing the pg_hba.conf aren’t very helpful for determining the problem, so it can be difficult to debug.

While not a solution directly, hopefully this info and those examples are useful to you as you debug your issue. Good luck.

whitemwco · February 15, 2024, 8:21pm

The light finally got turned on. As I was thinking, I was not performing an important step.

I needed to assign a password to a password to postgres role then change line: local all all peer to local all all md5

restart postgresql, that fixed all my problems.