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

Philip Warner wrote:
> 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.
   So  someone  could  prevent  a user from overriding a DEFAULT   column value by revoking the INSERT and UPDATE
rightsto  the   column. Good idea.
 

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

> You may also want to consider:
>
>          SHOW                Permission to view the definition of the named
> object.
>                              (this is from Dec/Rdb)
   As you say it, I see it.  The default for all system catalogs   would be, that a normal user cannot SELECT from
them. So  we   would  need some kind of DESCRIBE command since psql wouldn't   be able to grab these definitions any
more.
   I see, this project is becoming bigger the more people really   think about the side effects.

>          RENAME              Permission to rename the named relation
>                              (gets past my objection above, but probably
>                              best left as part of ALTER)
   Let's leave it as part of ALTER TABLE.

>          INHERIT             Do you need this?
   What other rights must a user have on the inherited relations   to work properly with them?

>
>          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.
   I like that. And the concept of 'closed' databases should  be   added to this project.

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

> >        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
owntables,   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
oncein a while.
 

> 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
groupsaccess   to it. But a user with SELECT ANY TABLE can do  so  from  the   start,  because  the  DBA decided that
thisuser act's like a   superuser if issuing some SELECT database wide.
 

> >        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.
   These aren't separate privileges. CREATE OBJECT is  the  same   as CREATE TABLE and CREATE VIEW and CREATE FUNCTION
and...
 

> >        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.
   You never walk alone.

> >        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'?
   Right.  The  euid switching will only be done at entering and   leaving a prosetuid function or trigger. Anything
doneinside   of  that  uses  the  new euid until a (however deeply nested)   call to another setuid function happens.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #