Home → Problems →

postgres peer authentication failed

• Edit

matt.lewellyn

Feb 25, 2019 @ 05:17 PM

I'm running into a problem getting the Postgres service set up on a Ubuntu1804 environment. With the attached appveyor.yml, I am attempting to have it create a Postgres database prior to tests running.

• createdb is failing: createdb: could not connect to database template1: FATAL: Peer authentication failed for user "postgres"

• If I ssh into the environment and run createdb in that shell, the command succeeds without error

• If, in ssh after creating the database, I attempt to psql into Postgres (psql -d test_db_setup -U postgres -W -h localhost) with the default username and password documented (postgres, Password12!), I get an auth error: psql: FATAL: password authentication failed for user "postgres"

• If I then reset the password in Postgres for the postgres user to what I expect it to be, I can then proceed normally

Questions:

• Why does createdb fail when run from appveyor.yml, but succeed when I ssh to the build instance?

• Why does the postgres user seem to have different credentials from what is documented?

Thanks for any assistance.

• appveyor.yml 935 Bytes
• appveyor_build_log.txt 1.14 KB

1. 1 Posted by Wasa Pleshakov on Feb 25, 2019 @ 10:38 PM

   

   You dont have to set variables PGUSER and PGPASSWORD since postgres configured to allow passwordless connections for current user appveyor.
   Remove both next lines and it should work:
   

     - sh: export PGUSER=postgres
     - sh: export PGPASSWORD=Password12!
   

   • Edit

2. 2 Posted by matt.lewellyn on Feb 26, 2019 @ 05:14 PM

   

   I see, that worked for the createdb issue. I would propose updating the documentation at https://www.appveyor.com/docs/services-databases/#postgresql to reflect this, as that indicates using these variables for the command line tools.

   However, password auth over TCP from the application I am testing still doesn't work. The following SSH session output shows that only if I reset the postgres user's password to the documented password, I can do TCP connection as expected.

   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ createdb brainstrain
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ psql -d brainstrain -U postgres -h localhost
   Password for user postgres: 
   psql: FATAL:  password authentication failed for user "postgres"
   FATAL:  password authentication failed for user "postgres"
   
   # update PG auth with "local  all   all   trust" as the first line, so we can reset the password
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ sudo vim /etc/postgresql/11/main/pg_hba.conf 
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ sudo service postgresql restart
   
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ psql -U postgres
   psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
   Type "help" for help.
   
   postgres=# ALTER USER postgres with password 'Password12!';
   ALTER ROLE
   postgres=# \q
   
   # update PG auth to remove the trust line
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ sudo vim /etc/postgresql/11/main/pg_hba.conf 
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ sudo service postgresql restart
   
   appveyor@av-5d87d07504854ee5be7000c36fc82eab:~$ psql -d brainstrain -U postgres -h localhost
   Password for user postgres: 
   psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
   SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
   Type "help" for help.
   
   brainstrain=#
   

   • Edit

3. 3 Posted by Wasa Pleshakov on Feb 26, 2019 @ 10:05 PM

   

   Oh, we don't set the password for postgres user at all on Ubuntu.
   The document you refer to was written for Windows and it reflects the state of postgres on Windows.
   We will fix both pg_hba.conf and password in next ubuntu image update.

   Meanwhile, you may automate update of phg_hba.conf:
   

   init:
     - sh: sudo sed -i -E -e 's/(local\s+all\s+postgres\s+)peer/\1trust/' /etc/postgresql/11/main/pg_hba.conf
   

   Init stage will execute before starting postgres service.
   And you may add next line to set password for postgres user:
   

   sudo -u postgres psql -c "ALTER USER postgres with password '${PGPASSWORD}';" postgres
   

   • Edit

4. 4 Posted by matt.lewellyn on Feb 27, 2019 @ 04:37 PM

   

   Thank you for the additional configuration - that resolved my problem, and the builds are now chugging along quite well.

   • Edit

5. matt.lewellyn closed this discussion on Feb 27, 2019 @ 04:40 PM.