How to configure PostgreSQL security settings to allow select of different columns depending on the row

Question

I have a complex security/authorization situation to set up in Postgres 15, where one role can CRUD all data, while another role can read-only data but must be allowed to see different columns depending on a value in a row's column.

I've simplified this down to a very basic example below, although my real situation is more complex and involves setting this up to work with PostgREST in Supabase

I have created 2 roles in my database: restricted and admin and a table, which we'll call my_table. This has Row level security enabled too.

CREATE ROLE restricted;
CREATE ROLE admin;
CREATE TABLE my_table (
 id SERIAL PRIMARY KEY,
 public_column TEXT,
 private_column TEXT,
 role TEXT CHECK (role IN ('restricted', 'admin'))
);
--Enable RLS
ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;
--Grant all priviliges to the default postgres user for later
GRANT ALL on my_table to postgres;

The admin role should be able to read all rows and all columns of my_table.

GRANT ALL ON my_table TO admin;
CREATE POLICY "admins_can_crud_all_rows" ON my_table
AS PERMISSIVE FOR ALL
TO admin
USING (true);
--Assuming I'm logged in as admin, I can now run this:
SELECT * from my_table;
--etc.

The restricted role should have these permissions only:

• For rows where role = "restricted", they should be able to see all columns.
• For rows where role = "admin" they should be able to only see the columns id and public_column

Here's the method I came up with, but I'm unsure if it's the optimal way to do it.

--Setup priviliges for restricted role: only allow select and limit the acceptable columns
GRANT SELECT(id, public_column) ON my_table TO restricted;
--RLS policy for restricted role. The above priviliges restrict columns, its ok to allow all rows here
CREATE POLICY "restricted_can_read_all_rows_with_limited_columns" ON my_table
AS PERMISSIVE FOR SELECT
TO restricted
USING (true);
--Create a view, owned by user postgres (assuming we're currently logged in as postgres)
--Note: the postgres role is configured to bypass RLS
CREATE VIEW my_table_all_columns_when_role_is_restricted AS
 SELECT *
 FROM my_table
 WHERE role = 'restricted';
--Grant permission to use this view to "restricted" role
GRANT SELECT ON my_table_all_columns_when_role_is_restricted TO restricted;
--Assuming I'm logged in as the role "restricted", I can do these things now:
--Select all rows but with limited columns
SELECT id, public_column FROM my_table;
--Select limited rows but with all columns
--Note the view will run with permissions of the role postgres
SELECT * FROM my_table_all_columns_when_role_restricted;

I'm pretty sure that would work as expected, however I'm wondering if this is the optimal way to set things up? Is there any better ways that would be more performant and/or easier to maintain and understand?

• Jasen: This is a very good question ... to ask on DBA
• Zegarek: You need to set security_invoker and security_barrier on that view. See in the doc how a simple function could be used to expose the private rows. It might also be a good idea to use schema isolation and appropriate usage patterns, and keep restricted role's objects in their restricted schema.
• callum.boase: Thanks @Zegarek After reading the docs you linked, I understand what security_barrier means and why I should add it. I'm still not clear on why I'd need to set the view with security_invoker too. My understanding is that setting the view as security_invoker would mean it executes with the privileges of the user that calls it but if the select to the view was executed as the restricted role, wouldn't it prevent them seeing all columns?

Answer 1

It looks like I was mistaken in three different ways when posting my comment:

1. Your policies only grant full access to whatever a given user was already granted access to, so security_barrier is just good practice, changing nothing in this case.
2. I realised that your view is supposed to access everything, then narrow it down to whatever the other user should be able to see, so security_invoker (which would normally be a sensible default in a setup like this) not only doesn't help, it actually breaks it.
3. You're already resorting to views and your policies are effectively repeated grants, so you can do without RLS entirely by sticking to views (without security_invoker) that hides sensitive data, then let restricted role only use those, possibly revoking or limiting their function- and procedure-related permissions. As the name suggests, row-level security policies aren't meant for value-level control.

INSERT INTO my_table 
 ( public_column , private_column , role ) 
VALUES
 ('public_value_in_restricted_row','private_value_in_restricted_row','restricted')
,('public_value_in_admin_row' ,'private_value_in_admin_row' ,'admin' )
RETURNING *;
CREATE VIEW my_table_all_columns_when_role_is_restricted AS
 SELECT *
 FROM my_table
 WHERE role = 'restricted' OR role is null
UNION ALL
 SELECT id,public_column,null,null
 FROM my_table
 WHERE role = 'admin';
 
GRANT SELECT ON my_table_all_columns_when_role_is_restricted TO restricted;

• You haven't excluded null values from my_table.role, so your current view isn't hiding just admin rows but null ones also. I assumed restricted visibility for those.
• By default, with unaltered default privileges, there's no need to grant all to the object owner:

  default privileges for any object type normally grant all grantable permissions to the object owner

• A materialized view should be able to hide away stats and other secondary features of my_table that users of restricted role might try to inspect in an attempt to figure out what's hidden from them, at the cost of having to refresh it.
• You could also split the table into one (id,public_column) and another (id,private_column,role), partitioned by list of role values, into admin and restricted parts, thus isolating all sensitive features of the admin part. Then, public part can be read freely and left joined to RLS-secured private part when necessary CREATE POLICY restricted_can_select_restricted_rows ON my_table_private_part_restricted AS PERMISSIVE FOR SELECT TO restricted USING (role='restricted');. demo

• callum.boase: Thanks @Zegarek that's a nice idea to create a view like that. Unfortunately, it seems like that view has a security issue using something like the tricky() function example from the docs, even though I've created the view with security_barrier enabled. Fiddle demonstrating the problem. Am I missing something or doing something wrong? Thanks also for your suggestion of splitting the table. I haven't tried that yet.
• Zegarek: Exactly my point earlier. Here I suggest to use the view only if you're already using views anyway, only making sure to revoke or limit routine-related permissions to reduce the tricky() risk as much as possible - if the restricteds can't create and execute leaky code, they can't pull a tricky() on you. Although I'm sure there's more where that came from. The alternative is supposed to only show a setup where the sensitive objects are split off and kept completely isolated from restricted - that's just one way and I expect you'll want to minimise structural fragmentation.
• callum.boase: Thanks for the clarification. My real situation is that the two roles will be for use by an API server created with postgREST, which does not provide direct db access or endpoints to create functions, so I think the view method will be fine for me. Thankyou! One final question: are you aware of any advantages or drawbacks in using functions instead of views to provide access to some data? The use of both functions and views seems to be a common pattern in postgREST implementations, but I haven't found clear answers as to when to use which.