PostgreSQL: FATAL - Peer authentication failed for user (PG::ConnectionBad)
postgresql, ruby-on-rails
Solution
"Peer authentication" means that it's using a unix socket and expecting the connecting unix user to have the same unix username as the postgresql username.
Since your local unix username is `funkdified` and you're trying to connect as user `goodsounds` over a unix domain socket (`local`) connection where your `pg_hba.conf` specifies `peer` authentication, Pg correctly rejects your connection attempt.
This is the default behaviour for many installs when using unix sockets.
You can:
- Connect via TCP/IP by specifying a hostname in your database connection settings;
- edit `pg_hba.conf` to use `md5` password authentication instead of `peer` authentication for unix sockets (`local` connection type) so Pg accepts password authentication; or
- Connect with a PostgreSQL username the same as your unix username and create the user in PostgreSQL if it doesn't exist yet.
See the docs for `pg_hba.conf` and the rest of the client authentication chapter of the documentation.
Note that changes to `pg_hba.conf` do not take effect immediately, you must restart or at least reload PostgreSQL to get it to reread `pg_hba.conf`.
Oh, also, if you have multiple PostgreSQL versions installed you might have a libpq from one version and a server from another. In this case make sure the location for the unix socket that libpq connects to by default is the same as the server's `unix_socket_directories` or override it with (e.g.) `host=/tmp` in your connection string.
Problem
I am working with PostgreSQL and I have a user with password matching the one specified in database.yml ``` postgres=# select * from pg_user ; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ------------+----------+-------------+----------+-----------+---------+----------+----------+----------- goodsounds | 16386 | t | t | t | t | ******** | | postgres | 10 | t | t | t | t | ******** | | (2 rows) ``` But when I try creating a database by running the command ``` rails db:create ``` I get the error FATAL: Peer authentication failed for user "goodsounds" Here is my pg_hba.conf: ``` # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust ``` Previously "trust" above was md5 but I changed to see if that would help. Here is my database.yml: ``` # PostgreSQL. Versions 8.2 and up are supported. # # Install the pg driver: # gem install pg # On Mac OS X with macports: # gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config # On Windows: # gem install pg # Choose the win32 build. # Install PostgreSQL and put its /bin directory on your path. # # Configure Using Gemfile # gem 'pg' # development: adapter: postgresql encoding: unicode database: goodsounds_development pool: 5 username: goodsounds password: test # Connect on a TCP socket. Omitted by default since the client uses a # domain socket that doesn't need configuration. Windows does not have # domain sockets, so uncomment these lines. host: localhost port: 5432 # Schema search path. The server defaults to $user,public #schema_search_path: myapp,sharedapp,public # Minimum log levels, in increasing order: # debug5, debug4, debug3, debug2, debug1, # log, notice, warning, error, fatal, and panic # The server defaults to notice. #min_messages: warning # Warning: The database defined as "test" will be erased and # re-generated from your development database when you run "rake". # Do not set this db to the same as development or production. test: adapter: postgresql encoding: unicode database: goodsounds_test pool: 5 username: goodsounds password: test production: adapter: postgresql encoding: unicode database: goodsounds_production pool: 5 username: goodsounds password: test ```