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.

[D Mitchell]

Thank you for that.  However, I'm a little concerned that my atlassian host doesn't appear to use a pg_hba.conf

[routeone@atlassian pgsql-9.6]$ locate pg_hba.conf
/usr/pgsql-9.3/share/pg_hba.conf.sample
/usr/pgsql-9.6/share/pg_hba.conf.sample

Perhaps there is another control in place.

[Nic Brough -Adaptavist-]

I've only ever seen postgres security done with that file, but it being missing would explain it - the lines in that file tell postgres to let people connect to it.  Without rules in that file letting you in, postgres will only let in the local admin user on that machine

[D Mitchell]

makes sense.  thanks Nic!