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 tableThese 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
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 Berkus
Aglio Database Solutions
San 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 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
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
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
DDLHm, 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
DDLHm, 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
DDLHm, 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, Pennsylvania 19073Bruce,
* Allow logging of only data definition(DDL), or DDL and modification
statementsI 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