Some of you may remember some mumblings about some work on access
privileges, so this is the idea. Comments welcome.


* Goals

The goal of the first stage is to provide a fully SQL92 compliant
solution. That comprises particularly column level granularity, the
REFERENCES[*] privilege for use by constraints, and the USAGE
privilege. We currently don't have any of the things that USAGE
pertains to -- domains, character sets, collations, translations --
but at least we shouldn't have to start all over when we do. Also
GRANT OPTION needs to be supported.

[*] -- now with that RI snafu unveiled that goal seems optimistic

The second stage would be adopting all specifications made by SQL3 if
they are applicable. This includes particularly the privilege types
TRIGGER and UNDER (for table inheritance, which should probably work
well first). Also we could think about EXECUTE for functions and some
`setuid'-like features.

Stage two isn't necessarily anticipated for 7.1 but I'd like to have a
framework which adapts well.

* User management

One thing I'd like to see resolved first is the issue of
pg_shadow.usesysid. This field is fully functionally dependent on
pg_shadow.oid so there's little theoretical need to keep it around.
Secondly, the system happily reassigns previously used sysids, which
is a pretty dangerous thing to do as we all know, since there might
still be old database objects hanging around that the new users
shouldn't necessarily have access to. (And connecting to all databases
in turn to remove any dangling objects when a user is dropped isn't
really an option.) So the answer is to not recycle sysids. But then
why not use the oid?

Some arguments for user sysids I have heard in the past were that some
people want to keep them the same as the Unix uid. While I'm at a loss
as to how this would matter in practice (aren't names enough) I grant
that that's an argument (albeit one that doesn't scale well because
not every database user is a Unix user and two identically numbered
Unix users from different machines would presumably map to different
database users). But if you look closer then this thinking is
primarily caused by the fact that there is a usesysid field at all --
if there wasn't, you wouldn't have to keep it in sync.

Another reason why an oid based arrangement would be nicer is that if
we did the same thing for groups why could refer to both users and
groups through one attribute. See `Implementation' below.

* Implementation

The central idea in this proposal is a new system table to store
permissions:

pg_privilege (
    priobj oid,
    prigrantor oid,
    prigrantee oid,
    priaction char,
    priisgrantable boolean,

    primary key (priobj, prigrantee, priaction)
)                                                                                 

"priobj" would store the oid of the object being described, for
example a table or function or type. "prigrantor" is the user who
granted the privilege. (It is necessary to store this to get grant
options to work correctly.) "prigrantee" is obviously the user to
which you grant the privilege or a group. We could put 0 for "public".
"priaction" would be the encoding of the privilege type, such as
's'=select, 'u'=update, perhaps. And "prigrantable" is whether the
privilege is grantable.

The key advantages to this method over the old one are:
- Extensible beyond tables, in fact to any kind of object
- Easier to query, e.g., for what-if inquiries
- The old method would make grant options pretty tough without a major
  rework
- A pg_privilege row would be almost exactly what SQL calls a
  "privilege descriptor". So the implementation will be much easier
  and verifyable because you can read the program code out of the
  standard text. (in theory anyway)

Those that follow will see how simple-minded grant, revoke, and
privilege lookup will be in their core: simply insert, delete, or look
for a row. (Of course the devil is in the details.)

* Column privileges

There are two approaches I see to managing column privileges, one is a
little cleaner, the other faster. Note that granting access to a
table is different than granting access to all of its columns; the
difference is what happens when you add a new column.

The straightforward choice would be to store a single reference to
pg_class when the privilege describes the whole table, and
pg_attribute references when only specific columns are named. That
would mean the lookup routine will first look for a pg_class.oid entry
and, failing that, then for possible pg_attribute.oid entries for the
columns that it's interested in. This is of course suboptimal when no
privilege exists in the first place but that is not necessarily the case
we're optimizing for.

The second choice would be to always have an entry for the table, even
if it only says "I'm not the real privilege, but there are column
privileges, so you better keep looking." That would probably mean
another column in the pg_privilege. This way you have to maintain
redundant information but there is enough precedent for this sort of
thing in the other system catalogs.

* Groups

Handling groups efficiently is a bit tricky because it's essentially
equivalent to a join: scan all the privileges and all the groups and
look for matches between them and with the current user id. I suppose
one could simply run this query by hand once and see what the
optimizer thinks would be a good way to run it, but that isn't
facilitated by the way group information is stored right now.

I would do it like this: Looking up privileges granted to groups would
be done if the lookup based on the user id fails. Then you have to
scan pg_group anyway, so you might as well just scan it once
completely and record all the groups the user is in. Then you do a
privilege lookup for each group in a manner identical to individual
users.

This is different from the current implementation which looks through
all existing privileges on a table and if one is owned by a group then
scan pg_group to see if the user is in the group. That might be
suboptimal.

* Performance concerns

The fastest privilege system if of course one that does no checking at
all. Features always cost a price. I have no concern, however, that
this new implementation would cause any noticeable penalty at all. If
you consider how much reading the parser, planner, optimizer, and
rewriter do just to make sense of a query, this is really a minor
item.

If you're the table owner then no access checking is done at all. If
you don't use groups or column privileges then one syscache lookup
will tell you yes or no. If you do use groups then the new system
would potentially even be faster. If you want to use column privileges
you'd currently wait forever. :)

* Possibilities for extensions

One thing that has been thrown around is a LOCK privilege. Currently
everyone with write access can lock the table completely. It would
make sense to me to restrict locks of Share mode and higher to the
owner and owners of this privilege.

There is also demand for various CREATE privileges (one for each thing
you can create, one supposes). Once we have schemas we can easily fit
this into the above design. Since this is not covered by the standard
("implementation-defined"), a good round of discussion ought to take
place first.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Mon, 22 May 2000 19:25:29 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Proposal for enhancements of privilege system
Peter Eisentraut <peter_e@gmx.net> writes:
> [ pretty good proposal ]

Just a couple of trivial comments ---

> Some arguments for user sysids I have heard in the past were that some
> people want to keep them the same as the Unix uid.

There may once have been a reason for that, but it's probably buried in
ancient Berkeley-specific admin practices.  I sure can't see any good
reason to keep the extra number around now.  As you say, it should be
OK to use the pg_shadow row OID to identify users.

BTW I believe most of the "owner" columns in the system tables are
declared as "int4" because they hold sysids ... don't forget to change
'em to be "Oid" when you do this.

> Another reason why an oid based arrangement would be nicer is that if
> we did the same thing for groups why could refer to both users and
> groups through one attribute. See `Implementation' below.

"findoidjoins" will probably get unhappy with you if you do that.
Which is maybe not a big deal, but...

> "prigrantee" is obviously the user to
> which you grant the privilege or a group.
> We could put 0 for "public".

I'd be inclined to provide an additional field that explicitly encodes
"grantee is user", "grantee is group", or "grantee is public".  That
way you don't need to do a join to find out what you are looking at.

Really, having an OID column that might reference either users or groups
is the SQL equivalent of a type pun.  An alternative representation that
would avoid that would be two OID columns, one to use if user and one
to use if group (if they're both 0 then it's grant to public).

			regards, tom lane

Mon, 22 May 2000 20:00:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for enhancements of privilege system 
> > Another reason why an oid based arrangement would be nicer is that if
> > we did the same thing for groups why could refer to both users and
> > groups through one attribute. See `Implementation' below.
> 
> "findoidjoins" will probably get unhappy with you if you do that.
> Which is maybe not a big deal, but...

I think it will find it.  It is not a big deal anyway.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 22 May 2000 20:23:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Proposal for enhancements of privilege system
Peter Eisentraut wrote:

> pg_privilege (
>     priobj oid,
>     prigrantor oid,
>     prigrantee oid,
>     priaction char,
>     priisgrantable boolean,
> 
>     primary key (priobj, prigrantee, priaction)
> )
> 

I like it.

> The straightforward choice would be to store a single reference to
> pg_class when the privilege describes the whole table, and
> pg_attribute references when only specific columns are named. That
> would mean the lookup routine will first look for a pg_class.oid entry
> and, failing that, then for possible pg_attribute.oid entries for the
> columns that it's interested in. This is of course suboptimal when no
> privilege exists in the first place but that is not necessarily the case
> we're optimizing for.

Don't worry about performance for the access denied case. That is going
to be outweighed 1000:1 by the access allowed case. Go for the clean
solution.

Tue, 23 May 2000 10:49:06 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: Proposal for enhancements of privilege system
Tom Lane writes:

> Really, having an OID column that might reference either users or groups
> is the SQL equivalent of a type pun.

Well, I don't really know what a type pun is but the priobj column would
do exactly the same thing by referring to tables, types, functions, etc.
by unadorned oid, which I thought would be pretty nice. Really, in normal
mode of operation there is never a question "Does this privilege apply to
a user or a group?" it's always "Given this object and this user/group id,
do I have access?" I don't see that as a practical problem, but I'll think
about it.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 23 May 2000 23:38:11 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Proposal for enhancements of privilege system 
On Tue, 23 May 2000, Chris Bitmead wrote:
> Peter Eisentraut wrote:
> 
> > pg_privilege (
> >     priobj oid,
> >     prigrantor oid,
> >     prigrantee oid,
> >     priaction char,
> >     priisgrantable boolean,
> > 
> >     primary key (priobj, prigrantee, priaction)
> > )
> > 
> 
> I like it.

Imho this is an area where it does make sense to look at what other db's do,
because it makes the toolwriters life so much easier if pg behaves like some other
common db. Thus I do not really like a standalone design.

Other db's usually use a char array for priaction and don't have priisgrantable, 
but code it into priaction. Or they use a bitfield. This has the advantage of only 
producing one row per table.

Andreas

Sun, 28 May 2000 10:12:23 +0200
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
Re: Proposal for enhancements of privilege system
Andreas Zeugswetter writes:

> Imho this is an area where it does make sense to look at what other
> db's do, because it makes the toolwriters life so much easier if pg
> behaves like some other common db.

The defined interface to the privilege system is GRANT, REVOKE, and
"access denied" (and a couple of INFORMATION_SCHEMA views, eventually).
I don't see how other db's play into this.

> Other db's usually use a char array for priaction and don't have
> priisgrantable, but code it into priaction. Or they use a bitfield.
> This has the advantage of only producing one row per table.

That's the price I'm willing to pay for abstraction, extensibility, and
verifyability. But I'm open for better ideas.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Mon, 29 May 2000 19:11:49 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Proposal for enhancements of privilege system