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;