Thursday, August 8, 2013

Another reason to set pg_hba.conf restrictively

pg_hba.conf can be a pain in the neck.  You're expecting some burst traffic, you deploy 8 new virtual appservers, fire them up, and ... they can't connect to Postgres.  Gah!

That kind of experience makes it tempting to set everything to:

   host  all  all   md5

But I just encountered another case this month why you shouldn't.  While everyone thinks about malefactors as the main reason to have tight restrictions on which hosts can connect via pg_hba.conf, one should remember that accidents are far more common than malice.  As was the case this time.

I set up a benchmark test for a client which involved replaying 8 hours of activity logs against a test copy of their database.  What they didn't tell me (because they didn't know due to poor internal documentation) was that their database used DBlink to write data to another database several times a minute.

Thank goodness they had exercised good pg_hba.conf practices!  All I got was a bunch of DBlink errors, instead of scribbling all over production data.   Whew!

1 comment:

  1. > pg_hba.conf can be a pain in the neck. (...) That kind of experience makes it tempting to set everything to....

    Tell me about it. My experience with pg_hba.conf, as a developer and not DBA, was that it was a huge mystery to me for years, with seemingly the same line having different effects at different times, and it remained so right until I took a course led by Bruce Momjian that taught me that it's processed line-by-line top to bottom, and the first line that matches the criteria decides the access method. Perhaps obvious to someone admin-oriented, but that bit of information was the single thing that most helped me get a mental grip on how that file works, and it'd saved me a lot of kludging if it was part of the header comment in it and not something I'd have to look up the docs to find out -- or have Bruce Momjian tell me.