GaSiD.org.uk

Woah, neglected blog!

Tasked with implementing postgres database auth for a client, the task seemed fairly simple, add some users, edit pg_hba.conf, remove all of the trust config. Implement auth for various users from various hosts using a password… what could possibly go wrong?

After reconfiguring the apps to match, things were fine, with the exception of a Slony (Slony-I 2.0.6 in this instance ‘installed’ from source).

In this set, slon_start seems to takes the client auth details from slon_tools.conf when the cluster is originally configured. But editing it doesn’t then change them. So when slon is started, it tries to get its auth details form somewhere (there’s no slon.conf on these systems at all), so where was it?

I remember once looking into the postgres schema added by slony when you initialise a cluster. So I investigated further…

mydb=# set search_path TO _repl;
SET
mydb=# select * from sl_path;
pa_server | pa_client | pa_conninfo | pa_connretry
-----------+-----------+----------------------------------------------+--------------
1 | 2 | host=172.30.50.231 dbname=mydb user=slony port=5432 | 10
2 | 1 | host=172.30.50.232 dbname=mydb user=slony port=5432 | 10

Ok, so that’s where it’s stored, therefore is it really that easy to fix my problem?

mydb=# update sl_path set pa_conninfo=pa_conninfo || ' password=xxxx'
UPDATE 12
mydb=# select * from sl_path;
pa_server | pa_client | pa_conninfo | pa_connretry
-----------+-----------+-----------------------------------------------------------+--------------
1 | 2 | host=172.30.50.231 dbname=mydb user=slony port=5432 password=xxxx | 10
2 | 1 | host=172.30.50.232 dbname=mydb user=slony port=5432 password=xxxx | 10

Then I started slon on each of my nodes and… wow. slon now auths to my DB servers and replication syncs back up!