Thread: How To Log User Name + Database?
Can anyone tell me how to log the db user name and db name to the server log? Is this possible at present? Couldn't find anything about it in the docs or deja... Regards, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes:
> Can anyone tell me how to log the db user name and db name to the server
> log? Is this possible at present?
Offhand I don't think that happens at the moment, but it does seem like
it'd be a good idea to have a backend do an elog(DEBUG) at startup that
mentions its database name and user name. Assuming you have logging of
timestamps/PIDs turned on, that would be enough to correlate later log
messages with a user name.
Comments anyone?
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [001206 11:25]: > Ed Loehr <eloehr@austin.rr.com> writes: > > Can anyone tell me how to log the db user name and db name to the server > > log? Is this possible at present? > > Offhand I don't think that happens at the moment, but it does seem like > it'd be a good idea to have a backend do an elog(DEBUG) at startup that > mentions its database name and user name. Assuming you have logging of > timestamps/PIDs turned on, that would be enough to correlate later log > messages with a user name. in 7.1, I use: log_connections = on fsync = off syslog_facility = LOCAL5 syslog_ident = pg-test syslog=2 show_source_port = on Which produces: Dec 4 04:57:09 lerami pg-test[27458]: [1] DEBUG: connection: host=[local] user=ler database=regression Dec 4 04:57:11 lerami pg-test[27462]: [1] DEBUG: connection: host=[local] user=ler database=regression in the syslog. So, I think it's there already. > > Comments anyone? > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Tom Lane wrote: > Offhand I don't think that happens at the moment, but it does seem like > it'd be a good idea to have a backend do an elog(DEBUG) at startup that > mentions its database name and user name. Assuming you have logging of > timestamps/PIDs turned on, that would be enough to correlate later log > messages with a user name. > Comments anyone? Yes. Good idea. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
> Offhand I don't think that happens at the moment, but it does seem like > it'd be a good idea to have a backend do an elog(DEBUG) at startup that > mentions its database name and user name. Assuming you have logging of > timestamps/PIDs turned on, that would be enough to correlate later log > messages with a user name. Since each connection generates a backend process, I think the timestamp/PID would be enough to relate log messages to an initial one saying: PID x starting (database y, user z). Maybe you could even include the source IP address, or whether it's an tcp or a unix domain socket connection. Or maybe this is useless, dunno.
Larry Rosenman <ler@lerctr.org> writes:
>>>> Can anyone tell me how to log the db user name and db name to the server
>>>> log? Is this possible at present?
> So, I think it's there already.
You're right ... I had just found that in the sources myself.
log_connections is the controlling option.
regards, tom lane
Tom Lane wrote: > > Ed Loehr <eloehr@austin.rr.com> writes: > > Can anyone tell me how to log the db user name and db name to the server > > log? Is this possible at present? > > Offhand I don't think that happens at the moment, but it does seem like > it'd be a good idea to have a backend do an elog(DEBUG) at startup that > mentions its database name and user name. Assuming you have logging of > timestamps/PIDs turned on, that would be enough to correlate later log > messages with a user name. > > Comments anyone? That'd be an improvement. Considering the case where you have many users and many dbs, with log lines co-mingled. Would it be better to (optionally) allow the db name and user name to be logged on each line? That way, you wouldn't have to do the visual correlation with a line possibly tens or hundreds of lines earlier in the log. It might also simplify log processing scripts a bit. Regards, Ed Loehr
Ed Loehr <eloehr@austin.rr.com> writes:
> Would it be better to
> (optionally) allow the db name and user name to be logged on each
> line?
That would bulk up the log considerably, and also create formatting
problems (you couldn't really make these items be fixed-width columns).
regards, tom lane
Tom Lane wrote: > > Ed Loehr <eloehr@austin.rr.com> writes: > > Would it be better to > > (optionally) allow the db name and user name to be logged on each > > line? > > That would bulk up the log considerably, and also create formatting > problems (you couldn't really make these items be fixed-width columns). These points, at least the first, suggest to me it should be an optional feature. But regarding bulkier logs, FWIW, I would gladly trade the time/hassle of correlating visually correlating log entries for additional disk space. Time is far more expensive than disk space. Regarding fixed width problems, not sure why fixed-width columns would be a requirement. I'll assume it is some unusual internal pg formatting constraint. Apache and others obviously handle variable width logging... Regards, Ed Loehr
Ed Loehr writes: > Can anyone tell me how to log the db user name and db name to the server > log? Is this possible at present? Couldn't find anything about it in > the docs or deja... Now that you've found out that version 7.1, which will appear sometime within the next five years, can do this, I think if you crank up the debug level (-d) in 7.0 you should get some similar output. I'm fairly certain that the code was already there, I just wrapped it into a new name. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Wed, 6 Dec 2000, Tom Lane wrote: > Ed Loehr <eloehr@austin.rr.com> writes: > > Would it be better to > > (optionally) allow the db name and user name to be logged on each > > line? > > That would bulk up the log considerably, and also create formatting > problems (you couldn't really make these items be fixed-width columns). Not only that, but they would fill up almost all of the line (64+32 chars max) Perhaps you could write the data from the backendkeydata packet instead (pid + secret) even though that would also take op quite some space (atleast 8 + 8 chars) -- Bye, Peter Korsgaard
Peter Eisentraut wrote: > > Ed Loehr writes: > > > Can anyone tell me how to log the db user name and db name to the server > > log? Is this possible at present? Couldn't find anything about it in > > the docs or deja... > > Now that you've found out that version 7.1, which will appear sometime > within the next five years, can do this, I think if you crank up the debug > level (-d) in 7.0 you should get some similar output. I'm fairly certain > that the code was already there, I just wrapped it into a new name. Thanks. While helpful to know, that is not quite what I was thinking of, given it only logs the user name and db once (upon backend start-up). I was hoping for something that didn't require the correlation with an earlier log line in order to make sense of it. Granted this is redundant information if you can figure it out by connecting it with the earlier log line; I'm just suggesting what looks like a more easily usable approach. Regards, Ed Loehr
Ed Loehr writes: > was hoping for something that didn't require the correlation with an > earlier log line in order to make sense of it. Granted this is redundant > information if you can figure it out by connecting it with the earlier > log line; I'm just suggesting what looks like a more easily usable > approach. I would like to see something like the format-string based approach in Apache. But before we go too far with that we need to think about some other things in matters logging and error reporting. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/