Thread: Suggestion GRANT ALTER, TRIGGER ON ALTER

Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Josh Berkus
Date:
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


Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Tom Lane
Date:
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


Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Josh Berkus
Date:
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



Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Tom Lane
Date:
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


Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Josh Berkus
Date:
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



Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)

From
Tom Lane
Date:
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


Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)

From
Josh Berkus
Date:
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



Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)

From
Tom Lane
Date:
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


Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON

From
"scott.marlowe"
Date:
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.



Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON

From
Alvaro Herrera
Date:
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)


Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
"Christopher Kings-Lynne"
Date:
> 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



Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)

From
"Christopher Kings-Lynne"
Date:
> 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



Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)

From
Tom Lane
Date:
"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


Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON

From
"scott.marlowe"
Date:
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.



Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Josh Berkus
Date:
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


Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

From
Peter Eisentraut
Date:
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



Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)

From
Bruce Momjian
Date:
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