Skip site navigation (1) Skip section navigation (2)

Re: New Privilege model purposal

From: JanWieck(at)t-online(dot)de (Jan Wieck)
To: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: PostgreSQL HACKERS <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: New Privilege model purposal
Date: 2000-07-25 18:41:53
Message-ID: 200007251841.UAA21038@hot.jw.home (view raw, whole thread or download thread mbox)
Lists: 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 rights to  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
> >        LOCK ANY TABLE
> 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.

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

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

> >        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 done inside
    of  that  uses  the  new euid until a (however deeply nested)
    call to another setuid function happens.



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

In response to


pgsql-hackers by date

Next:From: Jan WieckDate: 2000-07-25 18:57:49
Subject: Re: Re: Problem with disabling triggers in pg_dump
Previous:From: Stephan SzaboDate: 2000-07-25 18:16:13
Subject: Re: Re: Problem with disabling triggers in pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group