I am trying to setup HA solution for PostgreSQL. For that, I am running pgpool service.

In that service, it calls a script whenever my primary node fails by calling it like this:

/etc/pgpool-II-94/failover.sh %d %H'

where %d is my node id and %H is the hostname for the new server. Think of them simply as two parameters.

My Failover script is below:

echo "Failed node: $failed_node"
set -x
/usr/bin/ssh -T -l postgres $new_master "/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

The problem I am facing here is that when this script gets executed, it gets executed as root and hence I cannot authenticate it for postgres user for other server.

How to run my pgpool service as postgres user itself so that my authentication get passed?

Right now I am using:

systemctl start pgpool-II-94

from postgres to postgres in all the servers I have setup passwordless ssh so that would not be an issue.

For example: when I run through postgres user

-bash-4.2$ /usr/bin/ssh -T -l postgres lbdevsecondary 'echo $HOSTNAME'

But when I run through root user, it asks for password

/usr/bin/ssh -T -l postgres lbdevsecondary 'echo $HOSTNAME'
postgres@lbdevsecondary's password:

Or please tell me a way to rewrite the above statement in the way that I first logged in as postgres user and then run the commands from my scripts.

I am using CentOS 7 and fairly new to Linux.

  • 1
    Add root's ssh public key to postgreql's .ssh/authorized_keys
    – Jasen
    Commented Dec 25, 2016 at 11:21
  • @Jasen watch the auditor's head explode when you do that. While I want to see that happen, the outcome is an audit finding that I'd have to fix. Thus, better not to run PgPool as root in the first place.
    – RonJohn
    Commented Jan 19, 2023 at 20:51

1 Answer 1

  1. Su

You can change the user in your script at any time:

su - postres
  1. Ssh-copy-id

Add ssh keys for root to the remote server:

ssh-copy-id [email protected]

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .