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
The connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql .
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.
Through trial and error I found that the password for Postgre SQL 10 for the username postgres is "admin". I kept typing in different password until I reached that password. I am using pgAdmin 4 to test out my SQL Statements, POSTGRE SQL 10 is the first server connection set up using localhost.
"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:
pg_hba.conf
to use md5
password authentication instead of peer
authentication for unix sockets (local
connection type) so Pg accepts password authentication; orSee 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With