Re: New Privilege model purposal

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