Re: Column Privileges: NULL instead of permission denied - Mailing list pgsql-general

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;