Thread: Suggestion GRANT ALTER, TRIGGER ON ALTER
Pgsql developers:
Based on a question posed me by David Fetter of Bricolage, I realized that we
have what looks to me to be a serious inconsistency in our permissions model.
Please ignore me if this has already been proposed and acted on.
SELECT, UPDATE, DELETE, RULE, TRIGGER can all be GRANTed. However, ALTER /
DROP cannot be granted ... they belong only to the table owner and the
superuser, who then have no restrictions on what they can do with the table.
In a database system with many command-line users, it is quite possible that
an admin would want to GRANT some users the ability to ALTER some tables in
the public schema, without either DROPing them or granting permission on
*all* tables.
Therefore I propose the following two additional permissions on TABLEs, VIEWs,
and FUNCTIONs:
GRANT ALTER ON object TO user : allows the user to run ALTER or REPLACE
statements on the object;
GRANT DROP ON object TO user : allows the user to DROP the object (obviously a
permission that could only be used once).
Accompanying these should be an extension of triggers to allow logging, etc.,
of such activity. Namely:
CREATE TRIGGER tg_name {BEFORE|AFTER} ALTER ON table
CREATE TRIGGER tg_name BEFORE DROP ON table
CREATE
These would allow more sophisticated action to be taken on the execution of
DDL statements. DROP triggers would be BEFORE only, for obvious reasons.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Therefore I propose the following two additional permissions on
> TABLEs, VIEWs, and FUNCTIONs:
> GRANT ALTER ON object TO user : allows the user to run ALTER or REPLACE
> statements on the object;
> GRANT DROP ON object TO user : allows the user to DROP the object (obviously a
> permission that could only be used once).
ALTER permission seems reasonable, I'm less convinced that GRANT DROP is
really needed.
> Accompanying these should be an extension of triggers to allow logging, etc.,
> of such activity. Namely:
> CREATE TRIGGER tg_name {BEFORE|AFTER} ALTER ON table
> CREATE TRIGGER tg_name BEFORE DROP ON table
These I do not like. We do not run user triggers in the midst of
catalog operations because they might see inconsistent states of the
system catalogs. (Consider for instance the possibility that a table is
being dropped as part of a cascaded drop, and something it depends on is
already gone. What does the trigger see? Does it still work?)
regards, tom lane
Tom,
> > Therefore I propose the following two additional permissions on
> > TABLEs, VIEWs, and FUNCTIONs:
> > GRANT ALTER ON object TO user : allows the user to run ALTER or REPLACE
> > statements on the object;
> > GRANT DROP ON object TO user : allows the user to DROP the object
(obviously a
> > permission that could only be used once).
>
> ALTER permission seems reasonable, I'm less convinced that GRANT DROP is
> really needed.
No, I suppose not; what David really needs is GRANT ALTER; including GRANT
DROP just seemed consistent.
> > CREATE TRIGGER tg_name {BEFORE|AFTER} ALTER ON table
> > CREATE TRIGGER tg_name BEFORE DROP ON table
>
> These I do not like. We do not run user triggers in the midst of
> catalog operations because they might see inconsistent states of the
> system catalogs. (Consider for instance the possibility that a table is
> being dropped as part of a cascaded drop, and something it depends on is
> already gone. What does the trigger see? Does it still work?)
Hmmm .... yeah, that sounds non-trivial.
David is going to talk with Joe about doing some special logging for DDL
operations; if they come out of it with some clean code, would you consider a
patch that includes an new logging option for "log_ddl" ?
--
-Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus <josh@agliodbs.com> writes:
> David is going to talk with Joe about doing some special logging for DDL
> operations; if they come out of it with some clean code, would you
> consider a patch that includes an new logging option for "log_ddl" ?
No objection here. We saw a recent request for logging only
data-modifying statements, too (ie, everything but SELECTs).
Might be worth thinking about whether those two cases cover it,
or whether there needs to be some more-general way of choosing
which statements to log according to their type.
regards, tom lane
Tom, > No objection here. We saw a recent request for logging only > data-modifying statements, too (ie, everything but SELECTs). > Might be worth thinking about whether those two cases cover it, > or whether there needs to be some more-general way of choosing > which statements to log according to their type. Actually, I can see that ... what about an option like "log_statement" which took an array of text which would correspond to the first part of the statement? Then we could leave it up to the DBA do decide what they want to log, with the validation list being the base list of SQL statements, i.e.: log_statement = "CREATE TABLE, ALTER TABLE, CREATE VIEW, ALTER VIEW" -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Actually, I can see that ... what about an option like "log_statement" which
> took an array of text which would correspond to the first part of the
> statement? Then we could leave it up to the DBA do decide what they want to
> log, with the validation list being the base list of SQL statements, i.e.:
> log_statement = "CREATE TABLE, ALTER TABLE, CREATE VIEW, ALTER VIEW"
Strikes me as a tad unwieldy --- the useful cases would correspond to
very long log_statement lists, and in every new release the list would
change. It's probably better to have a very small number of categories,
something likeSELECTINSERT/UPDATE/DELETEall DDL
and be able to flip logging on/off per category. But we need to think
about exactly what the categories are.
A related point that I've been meaning to bring up is that I'm not sure
what sort of logging ought to happen in the new FE/BE protocol's
PARSE/BIND/EXECUTE universe. Right now, if you've got log_statement on,
the strings fed to PARSE get logged. But that's got precious little to
do with what gets executed when, if the client is actually exploiting
the opportunity to prepare statements in advance of execution. On the
other hand, I'm not sure we want three log entries for every command.
Any thoughts on this out there?
regards, tom lane
Tom, > Strikes me as a tad unwieldy --- the useful cases would correspond to > very long log_statement lists, and in every new release the list would > change. It's probably better to have a very small number of categories, > something like > SELECT > INSERT/UPDATE/DELETE > all DDL > and be able to flip logging on/off per category. But we need to think > about exactly what the categories are. I would propose SELECT INSERT/UPDATE DELETE DDL > the opportunity to prepare statements in advance of execution. On the > other hand, I'm not sure we want three log entries for every command. > Any thoughts on this out there? Sorry, I don't know enough about the new structure to have an opinion. -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus <josh@agliodbs.com> writes:
> I would propose
> SELECT
> INSERT/UPDATE
> DELETE
> DDL
Hm, why that particular division --- why separate DELETE but keep
INSERT and UPDATE together?
regards, tom lane
On Tue, 20 May 2003, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > > I would propose > > SELECT > > INSERT/UPDATE > > DELETE > > DDL > > Hm, why that particular division --- why separate DELETE but keep > INSERT and UPDATE together? Why not just use a regex? Then you could log exactly what you're looking for.
On Tue, May 20, 2003 at 02:42:21PM -0600, scott.marlowe wrote: > On Tue, 20 May 2003, Tom Lane wrote: > > > Josh Berkus <josh@agliodbs.com> writes: > > > I would propose > > > SELECT > > > INSERT/UPDATE > > > DELETE > > > DDL > > > > Hm, why that particular division --- why separate DELETE but keep > > INSERT and UPDATE together? > > Why not just use a regex? Then you could log exactly what you're looking > for. I'd think a bitstring or some such is better... with a regex you are sure going to drive the performance down. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El sudor es la mejor cura para un pensamiento enfermo" (Bardia)
> No, I suppose not; what David really needs is GRANT ALTER; including GRANT > DROP just seemed consistent. How is GRANT ALTER less powerful than GRANT DROP? You can just ALTER TABLE/DROP COLUMN all the columns in the table if you like, effectively dropping (or wrecking) the table. Chris
> Strikes me as a tad unwieldy --- the useful cases would correspond to > very long log_statement lists, and in every new release the list would > change. It's probably better to have a very small number of categories, > something like > SELECT > INSERT/UPDATE/DELETE > all DDL > and be able to flip logging on/off per category. But we need to think > about exactly what the categories are. How about 'log to table' like Oracle can (apparently) do. All sorts of problems I can think with it, but then at least people can just query it using normal SQL. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> How about 'log to table' like Oracle can (apparently) do. All sorts of
> problems I can think with it, but then at least people can just query it
> using normal SQL.
Right offhand I do not see how a failed transaction could make any
entries (that later xacts could see, that is) in such a table. And
surely error entries are precisely the most interesting ones in a log.
So you'd need to commit some major-league abuse of the transactional
and MVCC mechanisms to make this work usefully.
regards, tom lane
On Tue, 20 May 2003, Alvaro Herrera wrote: > On Tue, May 20, 2003 at 02:42:21PM -0600, scott.marlowe wrote: > > On Tue, 20 May 2003, Tom Lane wrote: > > > > > Josh Berkus <josh@agliodbs.com> writes: > > > > I would propose > > > > SELECT > > > > INSERT/UPDATE > > > > DELETE > > > > DDL > > > > > > Hm, why that particular division --- why separate DELETE but keep > > > INSERT and UPDATE together? > > > > Why not just use a regex? Then you could log exactly what you're looking > > for. > > I'd think a bitstring or some such is better... with a regex you are > sure going to drive the performance down. Sure, that's fine too. I'd just prefer some way to "wild card" what is being logged. I.e. I can look for specific SQL code and log just that. Logging all selects doesn't really gain me much over grepping the log files, as there will still be tons of selects I'm not interested in on a production system. Logging queries that contain specific keywords (i.e. table name, field name things like that) represents a much more useful tool to me.
Chris, > > No, I suppose not; what David really needs is GRANT ALTER; including > > GRANT DROP just seemed consistent. > > How is GRANT ALTER less powerful than GRANT DROP? You can just ALTER > TABLE/DROP COLUMN all the columns in the table if you like, effectively > dropping (or wrecking) the table. Another good point. Makes sense to just do GRANT ALTER then, which would cover DROP as well. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus writes: > Another good point. Makes sense to just do GRANT ALTER then, which would > cover DROP as well. If you have the privilege to alter an object you are nearly equivalent to the owner of the object. A more useful approach might be group ownership of objects. -- Peter Eisentraut peter_e@gmx.net
Seems this would be the easiest way: * Allow logging of only data definition(DDL), or DDL and modification statements I can't see why someone would want to see only SELECT and not others, and I can't imagine wanting modification statements and not DDL. Added to TODO. --------------------------------------------------------------------------- Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > > Actually, I can see that ... what about an option like "log_statement" which > > took an array of text which would correspond to the first part of the > > statement? Then we could leave it up to the DBA do decide what they want to > > log, with the validation list being the base list of SQL statements, i.e.: > > > log_statement = "CREATE TABLE, ALTER TABLE, CREATE VIEW, ALTER VIEW" > > Strikes me as a tad unwieldy --- the useful cases would correspond to > very long log_statement lists, and in every new release the list would > change. It's probably better to have a very small number of categories, > something like > SELECT > INSERT/UPDATE/DELETE > all DDL > and be able to flip logging on/off per category. But we need to think > about exactly what the categories are. > > A related point that I've been meaning to bring up is that I'm not sure > what sort of logging ought to happen in the new FE/BE protocol's > PARSE/BIND/EXECUTE universe. Right now, if you've got log_statement on, > the strings fed to PARSE get logged. But that's got precious little to > do with what gets executed when, if the client is actually exploiting > the opportunity to prepare statements in advance of execution. On the > other hand, I'm not sure we want three log entries for every command. > Any thoughts on this out there? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073