[GENERAL] 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 jpfle...@ca.afilias.info -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] prepared statements and DBD::Pg
Daniel Verite wrote:
Tim Bunce wrote:
The example that started this thread was that this valid statement
worked:
prepare(CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);)
but this valid statement didn't:
prepare( INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);)
My argument is that both calls should return statement handles.
I think they do, and the original report is somehow flawed.
In my attempt to obfuscate the actual code, I actually included invalid
SQL , but I can assure you that the failure occurred as I described it,
though only with the version 2.11.8. Other versions 1.4 worked fine,
despite the explanation in the DBD::Pg docs which implied that they
might not.
Here's a test that demonstrates this with the SQL pasted from the
initial example.
print version is $DBD::Pg::VERSION\n;
$dbh-{pg_server_prepare} = 1;
my $prepare_sql =SQL;
CREATE TEMP TABLE foo( id int, user_id int,);
INSERT INTO foo(1, 1);
INSERT INTO foo(2, 2);
SQL
my $sth1=$dbh-prepare($prepare_sql);
print 1st statement handle=$sth1\n;
$prepare_sql=SQL;
INSERT INTO foo(1, 1);
INSERT INTO foo(2, 2);
SQL
my $sth2=$dbh-prepare($prepare_sql);
print 2nd statement handle=$sth2\n;
And here's the output I get:
version is 2.8.2
1st statement handle=DBI::st=HASH(0x8d40908)
2nd statement handle=DBI::st=HASH(0x8c73660)
If a server-side prepare is attempted and fails because it's a kind
of
statement that can't be server-side prepared then DBD::pg should
fallback to a client-side prepare.
Unfortunately with PG, an error in server-side prepare aborts the
current transaction, so that any subsequent command will fail until a
rollback is issued. Falling back to client-side prepare once in this
state would probably not help much.
Best regards,
--
JP Fletcher
Database Administrator
Afilias Canada
voice: 416.646.3304 ext. 4123
fax: 416.646.3305
mobile: 416.561.4763
jpfle...@ca.afilias.info
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] prepared statements and DBD::Pg
Hi, I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the first command in a prepared statement is 'CREATE TEMP TABLE'. For instance, this works: my $prepare_sql =SQL; CREATE TEMP TABLE foo( id int, user_id int,); INSERT INTO foo(1, 1); INSERT INTO foo(2, 2); SQL my $sth = $dbh-prepare($prepare_sql); This produces the error ERROR: cannot insert multiple commands into a prepared statement my $prepare_sql =SQL; INSERT INTO foo(1, 1); INSERT INTO foo(2, 2); SQL my $sth = $dbh-prepare($prepare_sql); Is this the expected behaviour? -- JP Fletcher Database Administrator Afilias Canada voice: 416.646.3304 ext. 4123 fax: 416.646.3305 mobile: 416.561.4763 jpfle...@ca.afilias.info -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and LDAP
FYI, I did see the message returned to my client, and did look in the server logs. Trouble was, the message in the log was no more helpful than the one returned to my client, with the exception of pointing me to the line number in pg_hba.conf. Gregory Stark wrote: Tom Lane t...@sss.pgh.pa.us writes: JP Fletcher jpfle...@ca.afilias.info writes: I've just compiled 8.3.6 with ldap support yet I get 'FATAL: missing or erroneous pg_hba.conf file' when I try to connect. Can't help you much when you don't show us the pg_hba.conf file ;-) However, a tip that might help is that there should be more information about the problem in the postmaster log. We intentionally don't send details about the conf file's contents to the client... Perhaps we should send a HINT to the client saying to consult the postmaster logs? -- JP Fletcher Database Administrator Afilias Canada voice: 416.646.3304 ext. 4123 fax: 416.646.3305 mobile: 416.561.4763 jpfle...@ca.afilias.info -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql and LDAP
Hi, I've just compiled 8.3.6 with ldap support yet I get 'FATAL: missing or erroneous pg_hba.conf file' when I try to connect. There weren't any errors during the compile, and ldd shows LDAP: postg...@schema-mgmt:/opt/pgdata/log/pgsql83$ ldd /opt/dbs/pgsql83/bin/postmaster | grep ldap libldap_r-2.4.so.2 = /usr/lib/libldap_r-2.4.so.2 (0xb7fd5000) This is on ubuntu linux, and the only configure option besides prefix was '--with-ldap' -- JP Fletcher Database Administrator Afilias Canada voice: 416.646.3304 ext. 4123 fax: 416.646.3305 mobile: 416.561.4763 jpfle...@ca.afilias.info -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
