CODEWITHSUNDEEP
ruby-on-railspostgresql
orderof1
orderof1

Reputation: 12761

Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails

I'm getting the error:

FATAL: Peer authentication failed for user "postgres"

when I try to make postgres work with Rails.

Here's my pg_hba.conf, my database.yml, and a dump of the full trace.

I changed authentication to md5 in pg_hba and tried different things, but none seem to work.

I also tried creating a new user and database as per Rails 3.2, FATAL: Peer authentication failed for user (PG::Error)

But they don't show up on pgadmin or even when I run sudo -u postgres psql -l.

Any idea where I'm going wrong?

Upvotes: 1244

Views: 1260922

Answers (29)

Erwin Zoer
Erwin Zoer

Reputation: 31

This is an interesting question to read but something I learned today and was unable to find here is that peer authentication expects that:

A. the user is logged into the operating system as the database user B. any interactions with the database occur from within that OS user context

For example using Ansible, this requires tasks to be executed with:

become_user: postgres

or whatever user you're using to access your database. This will ensure your tasks logs in as the 'become_user' and then connects to the database server.

Looking at some of the answers, changing the authentication method to md5 allows normal password authentication for the database user and thus does not require database interactions to occur from within the context of a user logged into the OS. This will then allow you to specify host, username and password to login to PostgreSQL.

Changing the authentication method to trust seems very dangerous. Sure it will work but you will need to be absolutely sure that anyone meeting the connection criteria is trustworthy.

Upvotes: 0

Pranav Premarajan
Pranav Premarajan

Reputation: 481

This error may occur when you do not provide the host. The following scenario resembles it.

user@homepc:~$ psql -d test_db -U test_user
psql: error: FATAL:  Peer authentication failed for user "test_user"
user@homepc:~$ psql -h localhost -d test_db -U test_user
Password for user test_user:

Providing host resolved my issue in psql command line. Try providing host in connection configuration for postgress in rails.

Upvotes: 28

Rozy Mahsun
Rozy Mahsun

Reputation: 363

you dont need to edit anything, just use this command:

$psql -U postgres -h 127.0.0.1 -d [your_db]

cheers

Upvotes: 1

Shubham Jain
Shubham Jain

Reputation: 1016

This error was misleading for me. I am running pg in docker with mounted ports. Adding -h <HostIp> -p <HostPort> to the psql command resolved the issue.

psql -U <User> -d <DB> -W -h <HostIp> -p <HostPort>

Upvotes: 3

Marcelo De Polli
Marcelo De Polli

Reputation: 29301

The problem is still your pg_hba.conf file*.

This line:

local   all             postgres                                peer

Should be:

local   all             postgres                                md5

After altering this file, don't forget to restart your PostgreSQL server. If you're on Linux, that would be sudo systemctl restart postgresql (on older systems: sudo service postgresql restart).

Locating hba.conf

Note that the location of this file isn't very consistent.

You can use locate pg_hba.conf or ask PostgreSQL SHOW hba_file; to discover the file location.

Usual locations are /etc/postgresql/[version]/main/pg_hba.conf and /var/lib/pgsql/data/pg_hba.conf.

These are brief descriptions of the peer vs md5 options according to the official PostgreSQL docs on authentication methods.

Peer authentication

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.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

Upvotes: 1584

Singh
Singh

Reputation: 605

In my case, I was not even able to edit or see the content of pg_hba.conf file.

What worked was:

/etc/postgresql/14/main$ sudo vi pg_hba.conf

Vi editor with sudo permission.

Upvotes: 2

Arivarasan L
Arivarasan L

Reputation: 9988

After installing Postgresql I did the below steps.

  1. Open the file pg_hba.conf. For Ubuntu, use for example /etc/postgresql/13/main$ sudo nano pg_hba.conf and change this line at the bottom of the file, it should be the first line of the settings:

    local   all             postgres                                peer

    to

    local   all             postgres                                trust

    Side note: If you want to be able to connect with other users as well, you also need to change:

    local   all             all                                peer

    to

    local   all             all                                md5

    If you used nano editor, exit with double Escape, x, y, Enter to save the config file.

  2. Restart the server

     $ sudo service postgresql restart

    Output: * Restarting PostgreSQL 13 database server

  3. Login into psql and set your password

     $ psql -U postgres
 db> ALTER USER postgres with password 'your-pass';

    Output: ALTER ROLE

    Side note: If you have other users, they will need a password as well:

     db> ALTER USER my_user with password 'your-pass';

    Then enter:

     exit

  4. Finally change the pg_hba.conf from

    local   all             postgres                                trust

    to

    local   all             postgres                                md5

  5. Restart the server again

     $ sudo service postgresql restart

    Output: * Restarting PostgreSQL 13 database server

  6. Login at psql with postgres user

    After restarting the postgresql server, the postgres user accepts the password that you chose:

     psql -U postgres

    Output:
    Password for user postgres:

    psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))

    Type "help" for help.

    And you are in psql:

    postgres=#

    Side note: Same now works for my_user if you added the user and password:

     psql -d YOUR_DB_NAME -U my_user

    Which will ask you for the new password of my_user.

Authentication methods details:

trust - anyone who can connect to the server is authorized to access the database

peer - use client's operating system user name as database user name to access it.

md5 - password-base authentication

for further reference check here

Upvotes: 701

Ravi Kumar Gupta
Ravi Kumar Gupta

Reputation: 1798

On CentOS 7, PG 10, the file path is

/var/lib/pgsql/10/data/pg_hba.conf

Upvotes: 2

alramdein
alramdein

Reputation: 901

Most of the solutions are suggest editing the pg_hba.conf.

For you who don't want to edit the configuration file, you basically just need to log in to the postgres user. If you are using/in Linux server, use this command

sudo -i -u postgres

It'll create user postgres and then log in to it. Now try your psql command again.

You can also add postgres user a password with the command: (you should be in root user)

passwd postgres

This is works because according to this PostgreSQL's Documentation,

Peer Authentication

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.

Upvotes: 13

Saranga kapilarathna
Saranga kapilarathna

Reputation: 654

Please follow the below steps

1). First, navigate to the /etc/postgresql/{your pg version}/main directory.

My version is 10 Then:

cd /etc/postgresql/10/main

2). Here resides the pg_hba.conf file needs to do some changes here you may need sudo access for this.

sudo nano pg_hba.conf

3). Scroll down the file till you find this –

# Database administrative login by Unix domain socket
local   all             postgres                                peer

4). Here change the peer to md5 as follows.

# Database administrative login by Unix domain socket
local   all             all                                md5

  • peer means it will trust the authenticity of UNIX user hence does not

  • prompt for the password. md5 means it will always ask for a password, and validate it after hashing with MD5.

5).Now save the file and restart the Postgres server.

sudo service postgresql restart

Now it should be ok.

Upvotes: 6

&#197;smund
&#197;smund

Reputation: 1428

If you want to keep the default config but want md5 authentication with socket connection for one specific user/db connection, add a "local" line BEFORE the "local all/all" line:

# TYPE  DATABASE     USER         ADDRESS             METHOD

# "local" is for Unix domain socket connections only
local   dbname       username                         md5  # <-- this line
local   all          all                              peer
# IPv4 local connections:
host    all          all          127.0.0.1/32        ident
# IPv6 local connections:
host    all          all          ::1/128             ident

Upvotes: 12

Rokas Lakštauskas
Rokas Lakštauskas

Reputation: 1238

Simplest solution without changing configs. (ubuntu) Change user, then connect to database cli.

sudo -i -u postgres

psql

taken from https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

Upvotes: 26

tayfun Kılı&#231;
tayfun Kılı&#231;

Reputation: 2843

pg_config is for compliation information, to help extensions and client programs compile and link against PostgreSQL. It knows nothing about the active PostgreSQL instance(s) on the machine, only the binaries.

pg_hba.conf can appear in many other places depending on how Pg was installed. The standard location is pg_hba.conf within the data_directory of the database (which could be in /home, /var/lib/pgsql, /var/lib/postgresql/[version]/, /opt/postgres/, etc etc etc) but users and packagers can put it wherever they like. Unfortunately.

The only valid ways find pg_hba.conf is to ask a running PostgreSQL instance where it's pg_hba.conf is, or ask the sysadmin where it is. You can't even rely on asking where the datadir is and parsing postgresql.conf because an init script might passed a param like -c hba_file=/some/other/path when starting Pg.

What you want to do is ask PostgreSQL:

SHOW hba_file;

This command must be run on a superuser session, so for shell scripting you might write something like:

psql -t -P format=unaligned -c 'show hba_file';

and set the environment variables PGUSER, PGDATABASE, etc to ensure that the connection is right.

Yes, this is somewhat of a chicken-and-egg problem, in that if the user can't connect (say, after screwing up editing pg_hba.conf) you can't find pg_hba.conf in order to fix it.

Another option is to look at the ps command's output and see if the postmaster data directory argument -D is visible there, e.g.

ps aux  | grep 'postgres *-D'

since pg_hba.conf will be inside the data directory (unless you're on Debian/Ubuntu or some derivative and using their packages).

If you're targeting specifically Ubuntu systems with PostgreSQL installed from Debian/Ubuntu packages it gets a little easier. You don't have to deal with hand-compiled-from-source Pg that someone's initdb'd a datadir for in their home dir, or an EnterpriseDB Pg install in /opt, etc. You can ask pg_wrapper, the Debian/Ubuntu multi-version Pg manager, where PostgreSQL is using the pg_lsclusters command from pg_wrapper.

If you can't connect (Pg isn't running, or you need to edit pg_hba.conf to connect) you'll have to search the system for pg_hba.conf files. On Mac and Linux something like sudo find / -type f -name pg_hba.conf will do. Then check the PG_VERSION file in the same directory to make sure it's the right PostgreSQL version if you have more than one. (If pg_hba.conf is in /etc/, ignore this, it's the parent directory name instead). If you have more than one data directory for the same PostgreSQL version you'll have to look at database size, check the command line of the running postgres from ps to see if it's data directory -D argument matches where you're editing, etc. https://askubuntu.com/questions/256534/how-do-i-find-the-path-to-pg-hba-conf-from-the-shell/256711

Upvotes: 5

Luca Marletta
Luca Marletta

Reputation: 457

I was moving data directory on a cloned server and having troubles to login as postgres. Resetting postgres password like this worked for me.

root# su postgres
postgres$ psql -U postgres
psql (9.3.6) 
Type "help" for help. 
postgres=#\password 
Enter new password: 
Enter it again: 
postgres=#

Upvotes: 15

Gihan Gamage
Gihan Gamage

Reputation: 3374

sudo psql --host=localhost --dbname=database-name --username=postgres

This solved my issue

Upvotes: 42

Taimoor Changaiz
Taimoor Changaiz

Reputation: 10684

  1. Go to this /etc/postgresql/9.x/main/ and open pg_hba.conf file

In my case:

$>  sudo nano /etc/postgresql/9.3/main/pg_hba.conf
  1. Replace peer with md5

So this will be changed to:

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            md5

This:

Database administrative login by Unix domain socket local all postgres md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

  1. Then restart pg server:

    $> sudo service postgresql restart

Below is list of METHODS used to connect with postgres:

# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.

Note: If you have not create you postgres user yet. Create that and now you can access postgres server using that user credentials.

TIP: If it does not work after postgres restart then close terminal and open again.

Upvotes: 23

d.danailov
d.danailov

Reputation: 9810

If you have an issue, you need to locate your pg_hba.conf. The command is:

find / -name 'pg_hba.conf' 2>/dev/null

and after that change the configuration file:

Postgresql 9.3

Postgresql 9.3

Postgresql 9.4

Postgresql 9.3

The next step is: Restarting your db instance:

service postgresql-9.3 restart

If you have any problems, you need to set password again:

ALTER USER db_user with password 'db_password';

Upvotes: 25

Mani
Mani

Reputation: 2563

If you are facing this issue with rails and you know that you already have created that user-name with password along with correct rights then you just need to put following at the end of your database.yml file.

host: localhost

overall file will look like below

development:
  adapter: postgresql
  encoding: unicode
  database: myapp_development
  pool: 5
  username: root
  password: admin
  host: localhost

You do not need to touch you pg_hba.conf file at all. Happy coding

Upvotes: 3

Marshall
Marshall

Reputation: 88

If you are trying to locate this file in Cloud 9, you can do

sudo vim /var/lib/pgsql9/data/pg_hba.conf

Press I to edit/insert, press ESC 3 times and type :wq will save the file and quit

Upvotes: 2

Epsilon47
Epsilon47

Reputation: 838

My issue was that I did not type any server. I thought it is a default because of placeholder but when I typed localhost it did work.

Upvotes: 2

iamuser2
iamuser2

Reputation: 1121

This has worked for me !!

sudo -u postgres psql

Upvotes: 111

Bill
Bill

Reputation: 89

Many of the other answers pertain to settings in the various config files, and the ones pertaining to the pg_hba.conf do apply and are 100% correct. However, make sure you are modifying the correct config files.

As others have mentioned the config file locations can be overridden with various settings inside the main config file, as well as supplying a path to the main config file on the command line with the -D option.

You can use the following command while in a psql session to show where your config files are being read (assuming you can launch psql). This is just a troubleshooting step that can help some people:

select * from pg_settings where setting~'pgsql';

You should also make sure that the home directory for your postgres user is where you expect it to be. I say this because it is quite easy to overlook this due to the fact that your prompt will display '~' instead of the actual path of your home directory, making it not so obvious. Many installations default the postgres user home directory to /var/lib/pgsql.

If it is not set to what it is supposed to be, stop the postgresql service and use the following command while logged in as root. Also make sure the postgres user is not logged into another session:

usermod -d /path/pgsql postgres

Finally make sure your PGDATA variable is set correctly by typing echo $PGDATA, which should output something similar to:

/path/pgsql/data

If it is not set, or shows something different from what you expect it to be, examine your startup or RC files such as .profile or .bash.rc - this will vary greatly depending on your OS and your shell. Once you have determined the correct startup script for your machine, you can insert the following:

export PGDATA=/path/pgsql/data

For my system, I placed this in /etc/profile.d/profile.local.sh so it was accessible for all users.

You should now be able to init the database as usual and all your psql path settings should be correct!

Upvotes: 3

Manisha Vasani
Manisha Vasani

Reputation: 271

Use host=localhost in connection.

PGconn *conn = PQconnectdb(
    "host=localhost user=postgres dbname=postgres password=123"
);

Upvotes: 6

Supun Muthutantrige
Supun Muthutantrige

Reputation: 269

Changing METHOD peer to trust in pg_hba.conf (/etc/postgresql/9.1/main/pg_hba.conf | line 85) solves the issue. Adding md5 asks for a password, hence if there is a requirement to avoid using passwords, use trust instead of md5.

Upvotes: 6

StylusEater
StylusEater

Reputation: 3949

If you connect over localhost (127.0.0.1) you shouldn't experience that particular issue. I wouldn't muck much with the pg_hba.conf but instead I would adjust your connection string:

psql -U someuser -h 127.0.0.1 database

where someuser is your user you're connecting as and database is the database your user has permission to connect to.

Here is what I do on Debian to setup postgres:

http://www.postgresql.org/download/linux/debian/  (Wheezy 7.x)

as root …

    root@www0:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" >> /etc/apt/sources.list

    root@www0:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

    root@www0:~# apt-get update

    root@www0:~# apt-get install postgresql-9.4        

    root@www0:~# su - postgres 

    postgres@www0:~$ createuser --interactive -P someuser
    Enter password for new role:
    Enter it again:
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n

    postgres@www0:~$ createdb -O someuser database

    postgres@www0:~$ psql -U someuser -h 127.0.0.1 database

Enjoy!

Upvotes: 358

Alexandr Zaichenko
Alexandr Zaichenko

Reputation: 51

You need just set METHOD to trust.

#TYPE  DATABASE        USER            ADDRESS                 METHOD
local    all             all                                     trust

And reload postgres server. 

# service postgresql-9.5 reload

Changes in pg_hba.conf dont require RESTART postgres server. just RELOAD.

Upvotes: 5

sibosop
sibosop

Reputation: 91

The edits above worked for me, after I figured out that I needed to restart the postgres server after making them. For ubuntu:

sudo /etc/init.d/postgresql restart

Upvotes: 7

Mazen Ora
Mazen Ora

Reputation: 71

the below command works for me:

psql -d myDb -U username -W

Upvotes: 5

nirvanastack
nirvanastack

Reputation: 463

I had the same problem.

The solution from depa is absolutely correct.

Just make sure that u have a user configured to use PostgreSQL.

Check the file:

$ ls /etc/postgresql/9.1/main/pg_hba.conf -l

The permission of this file should be given to the user you have registered your psql with.

Further. If you are good till now..

Update as per @depa's instructions.

i.e.

$ sudo nano /etc/postgresql/9.1/main/pg_hba.conf

and then make changes.

Upvotes: 11

Related Questions