Thread: Can I track DB connections through a generic acct on the basis of linux idsid
Can I track DB connections through a generic acct on the basis of linux idsid
Hi:
PG v8.3.4 on linux. Server is at one corporate site, half the DB users are at that site, the other half are at a different site 2 time zones away. Users from both sites access the DB through the same, generic postgres user accout that has select/insert/update/delete only. And almost all of this is through perl scripts that use DBI as a gateway into the PG DB.
I want to be able to determine who ran what script and from what user site. All this info is at my fingertips during the execution of the perl script. I would like to ba able to log this info immediately before or after the DB connection, not wait for the transaction or query to complete. I would prefer not to have to insert this info to a log table because that would mean waiting for an additional network hit for simple queries from the remote site.
Is there a way to pass the info (idsid, app, site) to the perl/dbi connection string and get this info reported in the deamon runlog somehow?
How about "raise notice"? Could that be made to work somehow?
OK, having said all that, a completely different tact... There are 1468 potential users (idsids) out there that can query the DB. Does it make sense to create a DB user for each of those, giving them all the same access through a role or something, and then attacking the problem from that direction? Is 1468 impractical?
Thanks in advance!
Re: Can I track DB connections through a generic acct on the basis of linux idsid
Is there a way to pass the info (idsid, app, site) to the perl/dbi connection string and get this info reported in the deamon runlog somehow?
In newer versions of Pg you can use the application ID field. It's still likely going to be another network round-trip for the DBI driver to set this behind the scenes, though.
How about "raise notice"? Could that be made to work somehow?
Sure, but again you'll still have to send the data to the server, so you might as well just INSERT it into a log table.
It sounds like you want to send some custom info as part of the initial connection. Honestly, I wouldn't worry too much about this. If you examine the connection setup using a protocol analyser like wireshark, you're likely to see a bit of client/server chat between the server and DBI driver already. Adding to this slightly shouldn't matter very much. Try it and see how much impact it actually has.
OK, having said all that, a completely different tact... There are 1468 potential users (idsids) out there that can query the DB. Does it make sense to create a DB user for each of those, giving them all the same access through a role or something, and then attacking the problem from that direction? Is 1468 impractical?
To me, it's more than you'd have to maintain them. Having server-side user identity is great for when you're doing trigger-based auditing and the like, but it can be a pain to keep up to date with other user info elsewhere. Binding Pg to an LDAP directory or the like can help, but isn't completely transparent and can be more hassle than it saves.
--
Craig Ringer