I want to run a program as root and have it connect to postgres using socket authentication with a different user (called arnold in my examples), as prescribed by a connection URI.
In particular, in my testing environment (an nspawn container), I can run the following as root and successfully establish a connection to postgres,
(test-root) # psql postgres://[email protected]/arnold <it works>
but in my production environment I am failing to connect:
(prod-root) # psql postgres://[email protected]/arnold psql: FATAL: Peer authentication failed for user "arnold"
pg_hba.conf is identical between the two boxes, both are running postgres 10, and I can connect successfuly with
sudo -u arnold psql -d arnold in both cases.
My question then is what could be different between the two environments so that this is working for only one of them? Thanks in advance.