pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers and System Tables.
5+ messages / 3 participants
[nested] [flat]
* Triggers and System Tables.
@ 2002-05-27 22:46 Stef Telford <stef@chronozon.artofdns.com>
2002-05-28 05:29 ` Re: Triggers and System Tables. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: stef@chronozon.artofdns.com @ 2002-05-27 22:46 UTC (permalink / raw)
To: pgsql-sql
Hello,
this is probably a really silly question, but where can i
control wether or not the superuser/root user can create a trigger
on a system table ?
Currently, have been tinkering with the idea of adding
in the ability to have 'session variables' for the connection, so
that before a transaction it can declare and save variables, so
that they can be used in the transaction themselves. This would
ease some problems i have with inheritance. It also would make
some sort of connection limiting per user a lot easier if it was
possible to attach triggers to pg_stat_activity (on INSERT only :)
So, the real question is, is this a question for hackers
mailing list or can anyone tell me how to drop the limitation ?
many thanks
Stef
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Triggers and System Tables.
2002-05-27 22:46 Triggers and System Tables. Stef Telford <stef@chronozon.artofdns.com>
@ 2002-05-28 05:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-05-28 05:29 UTC (permalink / raw)
To: Stef Telford <stef@chronozon.artofdns.com>; +Cc: pgsql-sql
On Mon, 27 May 2002, Stef Telford wrote:
>
> Hello,
> this is probably a really silly question, but where can i
> control wether or not the superuser/root user can create a trigger
> on a system table ?
In general this is not something you can safely do. I'm not sure if
pg_stat_activity modifications go through something that will fire
triggers, but in general you shouldn't expect that they will on system
tables.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Triggers and System Tables.
@ 2002-05-28 05:35 Stef Telford <stef@chronozon.artofdns.com>
2002-05-28 06:51 ` Re: Triggers and System Tables. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: stef@chronozon.artofdns.com @ 2002-05-28 05:35 UTC (permalink / raw)
To: sszabo@megazone23.bigpanda.com; stef@chronozon.artofdns.com; +Cc: pgsql-sql
> Hello,
> this is probably a really silly question, but where can i
> control wether or not the superuser/root user can create a trigger
> on a system table ?
In general this is not something you can safely do. I'm not sure if
pg_stat_activity modifications go through something that will fire
triggers, but in general you shouldn't expect that they will on system
tables.
It would make sense to assume that, even though the tables
in question are system tables, that triggers can be used
in unison with them. I do, of course, understand the
inherent dangers with attaching triggers (it could
be possible to attach a trigger that would delete user
logins) but then, this is what i want them -for- :)
The limitation would, to my untrained eye, appear to be
completely aberant. The ability to put triggers on
system tables would allow me to (using plpgsql)
* Limit and/or assign a 'number of logins'
* Create and/or use 'connection' or temporary 'global variables'
These may end up being 'quick and dirty' and by no means
tailored to maximum performance, but it would certainly
be a 'start' if nothing else.
regards
Stef
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Triggers and System Tables.
2002-05-28 05:35 Re: Triggers and System Tables. Stef Telford <stef@chronozon.artofdns.com>
@ 2002-05-28 06:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-05-28 14:35 ` Re: Triggers and System Tables. Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2002-05-28 06:51 UTC (permalink / raw)
To: Stef Telford <stef@chronozon.artofdns.com>; +Cc: pgsql-sql
On Tue, 28 May 2002, Stef Telford wrote:
> > Hello,
> > this is probably a really silly question, but where can i
> > control wether or not the superuser/root user can create a trigger
> > on a system table ?
>
> In general this is not something you can safely do. I'm not sure if
> pg_stat_activity modifications go through something that will fire
> triggers, but in general you shouldn't expect that they will on system
> tables.
>
As a note, pg_stat_activity is a view and doesn't appear to be
modified directly (it looks like it gets its real info
from various functions). And, IIRC it isn't guaranteed to be
completely up to date.
> It would make sense to assume that, even though the tables
> in question are system tables, that triggers can be used
> in unison with them. I do, of course, understand the
> inherent dangers with attaching triggers (it could
> be possible to attach a trigger that would delete user
> logins) but then, this is what i want them -for- :)
>
> The limitation would, to my untrained eye, appear to be
> completely aberant. The ability to put triggers on
> system tables would allow me to (using plpgsql)
AFAIK many/most places that want to modify system tables don't
do normal queries to do so and as such probably won't do rule
rewriting or trigger invokations. Part of this is probably a
performance reason, part is probably the dangerousness of it
which would tend to make it probably a lower priority for anyone
to consider looking at.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Triggers and System Tables.
2002-05-28 05:35 Re: Triggers and System Tables. Stef Telford <stef@chronozon.artofdns.com>
2002-05-28 06:51 ` Re: Triggers and System Tables. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-05-28 14:35 ` Jan Wieck <janwieck@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jan Wieck @ 2002-05-28 14:35 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Stef Telford <stef@chronozon.artofdns.com>; pgsql-sql
Stephan Szabo wrote:
>
> On Tue, 28 May 2002, Stef Telford wrote:
>
> > > Hello,
> > > this is probably a really silly question, but where can i
> > > control wether or not the superuser/root user can create a trigger
> > > on a system table ?
> >
> > In general this is not something you can safely do. I'm not sure if
> > pg_stat_activity modifications go through something that will fire
> > triggers, but in general you shouldn't expect that they will on system
> > tables.
> >
>
> As a note, pg_stat_activity is a view and doesn't appear to be
> modified directly (it looks like it gets its real info
> from various functions). And, IIRC it isn't guaranteed to be
> completely up to date.
You're right. All the pg_stat* relations are views, and are
NOT up to date to the millisecond.
>
> > It would make sense to assume that, even though the tables
> > in question are system tables, that triggers can be used
> > in unison with them. I do, of course, understand the
> > inherent dangers with attaching triggers (it could
> > be possible to attach a trigger that would delete user
> > logins) but then, this is what i want them -for- :)
> >
> > The limitation would, to my untrained eye, appear to be
> > completely aberant. The ability to put triggers on
> > system tables would allow me to (using plpgsql)
>
> AFAIK many/most places that want to modify system tables don't
> do normal queries to do so and as such probably won't do rule
> rewriting or trigger invokations. Part of this is probably a
> performance reason, part is probably the dangerousness of it
> which would tend to make it probably a lower priority for anyone
> to consider looking at.
Part of it is permissions. A normal user does not (and should
not) have a right to modify system catalogs through regular
queries, but CREATE TABLE has to modify them. The direct heap
access inside the utility function is the permission override
mechanism here, and that does not invoke triggers or any rule
rewriting.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-05-28 14:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-27 22:46 Triggers and System Tables. Stef Telford <stef@chronozon.artofdns.com>
2002-05-28 05:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-05-28 05:35 Re: Triggers and System Tables. Stef Telford <stef@chronozon.artofdns.com>
2002-05-28 06:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-05-28 14:35 ` Jan Wieck <janwieck@yahoo.com>
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