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)