Re: Column-Level Privileges

On Tue, Feb 3, 2009 at 7:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>> * Some of the information_schema views are specified to respond to
>>> per-column privileges; the column_privileges and columns views
>>> certainly need work now to meet spec, and there might be others.
>
>> Done.
>
> I looked through the spec a bit. If I'm reading it right, these
> views should show columns that you have either table-level or
> column-level privilege for:
> column_privileges
> columns
> key_column_usage
> role_column_grants
>
> What's more, these views should show you tables/views that you have
> column privilege on any column of, even if you haven't got any
> full-table privileges:
> tables
> table_constraints
> table_privileges
> views
>
> I thought about handling the tests for the latter by exposing
> pg_attribute_aclcheck_all() as a function named something like
> has_any_column_privilege(). However, that would amount to forcing a
> nestloop-with-inner-indexscan join to pg_attribute for any table you
> didn't have full-table privileges for; also it would bloat the syscache
> in a database with lots of tables. It might be better to expose that
> join explicitly and let the planner try to decide what to do. OTOH
> I don't think the planner would be very smart about avoiding the join
> if you do have full-table privileges. Either way you slice it it could
> be really slow :-(
>
> Comments, better ideas? Does anyone think I misread the spec?

Perhaps there should be a column in pg_class that indicates, in
essence, whether there is any point in checking pg_attribute. Call it
relaclpartial aclitem[]. Whenever user U has column-level privileges
P and Q on a subset of the columns in the table, put {U=PQ} in
relaclpartial.

...Robert