Thread: Column Privileges: NULL instead of permission denied

* 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

In article <DF0C87D105B235419E2D9E5066CCCF510B72B0@gcmxbe02.dac.int>,
Matthew Hawn <matthewh@donaanacounty.org> writes:

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



> Ex:

> Table people:  Name, SSN



> If I try:

> Select name, ssn from people;



> I get if the user does not have permission:

> **ERROR: permission denied for relation people **



> I would like to get:

> No Permission:

>  Dave, NULL

>  Bill, NULL

>  Steve, NULL

> Permission:

>  Dave, 456

>  Bill, 789

>   Steve, 123

The only thing I can think of is an ugly kludge:

-- Define the following function as a user with SELECT privilege

CREATE FUNCTION doselect(IN currusr text, OUT name text, OUT ssn int) RETURNS SETOF RECORD AS $$
SELECT name,
       CASE has_column_privilege($1, 'people', 'ssn', 'SELECT')
       WHEN true THEN ssn
       ELSE NULL END AS ssn
FROM people
$$ LANGUAGE sql SECURITY DEFINER;

CREATE VIEW people_view AS
SELECT * FROM doselect(current_user);

-- The following query will do what you want

SELECT * FROM people_view;

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