Truncate Triggers
(for 8.4 ...)
I'd like to introduce triggers that fire when we issue a truncate:
CREATE TRIGGER name [BEFORE | AFTER ] TRUNCATE ON table
FOR EACH STATEMENT EXECUTE PROCEDURE function (arguments);
The truncate trigger would fire separately from a statement-level DELETE
statement, to allow us to distinguish those two events. There would be
nothing to stop both triggers executing the same function however, if
desired.
COPY already provides a model for how a utility command can execute
triggers. tablecmds.c would invoke ExecBSTruncateTriggers() and
ExecASTruncateTriggers(), both of which would live in triggers.c
There doesn't seem much too difficult about the implementation or
behaviour, but I want to make sure we have the discussion at least, to
see if anybody has concerns or additional requirements.
Notes: As the syntax shows, these would be statement-level triggers
(only). Requesting row level triggers will cause an error. [As Chris
Browne explained, if people really want, they can use these facilities
to create a Before Statement trigger that executes a DELETE, which then
fires row level calls.]
I also plan to add a TRUNCATE privilege, though that will be a separate
patch in a later post. That will widen the use case of TRUNCATE, which
should be OK to do once we've covered the replication concerns.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
Notes: As the syntax shows, these would be statement-level triggers
(only). Requesting row level triggers will cause an error. [As Chris
Browne explained, if people really want, they can use these facilities
to create a Before Statement trigger that executes a DELETE, which then
fires row level calls.]
Is there a way for a BS trigger to return a flag "skip the statement",
as there is for BR?
I also plan to add a TRUNCATE privilege, though that will be a separate
patch in a later post. That will widen the use case of TRUNCATE, which
should be OK to do once we've covered the replication concerns.
Considering it's not MVCC-safe, do we *want* to widen the use case?
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.
regards, tom lane
On Fri, Jan 25, 2008 at 11:40:19AM +0000, Simon Riggs wrote:
(for 8.4 ...)
I'd like to introduce triggers that fire when we issue a truncate:
Rather than focusing exclusively on TRUNCATE, how about "triggers" that
fire whenever any kind of DDL operation is performed? (Ok, truncate is
more DML than DDL, but still).
The reason I put triggers in quotes is because I'm not suggesting that
we actually put triggers on the catalog tables, since we all know that's
hard/impossible. Instead this would have to tie into command processing,
similar to what you're proposing for truncate.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
Notes: As the syntax shows, these would be statement-level triggers
(only). Requesting row level triggers will cause an error. [As Chris
Browne explained, if people really want, they can use these facilities
to create a Before Statement trigger that executes a DELETE, which then
fires row level calls.]Is there a way for a BS trigger to return a flag "skip the statement",
as there is for BR?
We can alter the API for triggers to do that. Or are you thinking of
having the Truncate Trigger API be different?
I also plan to add a TRUNCATE privilege, though that will be a separate
patch in a later post. That will widen the use case of TRUNCATE, which
should be OK to do once we've covered the replication concerns.Considering it's not MVCC-safe, do we *want* to widen the use case?
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.
People use TRUNCATE whatever we say. If you force people to be table
owners or superusers you merely restrict their security options.
If you want to prevent wider use then perhaps a better explanation of
what "MVCC-safe" means in the docs would do that. Most people don't
understand that phrase, or its implications.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.
People use TRUNCATE whatever we say. If you force people to be table
owners or superusers you merely restrict their security options.
By that argument you could justify a separate privilege bit for anything
at all, eg, each sub-variant of ALTER TABLE. Please present an actual
argument why TRUNCATE should get its own bit.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.People use TRUNCATE whatever we say. If you force people to be table
owners or superusers you merely restrict their security options.By that argument you could justify a separate privilege bit for anything
at all, eg, each sub-variant of ALTER TABLE. Please present an actual
argument why TRUNCATE should get its own bit.
I've done this already, and continue to feel that TRUNCATE should have
its own bit. There are many cases where you want a user to be able to
truncate a table but not alter its structure. TRUNCATE is not a
DDL-type statement, those can and should be reserved to the owner.
Thanks,
Stephen
On Fri, 2008-01-25 at 14:00 -0500, Tom Lane wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.People use TRUNCATE whatever we say. If you force people to be table
owners or superusers you merely restrict their security options.By that argument you could justify a separate privilege bit for anything
at all, eg, each sub-variant of ALTER TABLE.
I already made the argument that TRUNCATE is not similar to DDL in its
effects or usage.
http://archives.postgresql.org/pgsql-sql/2008-01/msg00093.php
Please present an actual
argument why TRUNCATE should get its own bit.
Claiming my argument doesn't exist is itself a fairly weak defence...
I only mentioned this for completeness, since its on the TODO list. You
should probably go through the TODO and remove the items you disagree
with. I didn't put it there, nor did I originally propose it. I do think
it has merit; I came up against exactly that issue earlier this month.
Perhaps we should be implementing "extended privileges" by using one
additional bit to mean "has extended privilege list". We presumably want
to implement column level privileges, plus you raise interesting
thoughts about fine grained security access controls for certain
operations, so an extension mechanism seems like the way to go.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
On Fri, 2008-01-25 at 14:00 -0500, Tom Lane wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.People use TRUNCATE whatever we say. If you force people to be table
owners or superusers you merely restrict their security options.By that argument you could justify a separate privilege bit for anything
at all, eg, each sub-variant of ALTER TABLE.I already made the argument that TRUNCATE is not similar to DDL in its
effects or usage.
http://archives.postgresql.org/pgsql-sql/2008-01/msg00093.phpPlease present an actual
argument why TRUNCATE should get its own bit.Claiming my argument doesn't exist is itself a fairly weak defence...
Uhm, your argument seems to consist of:
| 1. Commands that alter the rows in the table
| e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group
I don't think saying "clearly" constitutes an argument.
The fact is that TRUNCATE is a big of a screw case. It can be seen either way.
A lot of users use it as a "cheaper form of delete" which would make it DML.
Except the whole reason it's cheaper is precisely because of the way it works
which is by being a closer analogy to a DROP and CREATE sequence of DDL.
This two-faced personality is just why we're facing this problem. It looks to
users like DML but it under the hood it behaves just like DDL.
I think there are two strategies here. Either we paper over the DDLishness by
making it look as much as possible like DML. It'll never be perfect but we'll
be as friendly as we can for users. That might just make the DDLishness pop up
at all the more surprising moments and be all that much harder to explain
though.
Or we can just declare it DDL and put a warning in the documentation that
while it may sound like it's DML it is in fact better thought of as a shortcut
for doing DROP and CREATE and should be used as such.
On the other hand perhaps it would be nice to have per-statement DDL triggers
in general. So you could, for example, update a data dictionary automatically
for simple cases like DROP TABLE.
Per-statement DDL triggers might also provide a more extensible way to enforce
unusual security policies. "Nobody's allowed to do any DDL except during a
maintenance window" for example.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
On Jan 26, 2008 8:14 AM, Gregory Stark <stark@enterprisedb.com> wrote:
This two-faced personality is just why we're facing this problem. It looks to
users like DML but it under the hood it behaves just like DDL.
Agreed that it looks like DML. Speaking as a user, I came away from
the documentation thinking that "TRUNCATE foo;" was just a quicker (to
type as well as to run) alternative to "DELETE * FROM foo;". Plus it
has the handy quality of being able to empty multiple tables in the
one command.
I think there are two strategies here. Either we paper over the DDLishness by
making it look as much as possible like DML. It'll never be perfect but we'll
be as friendly as we can for users. That might just make the DDLishness pop up
at all the more surprising moments and be all that much harder to explain
though.Or we can just declare it DDL and put a warning in the documentation that
while it may sound like it's DML it is in fact better thought of as a shortcut
for doing DROP and CREATE and should be used as such.
It's my humble opinion that the analogy to DROP + CREATE does need to
be made more clear.
Cheers
BJ
* Simon Riggs (simon@2ndquadrant.com) wrote:
Perhaps we should be implementing "extended privileges" by using one
additional bit to mean "has extended privilege list". We presumably want
to implement column level privileges, plus you raise interesting
thoughts about fine grained security access controls for certain
operations, so an extension mechanism seems like the way to go.
I disagree and feel that my now rather ancient proposal is still better-
split the "permission" bits and the "grantable" bits into two seperate
32bit integers. That has the advantage of doubleing the number of
available bits while also splitting up the frequently used bits
("permission" bits) from the much, much, much less frequently used bits
("grantable" bits).
Of course, the last time this went around the argument was that we
shouldn't add alot of extra code until we actually needed to, while at
the same time we shouldn't use up the few remaining bits we have. The
fact that this makes for an impossible situation seems to have been
lost.
Thanks,
Stephen
On Friday 25 January 2008 06:40, Simon Riggs wrote:
Notes: As the syntax shows, these would be statement-level triggers
(only). Requesting row level triggers will cause an error. [As Chris
Browne explained, if people really want, they can use these facilities
to create a Before Statement trigger that executes a DELETE, which then
fires row level calls.]
This seems to completly hand-wave away the idea of implementing row level
visibility in statement level triggers, something I am hoping to see
implemented somewhere down the line. Am I missing something?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
"Robert Treat" <xzilla@users.sourceforge.net> writes:
the idea of implementing row level visibility in statement level triggers
Huh?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
Robert Treat wrote:
On Friday 25 January 2008 06:40, Simon Riggs wrote:
Notes: As the syntax shows, these would be statement-level triggers
(only). Requesting row level triggers will cause an error. [As Chris
Browne explained, if people really want, they can use these facilities
to create a Before Statement trigger that executes a DELETE, which then
fires row level calls.]This seems to completly hand-wave away the idea of implementing row level
visibility in statement level triggers, something I am hoping to see
implemented somewhere down the line. Am I missing something?
The rowset (not row) associated with the statement level trigger would
be the whole table in case of a TRUNCATE trigger, so in this (corner)
case it's not too helpful.
Regards,
Andreas
On Sat, Jan 26, 2008 at 04:33:53PM +0000, Gregory Stark wrote:
"Robert Treat" <xzilla@users.sourceforge.net> writes:
the idea of implementing row level visibility in statement level triggers
Huh?
I think he means that statement level triggers can see the rows that
got affected, presumably by NEW representing a tuplestore. How that
would work for BEFORE STATEMENT triggers I don't know.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy
Robert Treat <xzilla@users.sourceforge.net> writes:
This seems to completly hand-wave away the idea of implementing row level
visibility in statement level triggers, something I am hoping to see
implemented somewhere down the line. Am I missing something?
That was discussed already --- we agreed that TRUNCATE triggers would
simply omit that functionality, when and if it's implemented for other
trigger types.
regards, tom lane
Stephen Frost <sfrost@snowman.net> writes:
Of course, the last time this went around the argument was that we
shouldn't add alot of extra code until we actually needed to, while at
the same time we shouldn't use up the few remaining bits we have. The
fact that this makes for an impossible situation seems to have been
lost.
No, it hasn't been forgotten at all. Whenever we have to cross that
bridge, we'll do so. The questions being asked here are about whether
an adequate case has been made for adding *user-visible* complexity,
not about nitty little details of internal representation.
There are also some compatibility concerns involved. If we add
grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL
ON TABLE suddenly conveys a whole lot more privilege than it did before.
This could lead to unpleasant surprises in security-sensitive
operations. One could also put forward the argument that it's a direct
violation of the SQL spec, which after all does specify exactly what
privileges ALL is supposed to grant.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
There are also some compatibility concerns involved. If we add
grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL
ON TABLE suddenly conveys a whole lot more privilege than it did before.
This could lead to unpleasant surprises in security-sensitive
operations. One could also put forward the argument that it's a direct
violation of the SQL spec, which after all does specify exactly what
privileges ALL is supposed to grant.
iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT
ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL
operation permissions explicitly would be reasonable. This might create
a disconnect with what 'revoke all' does, since that should really
remove all of the perms, but I feel that's reasonable. A 'Default
secure' approach.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT
ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL
operation permissions explicitly would be reasonable. This might create
a disconnect with what 'revoke all' does, since that should really
remove all of the perms, but I feel that's reasonable. A 'Default
secure' approach.
More like "default impossibly confusing" :-(. "GRANT ALL" doesn't mean
grant all privileges? How the heck are you going to explain/justify
that to a newbie?
regards, tom lane
On Sat, 2008-01-26 at 11:19 -0500, Robert Treat wrote:
On Friday 25 January 2008 06:40, Simon Riggs wrote:
Notes: As the syntax shows, these would be statement-level triggers
(only). Requesting row level triggers will cause an error. [As Chris
Browne explained, if people really want, they can use these facilities
to create a Before Statement trigger that executes a DELETE, which then
fires row level calls.]This seems to completly hand-wave away the idea of implementing row level
visibility in statement level triggers, something I am hoping to see
implemented somewhere down the line. Am I missing something?
Not sure why you say that.
We have a choice:
i) TRUNCATE never has access to rows
ii) TRUNCATE can have access, in which case it acts like a DELETE
Forcing ii) in all cases would effectively negate truncate triggers, so
we must have some way of providing both alternatives as options.
As Chris explained, if we allow a BEFORE STATEMENT trigger on TRUNCATE
to issue a DELETE instead, then we are OK to just allow i) and yet
retain the ability to access rows for those that want it. There may be
another of way of doing this also, but I'll leave that possibility to
whoever tries to implement the feature you mention in the future.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT
ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL
operation permissions explicitly would be reasonable. This might create
a disconnect with what 'revoke all' does, since that should really
remove all of the perms, but I feel that's reasonable. A 'Default
secure' approach.More like "default impossibly confusing" :-(. "GRANT ALL" doesn't mean
grant all privileges? How the heck are you going to explain/justify
that to a newbie?
"grant all" *already* doesn't mean grant all privileges. This isn't
really a change from that. Additionally, there's lots of places where
we follow the SQL spec because that's the right thing to do even though
it's not always the most intuitive thing to do. I certainly don't feel
this is 'impossibly confusing' any more than 'grant all' doesn't mean
you can truncate or alter the table today.
Thanks,
Stephen
