Thread: RFC: Industrial-strength logging (long message)

Request For Comments:  Towards an industrial-strength
logging facility

1999-10-19, Tim Holloway mtsinc@leading.net

Introduction.

PostgreSQL is a commercial-quality DBMS. However, one item
generally found in commercial DBMS's that
PostgreSQL has so far lacked has been a logging facility.
Yes, it has a debugging facility that can
spit out reams of useful information, but debugging is not
logging - it has different goals and constraints. This,
then, is an attempt to provide that missg item.

What should a log look like?

This depends. I like a console-style listing, as my needs
are simple. Others would prefer that the
log be itself a database. Happily, I think that what I have
developed so far can be used for both.
While it's perilous to attempt to be all things to all
people, my experience in working with the Amiga was that
simplicity doesn't have to mean rigidity or lack of ability.
Preliminary design efforts have resulted in a plan that I
think will satisfy the majority of DBA's. Time will tell.

Design goals

1. Robustness. Adding logging should not cause the system to
become unstable.
2. Performance. Unless you're IBM at least, logging is a
means, not an end. The performance of the system
must not be degraded.
3. Security. Since logging is often part of a security
effort, it's only reasonable that the log itself
be secure. As of this writing, security is that of the
PostgreSQL backend and/or syslog facilities.
4. Routability. Preliminary design permits routing any or
all events to multiple destinations, each of which is
individually controllable as to format. Abuse of this power
may impact 2), above, however.
5. Locale support.  Not everyone's preferred language is
English. Because each and every log message is fully
configurable, and because care is given to formatting based
on locale, the DBA can customize logging to the convenience
of his or her own culture. I hope that those who benfit from
this will keep my on the proper path.

Implementation

"Simple things should be simple and complex things should be
possible"
              Alan Kay

I've seen far too many systems where simple things were
complex and complex things were simple and other
variantions on that theme. I HOPE that's not what I'm
producing. If I am, PLEASE LET ME KNOW!!!

Although the same mechanism is at work at all times, the
defaults are set to display just enough information to let
you know that more is possible:

Postgres [123] 900 - Logging configuration file
"/usr/local/pgsql/data/postgresql.conf" was not found or
denied access. Using default logging.
Postgres [123] 101 - Server started
Postgres [123] 102 - Server shutdown

These messages are routed to stderr (if available) for the
backend process AND to syslog (if available).
If there are other worthy default channels, I'd like to know
them.

The Next Level

The logging configuration file allows customizing of
logging. At one extreme, it can be used to suppress ALL
logging - even the default items listed above. At the other
- suffice it to say that you can get VERY elaborate.

There are 3 types of information in the logging
configuration file (which may, but likely won't, be part of
pg_hba.conf) Logging info is read at startup. There may
exist signals that cause it to be reread, but not just yet.

They are:

1. General log control. For example, suppression of
high-demand activities BEFORE they get run, formatted and
sent to the log subsystem.

2. Message format. Allows definition/override of message
formats on a class (see below) and individual basis. This is
both how formatting for database load and locale are done.
Multiple message formats are supported!

3. Message routing. Allows definition of the destination(s)
of log messages. Each destination (channel) can select which
messages it will format and report. To avoid potential loss
of critical info, any message not explicitly routed at least
once gets reported on the default channel - stderr/syslog,
unless otherwise configured.

Message classes

Implicit in the desire for logging into a database is the
understanding that some types of messages may have identical
formats but different content. To facilitate this (and to
aid in locale support) each possible message has a unique
identifier, and related messages (those which would route to
the same table)  are grouped into classes, identified by
century, as in the http and other familiar protocols.

Classes for PostgreSQL logging are not grouped by severity,
however, but by their affinity for a given
statistical table. Tentatively:

1xz - The PostgreSQL server
2xx - User-related information
3xx - Transaction information
4xx - EXPLAIN results (???)
9xx - General system alerts

Right now, the following are considered likely candidates,
subject to user feedback:

server infoServer name, signal ID
101 - Server started
102 - Server shutdown
103 - Signal xxx received
104 - Server ABEND

user sessionuserid, port or terminal ID, authentication scheme name
(e.g. md5). session ID
201 - User xxxx connected via port/terminal xxxxxxxx
authenticated by aaaaa
202 - User xxxx disconnected
203 - FORBIDDEN - connection denied for user xxxx via
port/terminal xxxxxxxxxx rejected by aaaaaaa

show commandsSession ID, command text
301 - SELECT text
302 - INSERT text
303 - UPDATE text
304 - DELETE text

show resultssession ID, count or OID. primary/first/only table ID
affected
401 - SUCCESS - nnn records retrieved
402 - SUCCESS - record inserted at OID
403 - SUCCESS - nnn records updated
404 - SUCCESS - nnn records deleted
405 - FORBIDDEN - action xxxxxx denied to user xxxx on table
xxxxxxxx

explainas below:
500 EXPLAIN transaction ID sequence cost rows bytes

miscellaneousexplanatory text
900 - Logging configuration file "ffff" was not found or
denied read access. Using default logging.
901 - Logging configuration file "ffff" could not be
processed - invalid text at line nnn.
902 - User overrides non-existent message ID nnn
903 - Channel requests non-existent message ID nnn
904 - end of section starting on line nnn was not found
905 - start of section ending on line nnn was not found
906 - (message from logging configuration file)

Multiple message format tables may be defined. Each of these
overrides or disables one or more of of the messages listed
above - or its "final" equivalent. Messages which aren't
overridden display in their default (English text) mode.
Because this could be VERY rude to a table loader, each
channel must explicitly request which messages are
acceptable (this also facilitates routing of message
classes). The default channel catches unsolicited messages
as a safeguard. To make it easier, both common message
formats in the message format tables and their
correspoonding solicitations in the channel definitions
allow ranges to be defined. E.g., you can define a logfile
format for messages 301-304 rather than having to replicate
the same format for each.

A brief example --- SUBJECT TO RADICAL CHANGE! ---

; One possible implementation of logging configuration:
; an SQL style - verb attribute(value[s]) might be better?
;
<logging>

<options>
level = warning ; of INFO, NOTICE, DEBUG, WARNING, ERROR, or
FATAL
log directory = /var/log/postgresql
startmessage = " This is a sample log configuration" ;
output via message 906
endmessage = "Have a Nice Day" ; output via message 906
</options>

<format name=info>
101 INFO "%n [%p] fing an" 
102 INFO "%n [%p] ist zu Ende" 
</format>

<format name=database>
201-203 INFO "%u %p"
301-304 INFO "'%2s'" ; quote with sql escapes
</format>

<channel>
format name : info
output : syslog( localost )
level : INFO
solicit : 101-104, default
</channel>

<channel>
format name : database-user
timestamp: local
file : user.log
solicit : 201-203
</channel>

<channel>
format name : database-session
file : session.log
solicit : 301-304
</channel>

; *** The default message channel ***
<channel>
output : syslog( dba.mousetech.com )
solicit : 1**, 9**, default
</channel>

</logging>

Apology

Although this scheme may appear elaborate, the internal
realization is fairly simple. I have far more concern that
it may overwhelm someone who is new to the entire PostgreSQL
system and is FAR more interested at that time in learning
POSTGRES! The plus side is that it's possible to amass a
library of mix-and-match blocks so that the more sordid
details need not be recreated endlessly by every DBA in the
world.

Credit where it's due

Asture observers may have noticed that the user-definable
message format is a blatant ripoff from Apache. The concept
of logging channels I lifted from bind, the DNS utility.
Some folding, spindling, stapling and/or mutilation may have
occurred in the process.

Feedback Needed

The details are still very much fluid, so your opinion
counts!!!! What's good? What's bad?
What can be improved, and what should be immediately hauled
off to the nearest toxic waste disposal center? Especially
of interest is what the shape of the config file should be.
Is the the pseudo-HTML format shown good? Would an SQL
statement form be preferable? Maybe something like LISP or
C? Or something entirely different? Please tell me! All I
ask is that it be YACC-parseable. Email your thoughts to me
at mtsinc@leading.net, subject: PostgreSQL logging. Results
will be posted to pgsql-admin and pgsql-hackers mailing
lists. Thank You.
  Tim Holloway    MTS Associates, Inc.

Thus spake Tim Holloway
> Request For Comments:  Towards an industrial-strength
> logging facility

<COMMENT>Woo hoo!</COMMENT>

Yes please.  As soon as possible.  I have been trying to figure out all
sorts of kluges for this.  I even considered putting something in PyGreSQL
but this is mush better.

> Design goals
> 
> 1. Robustness. Adding logging should not cause the system to
> become unstable.

Absolutely.

> 2. Performance. Unless you're IBM at least, logging is a
> means, not an end. The performance of the system
> must not be degraded.

If performance takes a hit, could it be turned on and off with a flag
or by the existence of the config file itself?  That way people willing
to pay for the logging can and those that need performance above all
can get it.

> Postgres [123] 900 - Logging configuration file
> "/usr/local/pgsql/data/postgresql.conf" was not found or
> denied access. Using default logging.

Or don't log - see above.

The one thing I would suggest is make sure that logs get date and time stamped.

How about the ability to send the log to a process instead of a file?  I
would like to log on a separate machine but there is firewall considerations.

> Although this scheme may appear elaborate, the internal
> realization is fairly simple. I have far more concern that
> it may overwhelm someone who is new to the entire PostgreSQL
> system and is FAR more interested at that time in learning

I don't see a problem here.  Logging would be an advanced subject.  No
one would have to deal with it.  I think it is important that it not
log (or log to /dev/null) by default so that new users don't suddenly
find their disk space disappearing.  Logging (especially if you log
SELECTs) cand use a lot of space.

Good work.  This was definitely needed.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Tim Holloway <mtsinc@southeast.net> writes:
> Request For Comments:  Towards an industrial-strength
> logging facility

Sounds pretty good overall.

> This depends. I like a console-style listing, as my needs are
> simple. Others would prefer that the log be itself a database.

Note that logging into a table is harder than you might think.
For example: (1) The postmaster cannot touch tables at all, so
no events detected in the postmaster could be logged that way.
(2) No events detected during a transaction that ultimately
aborts will be successfully logged.  (3) Logging events such as
"server failure" would be quite risky --- if the server has
suffered internal state corruption then it could manage to
corrupt the log table entirely while it's trying to add its
last-dying-breath report.

Fortunately none of these problems apply to stderr, syslog,
or plain-text-file reporting channels.

> There are 3 types of information in the logging
> configuration file (which may, but likely won't, be part of
> pg_hba.conf)

No, it should definitely not be part of pg_hba.conf, it should
be a separate configuration file.  (pg_hba.conf has a syntax too
simple to be easily extensible.)

Another possibility is to keep the config info in a system table, but
that would have a number of drawbacks (the postmaster cannot read
tables, nor can a backend that's just starting up and hasn't finished
initialization).  On the whole, a plain text file in the database
directory is probably the best bet.

> Logging info is read at startup. There may
> exist signals that cause it to be reread, but not just yet.

There MUST exist a way to alter the logging level on-the-fly;
IMHO this is a rock bottom, non negotiable requirement.
A production system can't restart the postmaster just to tweak
the logging level up or down for investigation of a problem.

Whether it's a signal or something else remains to be determined.
We have pretty nearly used up all the available signal numbers :-(.
I suppose that whichever signal is currently used to trigger
rereading of the pg_options configuration file could also trigger
re-reading of the logging config file.

> To avoid potential loss
> of critical info, any message not explicitly routed at least
> once gets reported on the default channel - stderr/syslog,
> unless otherwise configured.

Hmm, so I'd have to explicitly discard every message I didn't want to
hear about?  I think that "forced display" like this should only happen
for high-severity messages, not for routine junk.  There doesn't seem to
be any notion of message importance in your design, but I think there
should be.  Most people would probably prefer to filter on an importance
level, only occasionally resorting to calling out specific message types.

> Especially of interest is what the shape of the config file should be.
> Is the the pseudo-HTML format shown good?

You could do worse than to borrow BIND's syntax for log control.
        regards, tom lane

hi...

> > This depends. I like a console-style listing, as my needs are
> > simple. Others would prefer that the log be itself a database.
> 
> Note that logging into a table is harder than you might think.

unless i misunderstand, the concept is to design the logs such that it is
trivial to convert them into a database, even including tools to do this, not
to actually create a database on the fly.
i'm also supportive of including tools to run standard reports out-of-the-box
on these logs. using pgsql to massage its own logs is pretty sexy, imo =)

> No, it should definitely not be part of pg_hba.conf, it should
> be a separate configuration file.  (pg_hba.conf has a syntax too
> simple to be easily extensible.)
> initialization).  On the whole, a plain text file in the database
> directory is probably the best bet.

agreed...

> There MUST exist a way to alter the logging level on-the-fly;
> IMHO this is a rock bottom, non negotiable requirement.

whilst i don't think this is MUST, it is EXTREMELY desirable and would make the
logging actually useful for large installations =)

> Whether it's a signal or something else remains to be determined.
> We have pretty nearly used up all the available signal numbers :-(.
> I suppose that whichever signal is currently used to trigger
> rereading of the pg_options configuration file could also trigger
> re-reading of the logging config file.

why not use pg_options for logging cofig?

> Hmm, so I'd have to explicitly discard every message I didn't want to
> hear about?  I think that "forced display" like this should only happen
> for high-severity messages, not for routine junk.  There doesn't seem to
> be any notion of message importance in your design, but I think there
> should be.  Most people would probably prefer to filter on an importance
> level, only occasionally resorting to calling out specific message types.

systems i've dealt with in the past prioritize (as you mentioned) on a numeric
scale to reflect "importance" and the logging level is by default set at a
certain "height"... increasing logging is as easy as changing the threshold..
this has been effective in the past...

the only problem with ONLY relying on thresholds is that its a very coarse
grain method... so when you request a lower level of logging, you often get
inundated with all SORTS of stuff you don't really care/want...

a hybridization between the two approaches might be best.. e.g., each type of
message gets assigned a "criticality", between 1 and 10 perhaps. with 1 being
most critical, and 10 being least..  therefore, the higher you set your logging
level, the more messages you get (logical?)

the default behaviour for each level (1..10) is to let everything though on
that level. but you can apply filters to limit the output...
so you get the ability to define grossly with thresholds (1..10) and finely
(and optionally) with message filters...

> You could do worse than to borrow BIND's syntax for log control.

much worse. =)

-- 
Aaron J. Seigo
Sys Admin

On Sat, 23 Oct 1999, Aaron J. Seigo wrote:

> > There MUST exist a way to alter the logging level on-the-fly;
> > IMHO this is a rock bottom, non negotiable requirement.
> 
> whilst i don't think this is MUST, it is EXTREMELY desirable and would make the
> logging actually useful for large installations =)

Let's re-iterate Tom here: There MUST exist a way ... someone *MUST* be
able to change their configuration without having to physically stop/start
the server to affect the changes ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

Tom Lane wrote:
> 
> Tim Holloway <mtsinc@southeast.net> writes:
> > Request For Comments:  Towards an industrial-strength
> > logging facility
> 
> Sounds pretty good overall.
> 
> > This depends. I like a console-style listing, as my needs are
> > simple. Others would prefer that the log be itself a database.
> 
> Note that logging into a table is harder than you might think.
> For example: (1) The postmaster cannot touch tables at all, so
> no events detected in the postmaster could be logged that way.
> (2) No events detected during a transaction that ultimately
> aborts will be successfully logged.  (3) Logging events such as
> "server failure" would be quite risky --- if the server has
> suffered internal state corruption then it could manage to
> corrupt the log table entirely while it's trying to add its
> last-dying-breath report.
> 
> Fortunately none of these problems apply to stderr, syslog,
> or plain-text-file reporting channels.

Thanks, Tom! I'l file this collection of wisdom to help keep
me on the straight and
narrow. I guess I should have mentioned - at least in its
initial incarnation, cowardice
forbids me to attempt reading or writing PostgreSQL tables
directly. The logfile design is
designed to be text and customizable. If one of those custom
formats just happens to look
like loadable data, well..... :) 

BTW, cowardice also forbids me to attempt message filtering
except by message ID or severity
just yet (no "log all requests from Clevleand to channel 2"
stuff). I will try to provide a
stub for the adventurous, though. For everyone else, there's
Perl.

> > There are 3 types of information in the logging
> > configuration file (which may, but likely won't, be part of
> > pg_hba.conf)
> 
> No, it should definitely not be part of pg_hba.conf, it should
> be a separate configuration file.  (pg_hba.conf has a syntax too
> simple to be easily extensible.)

Of more concern to me was that I THINK I saw pg_hba.conf
being rescanned whenever security
was tested. I don't want to slow that down with a lot of
"one-time" (see below) data.
> 
> Another possibility is to keep the config info in a system table, but
> that would have a number of drawbacks (the postmaster cannot read
> tables, nor can a backend that's just starting up and hasn't finished
> initialization).  On the whole, a plain text file in the database
> directory is probably the best bet.

I think so too -- you just reinforced my feelings. There's
no intrinsic
benefit, since the error messages and channel definition get
compiled
into memory, anyhow.
> 
> > Logging info is read at startup. There may
> > exist signals that cause it to be reread, but not just yet.
> 
> There MUST exist a way to alter the logging level on-the-fly;
> IMHO this is a rock bottom, non negotiable requirement.
> A production system can't restart the postmaster just to tweak
> the logging level up or down for investigation of a problem.

OK, I'm convinced!
> 
> Whether it's a signal or something else remains to be determined.
> We have pretty nearly used up all the available signal numbers :-(.
> I suppose that whichever signal is currently used to trigger
> rereading of the pg_options configuration file could also trigger
> re-reading of the logging config file.

How about via psql or other facilities passing a message
packet?
Can you think of any cases where this would fail? BETTER
YET!
Is there any reason whay pg_options cannot be extended? It
seems like
a natural fit to me - the only reason I didn't suggest it
originally was that
it's been so low-key, I forgot it was there!

> 
> > To avoid potential loss
> > of critical info, any message not explicitly routed at least
> > once gets reported on the default channel - stderr/syslog,
> > unless otherwise configured.
> 
> Hmm, so I'd have to explicitly discard every message I didn't want to
> hear about?  I think that "forced display" like this should only happen
> for high-severity messages, not for routine junk.  There doesn't seem to
> be any notion of message importance in your design, but I think there
> should be.  Most people would probably prefer to filter on an importance
> level, only occasionally resorting to calling out specific message types.

Good point, but it was the second item on the message
override line:

101 INFO "Server started"    A
-----+

The intent was actually more to ensure that if a new release
added new messages, they
wouldn't suddenly pop up in places they'd cause the receptor
to get indigestion (e.g. table loader)
or have critical messages get lost entirely. I did the
pseudocode for lossless message routing
today -- adding a dropout threshold doesn't look like a
major problem.
> 
> > Especially of interest is what the shape of the config file should be.
> > Is the the pseudo-HTML format shown good?
> 
> You could do worse than to borrow BIND's syntax for log control.

*I* like it (I must - I stole almost everything else from
there!). That's what I meant
by "is a "C" format good?". It works well as an extension to
pg_options. Just wanted to see what
others would be most comfortable with.

Again, thanks! This is a big help!
  Tim Holloway

The Hermit Hacker wrote:
> 
> On Sat, 23 Oct 1999, Aaron J. Seigo wrote:
> 
> > > There MUST exist a way to alter the logging level on-the-fly;
> > > IMHO this is a rock bottom, non negotiable requirement.
> >
> > whilst i don't think this is MUST, it is EXTREMELY desirable and would make the
> > logging actually useful for large installations =)
> 
> Let's re-iterate Tom here: There MUST exist a way ... someone *MUST* be
> able to change their configuration without having to physically stop/start
> the server to affect the changes ...
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

I think we have a consensus. Destroy and recreate logging
data structures/tasks on receipt of
suitable event.

For simple things like log levels, though, I'd still like
feedback on
desirablility and feasibility of altering basic logging
options though
(authorized!) frontends. As a user, I get nervous when I
have to thread
my way past possibly-fragile unrelated items in a config
file when I'm trying
to do a panic diagnosis. As an administrator, I get even
MORE nervous if one
of the less careful people I know were to be entrusted with
that task.

Another possible mode of controlling what's logged is to
assign mask bits to various
classes of messaages and allow the administrator to alter
the filter mask.
Although, in truth, the channel design is pretty much the
same thing.
   Tim Holloway

Tim Holloway <mtsinc@southeast.net> writes:
>> Note that logging into a table is harder than you might think.

> I guess I should have mentioned - at least in its initial incarnation,
> cowardice forbids me to attempt reading or writing PostgreSQL tables
> directly. The logfile design is designed to be text and
> customizable. If one of those custom formats just happens to look like
> loadable data, well..... :)

Yeah, someone else suggested writing to a textfile and then having a
cron task or something like that load the data into a table later on.
That seems workable, but you'd need some answer to the following
problem.  Suppose that for some reason (like trying to diagnose a
transient problem) the log level is currently set high enough so that
every "insert" command generates a log entry.  The appointed hour
comes 'round and your cron task fires off.  Each time it copies data
out of the logfile into the database, it is itself adding at least one
more entry to the logfile.  Can you say "infinite loop"?

I can think of a couple of possible workarounds, but the one that seems
most natural is to let the logging task override the system-wide logging
level and set its own log level to something low.  That ties right in
with your followup comment:

> For simple things like log levels, though, I'd still like feedback on
> desirablility and feasibility of altering basic logging options though
> (authorized!) frontends.

I think you were thinking here of altering the system-wide level through
a frontend command, but what I'm envisioning is allowing an SQL client
to alter the log level for its own particular backend *without* any
system-wide effects.

Even that ability might need to be restricted to suitably-privileged
users, else it could be used to "fly under the radar" of an admin who
was using logging for security purposes.  Perhaps I'm being overly
paranoid, though.  There are probably only a few message types that
might be of interest for security purposes, so maybe we could define the
filtering commands in such a way that those messages are not disablable
from a client.  Anyone else have strong feelings about this?

>> No, it should definitely not be part of pg_hba.conf, it should
>> be a separate configuration file.  (pg_hba.conf has a syntax too
>> simple to be easily extensible.)

> Of more concern to me was that I THINK I saw pg_hba.conf being
> rescanned whenever security was tested.

That's true, pg_hba.conf is currently reread on each connection attempt.
We probably ought to try to avoid that... but in any case I think there
are obvious security reasons for keeping access authorization info
strictly separate from other configuration data.

> Good point, but it was the second item on the message
> override line:

> 101 INFO "Server started"
>      A
> -----+

Oops, I missed that...
        regards, tom lane

Why not do something similar to what we are doing with pg_shadow?  If I
remember the logic right, when you update pg_shadow, one ofits "steps" is
to dump it to a text file so that postmaster can read it?  this should
make it easy for one user/database to have one logging set, while another
doesn' have it set at all...and should make it so that each database
*should* theoretically log to different files/mechanisms?

On Sat, 23 Oct 1999, Tim Holloway wrote:

> 
> 
> The Hermit Hacker wrote:
> > 
> > On Sat, 23 Oct 1999, Aaron J. Seigo wrote:
> > 
> > > > There MUST exist a way to alter the logging level on-the-fly;
> > > > IMHO this is a rock bottom, non negotiable requirement.
> > >
> > > whilst i don't think this is MUST, it is EXTREMELY desirable and would make the
> > > logging actually useful for large installations =)
> > 
> > Let's re-iterate Tom here: There MUST exist a way ... someone *MUST* be
> > able to change their configuration without having to physically stop/start
> > the server to affect the changes ...
> > 
> > Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> > Systems Administrator @ hub.org
> > primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
> 
> I think we have a consensus. Destroy and recreate logging
> data structures/tasks on receipt of
> suitable event.
> 
> For simple things like log levels, though, I'd still like
> feedback on
> desirablility and feasibility of altering basic logging
> options though
> (authorized!) frontends. As a user, I get nervous when I
> have to thread
> my way past possibly-fragile unrelated items in a config
> file when I'm trying
> to do a panic diagnosis. As an administrator, I get even
> MORE nervous if one
> of the less careful people I know were to be entrusted with
> that task.
> 
> Another possible mode of controlling what's logged is to
> assign mask bits to various
> classes of messaages and allow the administrator to alter
> the filter mask.
> Although, in truth, the channel design is pretty much the
> same thing.
> 
>     Tim Holloway
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

The Hermit Hacker <scrappy@hub.org> writes:
> Why not do something similar to what we are doing with pg_shadow?  If I
> remember the logic right, when you update pg_shadow, one ofits "steps" is
> to dump it to a text file so that postmaster can read it?

I thought about suggesting that, but IIRC the pg_shadow stuff doesn't
really *work* very well --- CREATE USER and friends know that they
are supposed to dump the table to a textfile after modifying it,
but heaven help you if you try poking pg_shadow with vanilla SQL
commands.  And I bet aborting a transaction after it does a CREATE USER
doesn't undo the changes to the flat file, either.

So, unless someone is feeling inspired to go rework the way the pg_shadow
stuff is handled, I don't think it's a good model to emulate.
        regards, tom lane

Tom Lane wrote:
>
> Tim Holloway <mtsinc@southeast.net> writes:
> >> Note that logging into a table is harder than you might think.
>
> > I guess I should have mentioned - at least in its initial incarnation,
> > cowardice forbids me to attempt reading or writing PostgreSQL tables
> > directly. The logfile design is designed to be text and
> > customizable. If one of those custom formats just happens to look like
> > loadable data, well..... :)
>
> Yeah, someone else suggested writing to a textfile and then having a
> cron task or something like that load the data into a table later on.
> That seems workable, but you'd need some answer to the following
> problem.  Suppose that for some reason (like trying to diagnose a
> transient problem) the log level is currently set high enough so that
> every "insert" command generates a log entry.  The appointed hour
> comes 'round and your cron task fires off.  Each time it copies data
> out of the logfile into the database, it is itself adding at least one
> more entry to the logfile.  Can you say "infinite loop"?

You noticed that too, eh? You might want to take a look at the archived
psql-admin postings about the middle of last week. Since I'm working on the
premise that all log files are text files and there's already been the desire
expressed that they be rotatable, it's simplest to piggyback the load function
onto rotation: start a new file and load the prior one. It introduces
some latency into the log tables (forcing rotation can obviously cure this),
but should eliminate the log recursion by deferring the entries.
Hmmmm. Maybe the initial log filter WON'T be just a stub!

>
> > For simple things like log levels, though, I'd still like feedback on
> > desirablility and feasibility of altering basic logging options though
> > (authorized!) frontends.
>
> I think you were thinking here of altering the system-wide level through
> a frontend command, but what I'm envisioning is allowing an SQL client
> to alter the log level for its own particular backend *without* any
> system-wide effects.
>
> Even that ability might need to be restricted to suitably-privileged
> users, else it could be used to "fly under the radar" of an admin who
> was using logging for security purposes.  Perhaps I'm being overly
> paranoid, though.  There are probably only a few message types that
> might be of interest for security purposes, so maybe we could define the
> filtering commands in such a way that those messages are not disablable
> from a client.  Anyone else have strong feelings about this?

I seem to read a desire to log frontend action in what you're saying.
I guess I should define my ambitions. Initially, at least, all I'm trying
to log is server activity, and that from an administrative point of view.
I don't plan to subsume the debugging system, because:
1. IMHO, it works fine as is (excepting the lack of timestamping)2. The debugging messages weren't designed to fit the
constraintsofthe logger. That would require reworking dozens of messages all overthe program. I'd almost certainly
breaksomething critical.
 

Of course, the line between event logging and debugging is pretty fuzzy and
apt to change, depending on what you need at the moment.

I didn't consider logging as related to front-ends, since: they're more
of a programmer's problem; there exist a multitude of them, and some -
like ODBC - already have their own logging. I'm open to suggestion, though
I think that's too big a bite to chew just yet.

On Oct 23, Tim Holloway mentioned:

> I think we have a consensus. Destroy and recreate logging data
> structures/tasks on receipt of suitable event.
> 
> For simple things like log levels, though, I'd still like feedback on
> desirablility and feasibility of altering basic logging options though
> (authorized!) frontends. As a user, I get nervous when I have to
> thread my way past possibly-fragile unrelated items in a config file
> when I'm trying to do a panic diagnosis. As an administrator, I get
> even MORE nervous if one of the less careful people I know were to be
> entrusted with that task.

What about
SET LOGLEVEL TO <something>;
SET LOGDETAIL TO <something>;
or the like. You could use pg_shadow.usesuper as a security stipulation.
Using something like a signal to do this is probably overkill, especially
since there are hardly any left, and it's also infinitely less intuitive
and flexible.
-Peter

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

hi...

> What about
> SET LOGLEVEL TO <something>;
> SET LOGDETAIL TO <something>;
> or the like. You could use pg_shadow.usesuper as a security stipulation.
> Using something like a signal to do this is probably overkill, especially
> since there are hardly any left, and it's also infinitely less intuitive
> and flexible.

this would be done from psql? if so, here's a query i have: are there any plans
to seperate the admin functions out of psql and into another seperate tool? 

i have a queasyness with general users having access to a tool that can
do admin takss (even if they supposedly don't have a superuser account).

it also seems much cleaner to have admin in one tool and data interaction in
another.

am i off track here?

-- 
Aaron J. Seigo
Sys Admin

On Mon, 25 Oct 1999, Aaron J. Seigo wrote:

> hi...
> 
> > What about
> > SET LOGLEVEL TO <something>;
> > SET LOGDETAIL TO <something>;
> > or the like. You could use pg_shadow.usesuper as a security stipulation.
> > Using something like a signal to do this is probably overkill, especially
> > since there are hardly any left, and it's also infinitely less intuitive
> > and flexible.
> 
> this would be done from psql? if so, here's a query i have: are there any plans
> to seperate the admin functions out of psql and into another seperate tool? 
> 
> i have a queasyness with general users having access to a tool that can
> do admin takss (even if they supposedly don't have a superuser account).

There is no such thing, actually...all "admin commands" are seperate SQL
queries...psql is merely one of many interfaces that allows one to talk to
and send queries to the backend...what the backend then does with the
query is where the security lies...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

> I think we have a consensus. Destroy and recreate logging
> data structures/tasks on receipt of
> suitable event.
> 
> For simple things like log levels, though, I'd still like
> feedback on
> desirablility and feasibility of altering basic logging
> options though
> (authorized!) frontends. As a user, I get nervous when I
> have to thread
> my way past possibly-fragile unrelated items in a config
> file when I'm trying
> to do a panic diagnosis. As an administrator, I get even
> MORE nervous if one
> of the less careful people I know were to be entrusted with
> that task.

One more item I have not heard is that you can create virtual table that
look like tables but return data about users, queries on SELECT.

Informix does this.  Allows you to get info, without the need for
storage.  Not good for every case, but an interesting idea sometimes.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian wrote:
> 
> 
> One more item I have not heard is that you can create virtual table that
> look like tables but return data about users, queries on SELECT.
> 
> Informix does this.  Allows you to get info, without the need for
> storage.  Not good for every case, but an interesting idea sometimes.
> 

This should come naturally after the function interface is updated
to enable it to return cursors.

A very desirable feature, but I'm not sure anyone is actually working on it.

-------------------
Hannu