1

Is there a way to give a PostgreSQL user access to a different columns on different rows of a table? For example in the following table, is there some combination of GRANT or POLICY or ??? that would only allow the user to view the "allowed" cells?

CREATE TABLE my_table (
  id INT,
  col_a TEXT,
  col_b TEXT
);

INSERT INTO my_table VALUES 
  (1, 'allowed', 'allowed'),
  (2, 'allowed', 'forbidden')
;

I think it can be done by splitting the columns in to different tables, but is it possible with only a single table?

One possible solution suggested by O. Jones - use a view:

CREATE OR REPLACE VIEW my_secure_view AS
SELECT
  id,
  col_a,
  CASE WHEN id = 1 THEN col_b ELSE NULL END AS col_b
FROM my_table;

GRANT SELECT ON my_secure_view TO whatever_user;
REVOKE SELECT ON my_table FROM whatever_user;

Is there a better way?

Roy Paterson
  • 863
  • 9
  • 17

1 Answers1

2

You can do this by creating a VIEW containing the rows and columns you allow your user to see, then granting the user SELECT access to the view but not the underlying table.

For example

CREATE OR REPLACE VIEW my_secure_view AS
SELECT allowedcol1, allowedcol2
  FROM my_table
 WHERE col_b <> 'forbidden';

GRANT SELECT ON my_secure_view TO whatever_user;
REVOKE SELECT ON my_table TO whatever_user;

You can also, if you wish, write stored procedures and grant access to them to the users you choose, while revoking access to the underlying tables.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • This answer doesn't quite work because whatever_user can't see the "allowed" value of `col_a` in row #2. It does suggest a solution though... – Roy Paterson May 13 '21 at 15:28
  • You should probably create the view using `WITH (security_barrier)`: http://rhaas.blogspot.com/2012/03/security-barrier-views.html – Nick Barnes May 13 '21 at 22:22