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

Tue, 20 May 2003 10:07:33 -0700
Josh Berkus <josh@agliodbs.com>
Suggestion GRANT ALTER, TRIGGER ON ALTER
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

Tue, 20 May 2003 14:04:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER 
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 Berkus
 Aglio Database Solutions
 San Francisco


Tue, 20 May 2003 11:11:06 -0700
Josh Berkus <josh@agliodbs.com>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER
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

Tue, 20 May 2003 14:16:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER 
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 Berkus
 Aglio Database Solutions
 San Francisco


Tue, 20 May 2003 11:19:36 -0700
Josh Berkus <josh@agliodbs.com>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER
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

Tue, 20 May 2003 14:28:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)
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 Berkus
 Aglio Database Solutions
 San Francisco


Tue, 20 May 2003 11:39:03 -0700
Josh Berkus <josh@agliodbs.com>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)
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

Tue, 20 May 2003 15:05:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER) 
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.


Tue, 20 May 2003 14:42:21 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON
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)

Tue, 20 May 2003 19:23:35 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON
> 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


Wed, 21 May 2003 09:51:12 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER
> 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


Wed, 21 May 2003 09:53:04 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)
"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

Wed, 21 May 2003 00:23:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER) 
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.


Wed, 21 May 2003 09:08:11 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON
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

Wed, 21 May 2003 09:05:25 -0700
Josh Berkus <josh@agliodbs.com>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER
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


Sat, 24 May 2003 00:34:37 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Suggestion GRANT ALTER, TRIGGER ON ALTER

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, Pennsylvania 19073

Mon, 2 Jun 2003 14:51:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)
Bruce,

> * 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.

This seems very reasonable to me.  David?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 2 Jun 2003 11:53:12 -0700
Josh Berkus <josh@agliodbs.com>
Re: Logging (was Re: Suggestion GRANT ALTER, TRIGGER ON ALTER)