Thread: auditing pg_hba.conf
Hi, We manage hundreds of clusters and a handful of distinct pg_hba.conf files across several sites. We are mostly satisfied with our automated method of management, but on occasion, someone will hand edit a pg_hba.conf file, and some application will get locked out. This a bad. We'd like to be able to do a few things related to auditing pg_hba.conf: 1. Store a copy of pg_hba.conf on server start or reload 2. Have an audit trail that shows when particular rules were loaded. 3. Compare the contents of pg_hba.conf to the rules that are actually loaded. 4. Alert the DBA when the rules loaded differ from the file that was previously loaded. We can accomplish #1 and #2 by having a shell command copy the file, or by storing rules in a db table. I'm not sure that #3 and #4 are possible until we accomplish #1. I'm not aware of any function or catalog table/view that stores pg_hba rules. I'm curious to know if anyone has any suggestions, or has solved a similar problem. Best Regards, JP -- JP Fletcher Database Administrator Afilias Canada voice: 416.646.3304 ext. 4123 fax: 416.646.3305 mobile: 416.561.4763 jpfletch@ca.afilias.info
On Tue, Nov 3, 2009 at 3:41 PM, JP Fletcher <jpfletch@ca.afilias.info> wrote: > Hi, > > We manage hundreds of clusters and a handful of distinct pg_hba.conf files > across several sites. We are mostly satisfied with our automated method of > management, but on occasion, someone will hand edit a pg_hba.conf file, and > some application will get locked out. This a bad. We'd like to be able to > do a few things related to auditing pg_hba.conf: > > 1. Store a copy of pg_hba.conf on server start or reload > > 2. Have an audit trail that shows when particular rules were loaded. > > 3. Compare the contents of pg_hba.conf to the rules that are actually > loaded. > > 4. Alert the DBA when the rules loaded differ from the file that was > previously loaded. > > We can accomplish #1 and #2 by having a shell command copy the file, or by > storing rules in a db table. I'm not sure that #3 and #4 are possible until > we accomplish #1. I'm not aware of any function or catalog table/view that > stores pg_hba rules. I'm curious to know if anyone has any suggestions, or > has solved a similar problem. I'd probably put the various pg_hba.conf files under svn control, and then you can delete and check out a new one with a script that runs every so often / on server restart etc. Then when you need to change the pg_hba.conf for all the machines with x version of pg_hba.conf you update the master and run a script on the remote machines that checks out the new pg_hba.conf and reloads the db.
Hi Why don't use etckeeper ? Regards, http://joey.kitenet.net/code/etckeeper/ Le 03/11/09 23:41, JP Fletcher a écrit : > Hi, > > We manage hundreds of clusters and a handful of distinct pg_hba.conf > files across several sites. We are mostly satisfied with our > automated method of management, but on occasion, someone will hand > edit a pg_hba.conf file, and some application will get locked out. > This a bad. We'd like to be able to do a few things related to > auditing pg_hba.conf: > > 1. Store a copy of pg_hba.conf on server start or reload > > 2. Have an audit trail that shows when particular rules were loaded. > > 3. Compare the contents of pg_hba.conf to the rules that are actually > loaded. > > 4. Alert the DBA when the rules loaded differ from the file that was > previously loaded. > > We can accomplish #1 and #2 by having a shell command copy the file, > or by storing rules in a db table. I'm not sure that #3 and #4 are > possible until we accomplish #1. I'm not aware of any function or > catalog table/view that stores pg_hba rules. I'm curious to know if > anyone has any suggestions, or has solved a similar problem. > > Best Regards, > > JP >