pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger ddl actions in a table
4+ messages / 3 participants
[nested] [flat]
* trigger ddl actions in a table
@ 2007-11-15 15:29 "König, Monika" <monika.koenig@bonn.de>
2007-11-15 15:38 ` Re: trigger ddl actions in a table Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 4+ messages in thread
From: "König, Monika" @ 2007-11-15 15:29 UTC (permalink / raw)
To: pgsql-admin
I need a way for logging every action on the system catalog. I must have a
table which shows when a tables was created or updated or deleted. I need
the date of the change .
I tried to make an trigger on pg_catalog.pg_class but this throws an error :
"ERROR: permission denied: "pg_class" is a system catalog".
I can see all ddl in my log files, but
- there are even the errors in and
- i can't use it for a select
Is there an possibility for having such an system-action table??
Thanks
Monika König
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger ddl actions in a table
2007-11-15 15:29 trigger ddl actions in a table "König, Monika" <monika.koenig@bonn.de>
@ 2007-11-15 15:38 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2007-11-15 15:38 UTC (permalink / raw)
To: pgsql-admin
On Thu, Nov 15, 2007 at 04:29:51PM +0100, "König, Monika" wrote:
> I can see all ddl in my log files, but
> - there are even the errors in and
> - i can't use it for a select
There are logging options that should allow you to collect the DDL and then
parse it. You could then load it into a table. But to answer your
question,
> Is there an possibility for having such an system-action table??
you cannot today have triggers on system tables, and AFAIK nobody has
contributed a DDL-audit capability yet. IMO the safest (i.e. least subject
to attack) way to do this would be to pull it out of the WAL on another
machine. Given that we already have PITR, the data must be in there (maybe
not with a wallclock time, but with relative time). It seems this is
something that could be added, if someone were to do the work. I am not
aware that anyone is doing it at the moment, but I bet there are developers
who will work for sponsorship :)
A
--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke
^ permalink raw reply [nested|flat] 4+ messages in thread
* trigger ddl actions in a table
@ 2007-11-16 06:01 "König, Monika" <monika.koenig@bonn.de>
2007-11-16 20:04 ` Re: trigger ddl actions in a table Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 4+ messages in thread
From: "König, Monika" @ 2007-11-16 06:01 UTC (permalink / raw)
To: pgsql-admin
I need a way for logging every action on the system catalog. I must have a
table which shows when a tables was created or updated or deleted. I need
the date of the change .
I tried to make an trigger on pg_catalog.pg_class but this throws an error :
"ERROR: permission denied: "pg_class" is a system catalog".
I can see all ddl in my log files, but
- there are even the errors in and
- i can't use it for a select
Is there an possibility for having such an system-action table??
Thanks
Monika König
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger ddl actions in a table
2007-11-16 06:01 trigger ddl actions in a table "König, Monika" <monika.koenig@bonn.de>
@ 2007-11-16 20:04 ` Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 0 replies; 4+ messages in thread
From: Brad Nicholson @ 2007-11-16 20:04 UTC (permalink / raw)
To: "König, "Monika\"" <monika.koenig@bonn.de>; +Cc: pgsql-admin
On Fri, 2007-11-16 at 07:01 +0100, "König, Monika" wrote:
>
> I need a way for logging every action on the system catalog. I must have a
> table which shows when a tables was created or updated or deleted. I need
> the date of the change .
>
> I tried to make an trigger on pg_catalog.pg_class but this throws an error :
> "ERROR: permission denied: "pg_class" is a system catalog".
>
> I can see all ddl in my log files, but
> - there are even the errors in and
> - i can't use it for a select
>
>
> Is there an possibility for having such an system-action table??
You can't put triggers on the system catalog tables (as your error
points out). The only way that I know of to track this directly through
the DB is to log the DDL changes in the Postgres logs, and then parse
that out.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-11-16 20:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-15 15:29 trigger ddl actions in a table "König, Monika" <monika.koenig@bonn.de>
2007-11-15 15:38 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-11-16 06:01 trigger ddl actions in a table "König, Monika" <monika.koenig@bonn.de>
2007-11-16 20:04 ` Brad Nicholson <bnichols@ca.afilias.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox