Re: New Privilege model purposal - Mailing list pgsql-hackers

At 20:41 25/07/00 +0200, Jan Wieck wrote:
>Philip Warner wrote:
>> At 15:27 25/07/00 +0200, Jan Wieck wrote:
>>
>
>>          INHERIT             Do you need this?
>
>    What other rights must a user have on the inherited relations
>    to work properly with them?
>

I have no idea, I only suggested this because it's a feature that is easily
overlooked, and not part of most DBs, so we may need to thin about it...

The SQL standard also has a 'GRANT...WITH HIERARCHY' option that grants
access on all subtables.

FWIW, the SQL standard also defines a 'USAGE' priv that grants access to
domains, character sets, UDTs etc.


>> >    System Privileges
>> >
>> >        System Privileges are to grant permission to execute DDL-
>> >        statements or for database wide Object permissions (valid
>> >        for all objects of a particular kind).
>> >
>> >        SUPERUSER           A    special    System     Privilege,
>> >                            superseding  any  other  rights. What
>> >                            the holder of this  right  want's  to
>> >                            do,  he  does. It is the same as now,
>> >                            usesuper in pg_shadow.
>>
>> I suspect this is good grounds for a religious war, but I like a priv
>> system where I have to 'turn on' a super privilege before I get it. If I am
>> a superuser, I don't want my cape flapping in the breeze *all* the time.
>> Can you add some kind of 'CLARK_KENT' priv (ie. 'can become superuser')?
>> And have SUPERUSER off at the beginning of all sessions?
>>
>> There are two reasons I think this is important: 1) I am accident prone,
>> and 2) it's good to live like a mortal most of the time - you get to see
>> problems before a user complains.
>
>    If you don't need DBA privileges, don't log on as a DBA. Have
>    a separate account for that (IMHO).

I guess if there is an 'ALTER SESSION SET AUTHORIZATION <superuser-name>'
or alternatively, 'SET ROLE <rolename>', then I'll be happy, since I could
define a 'superuser/dba' role.

I think there is a need for one or more users to have superuser-like access
to a single DB, but have little or no access to other ones. The suggestion
above would allow a normal user to be superuser for single database,
without having to set up (potentially) a separate DBA account for each
database.


>> >        CREATE TABLE
>> >        ALTER ANY TABLE
>> >        DROP ANY TABLE
>> >        INSERT ANY TABLE
>> >        UPDATE ANY TABLE
>> >        DELETE ANY TABLE
>> >        SELECT ANY TABLE
>> >        LOCK ANY TABLE
>> >        REFERENCE ANY TABLE
>> >        CREATE SEQUENCE
>> >        ALTER ANY SEQUENCE
>> >        DROP ANY SEQUENCE
>>
>> This seems like overkill; you will need a new priv for every object type.
>> It is also not clear how 'ALTER ANY TABLE' should interact with 'ALTER
>> TABLE (specific table)', but I assume the more specific priv rules.
>
>    As I said, it should be fine grained. If  a  DBA  wants  some
>    user  to  be  able  to  create views, but not his own tables,
>    functions  etc.,  how  could  he  if  there  aren't  separate
>    privileges for the single actions?
>
>    The  interactions  will  be  hardwired in the pg_check_priv()
>    function.  Since  the  requested  privilege  is  a  #define'd
>    constant,  it'll  be  more  or  less  a big switch statement,
>    calling a single privilege lookup helper once in a while.

Maybe I was confusing implementation with interface, but what I was
suggesting (in a confused sort of way) was that you could define 'objects':
TABLE, TRIGGER, SEQUENCE, COLUMN..., and 'privileges': ALTER, DROP, CREATE,
UPDATE, DELETE, SELECT etc etc.

Then privs can be granted on objects, so the number of #defines only equals
the number of separate privs, not the number of privs times the number of
object types. Maybe I just misunderstood your plans? Did you mean that
'LOCK ANY TABLE' would be a priv granted at the database level, schema
level, or really at the system level?


>> It seems that this is just a way of defining 'default' privs for an object
>> that does not have an ACL, and if that is the case, why not define a
>> default protection at both the database level and the object-type level
>> (perhaps in the relevant pg_* table?). Certainly it seems that 'CREATE
>> TABLE' could be represented as 'INSERT' priv on the pg_class table etc.
>
>    No. They are meant as user or group specific privileges.
>
>    By default, only the owner has access to his tables. He (or a
>    superuser) must explicitly GRANT other users or groups access
>    to it. But a user with SELECT ANY TABLE can do  so  from  the
>    start,  because  the  DBA decided that this user act's like a
>    superuser if issuing some SELECT database wide.

So to use the above object/priv model, 'GRANT "CREATE ANY TABLE" on
database <dbname> to fred' might be equivalent to 'GRANT "INSERT" on OBJECT
TABLES to fred'.

I'm not particularly attached to my suggestion, but you can achieve
granularity without lots of priv named to remember.


>> >
>> >            If  a  function  is called through the fmgr (any user
>> >            defined function is), the  function  manager  honours
>> >            these   flags.  Prosetuid  will  cause  the  function
>> >            manager to switch to another effective user id,  used
>> >            during  pg_check_perms() for the time of the function
>> >            invocation.
>>
>> Wonderful! I've been hoping for this for a while.
>
>    You never walk alone.

That's what I tell my dog.


Bye for now,

Philip.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/