Re: System catalog representation of access privileges

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Alternatively, since you really only need two bits per privilege,
>> perhaps a pair of BIT (VARYING?) fields would be a more effective
>> approach. BIT VARYING would have the nice property that adding a new
>> privilege type doesn't force initdb.

> This would be tricky to index, I think.

True, but I don't believe that making the privilege value part of the
index is useful.

> Maybe just an index on (object, grantee) and walk through that with an
> index scan. This is done in some other places as well (triggers, I
> recall), but the performance is probably not too exciting.

I agree, that'd be slower than we'd like. It needs to be cached somehow.

The major problem is that you'd need multiple index scans: after failing
to find anything for (table, currentuser) you'd also need to try
(table, 0) for PUBLIC and (table, G) for every group G that contains the
current user. Not to mention the scan to find out which groups those are.

It gets rapidly worse if you want to allow any wildcarding on the object
--- for example, if a privilege record attached to a schema can allow
access to the tables therein, which I think should be possible. You'd
have to repeat the above for each possible priobject that might relate
to the target object.

I think this might be tolerable for getting the info in the first place,
but the final results really need to be cached. That's why I was
wondering about a special "privilege cache".

> However, last I looked at the syscache I figured that it would be
> perfectly capable of handling non-unique indexes if there only was an API
> to retrieve those values.

Yes, it's an API problem more than anything else. Invent away, if that
seems like a needed component.

regards, tom lane