Re: New Privilege model purposal

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

> 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
> > 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 done inside
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(at)Yahoo(dot)com #