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
.