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

At 15:27 25/07/00 +0200, Jan Wieck wrote:
>
>    Object Privileges
>
>        Object  Privileges  can  be  GRANTed  or  REVOKEed  to  a
>        particular user or group. The possible Privileges are:

This sounds great, and you may want to consider extending it to the COLUMN
level in tables & views.


>
...
>
>        LOCK                Permission  to  exclusively  lock the
>                            named relation.

This one worries me a little. I think I can see where you are coming from,
but you might be better off defining it as the ability to 'use the LOCK
statement to lock the object exclusively'. The reason I say this is that a
person altering a table's metadata and/or name, may well need an exclusive
lock, and it seems cumbersome to have to grant two privileges.

...etc...

You may also want to consider:
        SHOW                Permission to view the definition of the named
object.                            (this is from Dec/Rdb)
        RENAME              Permission to rename the named relation                             (gets past my objection
above,but probably                             best left as part of ALTER)
 
        INHERIT             Do you need this?
        DBA/OPERATOR/ADMIN  Permission to access the database when it is
'closed'                            (Dec/Rdb call it DBADMIN, I think)

I know we don't have the concept of a 'closed' database yet, but it is very
useful for performing tasks like renaming storage files, restoring backups
etc etc. The idea being that a DBA can close a database, then only DBA
users can connect to it.


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


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

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.


>
>        CREATE OBJECT
>        ALTER ANY OBJECT
>        DROP ANY OBJECT

Back to my previous comments - these seem to be more proerly defined as
'defaults' at the database level, but perhaps I am missing something.


>
>        System catalog changes
>
>            Pg_proc is extended by two new bool fields. Prosetuid
>            and procheckperm.  These two  and  the  proowner  are
>            held in the fmgr_info struct.
>
>            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.


>        Related details
>
>            The system will manage a  stack,  remembering  nested
>            states  of  the  effective user id. Calls through the
>            function manager can  switch  for-  and  backward  to
>            another  one, so prosetuid functions will inherit the
>            effective  permissions  of  the  function   (trigger)
>            owner.  The  stack  is  reinitialized  at transaction
>            aborts.

I assume this means that if function f1 running under uid 'fred' calls
function f2 (with no uid specified), then f2 will also still run as 'fred'?


>            For special purposes, there will be another  function
>            pg_check_realperms()  checking  against the real user
>            id allways (don't know what it'll be good for, but in
>            case...).

We'll also need to implement another kind of CURRENT_USER (I *think* SQL
defines one). You need to get the 'real' user as well as the 'effective'
user from within SQL.


I hope this is helpful, and I really look forward to this being implemented!


----------------------------------------------------------------
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   |/