How To Log User Name + Database?

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 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

* 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

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.

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:

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

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:

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/