FAQ
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

Search Discussions

  • Tom Lane at May 20, 2003 at 6:04 pm

    Josh Berkus 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
  • Josh Berkus at May 20, 2003 at 6:11 pm
    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
  • Tom Lane at May 20, 2003 at 6:17 pm

    Josh Berkus 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
  • Josh Berkus at May 20, 2003 at 6:20 pm
    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
  • Tom Lane at May 20, 2003 at 6:28 pm

    Josh Berkus 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
  • Josh Berkus at May 20, 2003 at 6:39 pm
    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
  • Tom Lane at May 20, 2003 at 7:06 pm

    Josh Berkus 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
  • Scott.marlowe at May 20, 2003 at 8:56 pm

    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.
  • Alvaro Herrera at May 20, 2003 at 11:28 pm

    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)
  • Scott.marlowe at May 21, 2003 at 3:50 pm

    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.
  • Christopher Kings-Lynne at May 21, 2003 at 1:53 am

    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
  • Tom Lane at May 21, 2003 at 4:30 am

    "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
  • Bruce Momjian at Jun 2, 2003 at 6:51 pm
    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
  • Josh Berkus at Jun 2, 2003 at 6:54 pm
    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
  • Christopher Kings-Lynne at May 21, 2003 at 1:51 am

    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
  • Josh Berkus at May 21, 2003 at 4:31 pm
    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
  • Peter Eisentraut at May 24, 2003 at 1:23 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 20, '03 at 5:08p
activeJun 2, '03 at 6:54p
posts18
users7
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase