Re: Can I track DB connections through a generic acct on the basis of linux idsid

[Craig Ringer <ringerc@xxxxxxxxxxxxx>]

On 01/11/11 09:23, Gauthier, Dave wrote:

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