Local authentication on postgres failing-CodePudding

I'm trying to configure a script that is run regularly by cron. I update the Postgres config to trust local connections but I'm getting a peer authentication error.

error

ubuntu@ubuntu:~$ sudo /data/postgresql-backup.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "rails_admin"

/etc/postgresql/14/main/pg_hba.conf

# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.

host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5
host    all             all             127.0.0.1/32            trust

# 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            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

/data/postgresql-backup.sh

#!/bin/bash

BACKUP_DIR="/data/psql-db-backup/"
FILE_NAME=$BACKUP_DIR`date  %d-%m-%Y-%I-%M-%S-%p`.sql
pg_dump -U rails_admin rails_production_db > $FILE_NAME

CodePudding user response：

You can only run that script as operating system user rails_admin, because only that user would be allowed to connect with peer authentication.

You can either relax peer to trust (if you trust all operating system users on the database server machine), or you can add a user name map to pg_ident.conf that allows the operating system user to connect as database user rails_admin.