Re: Column Privileges: NULL instead of permission denied

> From: Stephen Frost [mailto:sfrost@snowman.net]
> 
> * Matthew Hawn (matthewh@donaanacounty.org) wrote:
> > I have a table with privileged data that is restricted using column
> level
> > permissions.  I would like to have single query  that returns data
> from the
> > table.  If the user has permission, it should return the data but
> return
> > NULL if the user does not have permission.   I do not want to create
> > separate queries in my application for different users.
> 
> I think you'll have to build the query in the application to either
> have
> the NULL or not have it.  If you try to reference it in the query, PG
> is
> going to give you that permission denied error and I don't think
> there's
> an easy way to change that (and I'm not sure that we'd want to...).
> 
> 	Thanks,
> 
> 		Stephen

I definitely agree the default behavior should be to generate a permission
error.  However, to build my query in the application, I would have to
  * Query the database for all column permissions
  * Dynamically construct a sql statement to pull the relevant data.

My application has fairly fine grained control on columns so this could be
almost any column on any table referenced. In addition, dynamically creating
a query is expensive and error prone and prevents prepared queries and query
caching.

For a web application, all this becomes very expensive. 

Implicitly, there should be an error message, but it would be nice to
explicitly state a column should return null on permission denied. Something
like:

Select name, NULLIFDENIED(ssn) from people.

Or a special keyword, or a special table function that converts denied
fields to null:

Select name, ssn from NULLIFDENIED(people)