pre-upgrade pg_dump problem : peer authentication failed for...

D Mitchell

I see a couple of people had similar issues.  But the fix didn't seem to apply to mine.   So I'll ask here:

 

I'm going to upgrade my Confluence from 7.4.4 to 7.13.0.    

In the past, I've always taken a dump of postgres.   However when I try it now, I get:

 

[root@atlassian pgsql-9.6]# pg_dump -U confluenceuser confluencedb -f confluence-08-26-21.sql
pg_dump: [archiver (db)] connection to database "confluencedb" failed: FATAL: Peer authentication failed for user "confluenceuser"

 

Ownership looks correct:

postgres=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

--------------+----------------+----------+-------------+-------------+-----------------------

confluencedb | confluenceuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

 

Now, when I upgraded Confluence about a year ago, I do recall having to upgrade Postgres as well and I also had to fix the DB collation.  But it has been running fine.   

 

Why would my pg_dump not work anymore?  

Or perhaps this step is no longer necessary

Nic Brough -Adaptavist-

Peer authentication failed errors on a Postgresql database usually mean the pg_hba.conf file is restricting access. 

This file is both excellently secure and a monumental pain in the <whatever> - it has good, secure, intentions and does what it says it should and does it well. 

But the default of "totally secure" coupled with "you have to be a security expert to understand how I work because we've not provided you any tools to manage it usefully" is dreadful, and by far the main reason people hate Postgresql.  It is my preferred (atlassian supported) database by a long-shot, but I still despise that horrendous design.

TLDR: you need to do something in your Postgres config to fix the access.