pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user "postgres"

32,816

If your PHP Apache web site works with peer, keep this, and let's deal with it.

1. First Method :

As authentication is peer based, you have to specify the hostname (usually localhost) using -h option :

ssh server "pg_dump -h localhost -U postgres | gzip -c" >/backup/sqlbackup.sql.gz

The main issue here is that you will be prompted for the postgres user password. It is a problem for backups automation .

You can refer to this to avoid to be prompted for the password. But i will describe :

  1. Create an hidden file named .pgpass in $HOME
  2. Perform a chmod 600 .pgpass
  3. Edit file .pgpass and add this line : localhost:5432:postgres:postgres:password

2. Second Method :

You could add a specific role for backups and trust it.

Create the backup role in Postgres :

CREATE ROLE backup WITH LOGIN SUPERUSER PASSWORD 'password'

Edit file pg_hba.conf, add role backup and trust it :

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

Restart postgresql

Then you should be able to run backup with the following :

ssh server "pg_dump -U backup postgres | gzip -c" >/backup/sqlbackup.sql.gz
Share:
32,816

Related videos on Youtube

Drace
Author by

Drace

Updated on September 18, 2022

Comments

  • Drace
    Drace about 1 year

    I'm trying to backup my Postgres DB to another server but I keep getting denied access.

    my pg_hba.conf file looks like this:

    # DO NOT DISABLE!
    # If you change this first entry you will need to make sure that the
    # database superuser can access the database using some other method.
    # Noninteractive access to all databases is required during automatic
    # maintenance (custom daily cronjobs, replication, and similar tasks).
    #
    # 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
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5
    

    If I change the postgres setting from PEER to TRUST, i can backup the code but the web site can no longer access the database (something about SSL error, which I know isn't the issue).

    When I leave it as is, PEER, the server works - php can access the db - but I get error while trying to run the backup:

    root> pg_dump -U postgres tablename > test.sql

    .pg_dump: [archiver (db)] connection to database "tablename" failed: FATAL: Peer authentication failed for user "postgres".

    PHP Apache Web site uses appropriate username and password.

    Backup script looks like this: pg_dump -U postgres tablename > test.sql

    how can I satisfy both the web site and the backup script so I can run both?

    Entire script attempted is this: ssh SERVER "pg_dump -U postgres tablename| gzip -c" > /backup/sqlbackup.sql.gz

    taken from Server Fault: ssh remote command-fu

  • s.k
    s.k over 3 years
    If the -h localhost is given, pg_dump will prompt for a password for the user postgres. But if this user relies on a peer connection, it actually doesn't have a password. How would you bypass this?