Re: Column Privileges: NULL instead of permission denied

In article <DF0C87D105B235419E2D9E5066CCCF510B72B0(at)gcmxbe02(dot)dac(dot)int>,
Matthew Hawn <matthewh(at)donaanacounty(dot)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;