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

Question

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

User · Answer

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   gt  gt   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

User · Answer

If you want to keep the default config but want md5 authentication with socket connection for one specific user db connection  add a  quot local quot  line BEFORE the  quot local all all quot  line    TYPE  DATABASE     USER         ADDRESS             METHOD     quot local quot  is for Unix domain socket connections only local   dbname       username                         md5     lt -- 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

User · Answer

the below command works for me   psql -d myDb -U username -W

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

After installing Postgresql I did the below steps    open the file pg hba conf for Ubuntu it will be in  etc postgresql 9 x main and change this line   local   all             postgres                                peer  to  local   all             postgres                                trust Restart the server    sudo service postgresql restart  Login into psql and set your password    psql -U postgres db gt  ALTER USER postgres with password  your-pass    Finally change the pg hba conf from  local   all             postgres                                trust  to  local   all             postgres                                md5   After restarting the postgresql server  you can access it with your own password  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

User · Answer

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

User · Answer

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

User · Answer

The problem is still your pg hba conf file   etc postgresql 9 1 main pg hba conf       This line   local   all             postgres                                peer   Should be   local   all             postgres                                md5     If you can t find this file  running locate pg hba conf should show you where the file is   After altering this file  don t forget to restart your PostgreSQL server  If you re on Linux  that would be sudo service postgresql restart   These are brief descriptions of both 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     Sample location for pg hba conf   etc postgresql 9 1 main pg hba conf

User · Answer

Use host localhost in connection   PGconn  conn   PQconnectdb       host localhost user postgres dbname postgres password 123

User · Answer

If you have an issue  you need to locate your pg hba conf  The command is   find   -name  pg hba conf  2 gt  dev null  and after that change the configuration file   Postgresql 9 3    Postgresql 9 4    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This has worked for me     sudo -u postgres psql

User · Answer

sudo psql --host localhost --dbname database-name --username postgres   This solved my issue

User · Answer

Go to this  etc postgresql 9 x main  and open pg hba conf file   In my case     gt   sudo nano  etc postgresql 9 3 main pg hba conf    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    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

User · Answer

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

[ruby-on-rails] Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails

Examples related to ruby-on-rails

Examples related to postgresql