Re: Feature request concerning postmaster log file.

Hi to all,
Could it be possible to have informations about who do what on any tablespace
or database logged in separate files, as samba does.
I don't think that it should be the default way, since the way events are
logged by now seems to please all users, but I'd think that a run-time option
could do the stuff.
The thing I have in mind is the following :
if the run-time option is chosen, any message issued by any command, from
connexion to all sql commands launched in any way by a user should go in a
separate log file, that could be named log.username for example.
Every message provided by the backend could go in the main logfile, as it does
by now. One could even imagine that every back-end program could have it's
own logfile (I think of autovac, bgwriter and so on).
This is the way samba daemon can be tuned, and I find it very usefull, when
one have to track an error found by one user.
Let me know if you find this idea usefull.
Thanks.

David BOURIAUD wrote:

> if the run-time option is chosen, any message issued by any command, from
> connexion to all sql commands launched in any way by a user should go in a
> separate log file, that could be named log.username for example.

You can enable logging user name into postgres log and grep or some
other text utility help you to generate required log file.

Zdenek

Le vendredi 9 novembre 2007, vous avez écrit :
> David BOURIAUD wrote:
> > if the run-time option is chosen, any message issued by any command, from
> > connexion to all sql commands launched in any way by a user should go in
> > a separate log file, that could be named log.username for example.
>
> You can enable logging user name into postgres log and grep or some
> other text utility help you to generate required log file.
>
Thanks Zdenek for your answer.
I know what you answered me, but in certain cases, using a text utility like
grep is not suitable. I think of this when you get a many hundreds of Mo
logfile at least, or when you need to track commands made by one peculiar
user in real-time.
That's why I thought that having a possibility to specify how log file(s)
should be handled by postmaster could be of great help.
I suggested an option to have one file per user, but one could think of having
one file per IP address connected to the server, and so on. The way samba log
files can be handled could be a good starting point, but I must admit that I
don't have the programming skills required to code any patch...
I hope that this answer will clarify my point of view.

David BOURIAUD wrote:
> Le vendredi 9 novembre 2007, vous avez écrit :
>> David BOURIAUD wrote:
>>> if the run-time option is chosen, any message issued by any command, from
>>> connexion to all sql commands launched in any way by a user should go in
>>> a separate log file, that could be named log.username for example.
>> You can enable logging user name into postgres log and grep or some
>> other text utility help you to generate required log file.
>>
> Thanks Zdenek for your answer.
> I know what you answered me, but in certain cases, using a text utility like
> grep is not suitable. I think of this when you get a many hundreds of Mo
> logfile at least, or when you need to track commands made by one peculiar
> user in real-time.
> That's why I thought that having a possibility to specify how log file(s)
> should be handled by postmaster could be of great help.
> I suggested an option to have one file per user, but one could think of having
> one file per IP address connected to the server, and so on. The way samba log
> files can be handled could be a good starting point, but I must admit that I
> don't have the programming skills required to code any patch...

I don't think we need to make our logging more clever than it already
is, when there's plenty of existing software out there to do
post-processing like that. For example, you could direct your logs to
syslog, and let it handle it. I don't think the stock syslogd can do it,
but for example with syslog-ng you could split logs based on regexp on
the message content, like the username.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

David BOURIAUD wrote:
> Le vendredi 9 novembre 2007, vous avez écrit :
>
>> David BOURIAUD wrote:
>>
>>> if the run-time option is chosen, any message issued by any command, from
>>> connexion to all sql commands launched in any way by a user should go in
>>> a separate log file, that could be named log.username for example.
>>>
>> You can enable logging user name into postgres log and grep or some
>> other text utility help you to generate required log file.
>>
>>
> Thanks Zdenek for your answer.
> I know what you answered me, but in certain cases, using a text utility like
> grep is not suitable. I think of this when you get a many hundreds of Mo
> logfile at least, or when you need to track commands made by one peculiar
> user in real-time.
> That's why I thought that having a possibility to specify how log file(s)
> should be handled by postmaster could be of great help.
> I suggested an option to have one file per user, but one could think of having
> one file per IP address connected to the server, and so on. The way samba log
> files can be handled could be a good starting point, but I must admit that I
> don't have the programming skills required to code any patch...
> I hope that this answer will clarify my point of view.
>
>

This would involve a lot of work, I suspect. It's doable but very far
from simple. But you could use log_line_prefix to tag the lines and then
pipe the log through a preprocessor. That would split them in real time
without imposing the logic requirements on postgres.

cheers

andrew

Heikki Linnakangas wrote:
> David BOURIAUD wrote:
>> Le vendredi 9 novembre 2007, vous avez écrit :
>>> David BOURIAUD wrote:
>>>> if the run-time option is chosen, any message issued by any command,
>>>> from
>>>> connexion to all sql commands launched in any way by a user should
>>>> go in
>>>> a separate log file, that could be named log.username for example.
>>> You can enable logging user name into postgres log and grep or some
>>> other text utility help you to generate required log file.
>>>
>> Thanks Zdenek for your answer.
>> I know what you answered me, but in certain cases, using a text
>> utility like grep is not suitable. I think of this when you get a many
>> hundreds of Mo logfile at least, or when you need to track commands
>> made by one peculiar user in real-time.
>> That's why I thought that having a possibility to specify how log
>> file(s) should be handled by postmaster could be of great help.
>> I suggested an option to have one file per user, but one could think
>> of having one file per IP address connected to the server, and so on.
>> The way samba log files can be handled could be a good starting point,
>> but I must admit that I don't have the programming skills required to
>> code any patch...
>
> I don't think we need to make our logging more clever than it already
> is, when there's plenty of existing software out there to do
> post-processing like that. For example, you could direct your logs to
> syslog, and let it handle it. I don't think the stock syslogd can do it,
> but for example with syslog-ng you could split logs based on regexp on
> the message content, like the username.
>

I agree with Heikki. And add some additional logging logic into postgres
could have some performance impact for every one. For tracking DTrace
could be good solution. It is now available on Solaris, FreeBSD and
MacOS. Currently there are not probes for your purpose, but if you
specify commonly usefull scenario we can implement related probes.

Zdenek

On Fri, 9 Nov 2007, David BOURIAUD wrote:

> using a text utility like grep is not suitable. I think of this when you
> get a many hundreds of MB logfile at least, or when you need to track
> commands made by one peculiar user in real-time.

If your problem is that individual log files are too big to search
usefully, one option is to set log_rotation_size so that doesn't happen.
As for real-rime, I do this all the time with some variant on

tail -f <log> | grep <user>

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD