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

Re: New Privilege model purposal

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: PostgreSQL HACKERS <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: New Privilege model purposal
Date: 2000-07-25 23:54:32
Message-ID: (view raw or flat)
Lists: 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

>> >        CREATE TABLE
>> >        ALTER ANY TABLE
>> >        DROP ANY TABLE
>> >        INSERT ANY TABLE
>> >        UPDATE ANY TABLE
>> >        DELETE ANY TABLE
>> >        SELECT 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.

Maybe I was confusing implementation with interface, but what I was
suggesting (in a confused sort of way) was that you could define 'objects':

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 Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from   |/

In response to

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2000-07-26 00:17:20
Subject: RE: DELETE/DROP on Columns
Previous:From: Don BaccusDate: 2000-07-25 22:54:03
Subject: Re: Inprise InterBase(R) 6.0 Now Free and Open Source

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