Drizzle Team•13mo ago

How to implement Row Level Security in Postgres?

In my chat app, I have chats and messages. There are multiple messages in each chat. I'm trying to prevent user1 from editing user2's messages. ChatGPT suggests row-level security like the following

-- Enable row-level security on the messages table
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows users to access only their own messages
CREATE POLICY messages_user_policy
  FOR ALL
  USING (current_setting('app.user_id')::text = chat_id);

-- Set the current user ID when connecting to the database
SET app.user_id = 'user1';

-- Now, when querying the messages table, only messages belonging to 'user1' will be returned
SELECT * FROM messages;

-- Enable row-level security on the messages table
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows users to access only their own messages
CREATE POLICY messages_user_policy
  FOR ALL
  USING (current_setting('app.user_id')::text = chat_id);

-- Set the current user ID when connecting to the database
SET app.user_id = 'user1';

-- Now, when querying the messages table, only messages belonging to 'user1' will be returned
SELECT * FROM messages;

Is there any guidance on where/how I can implement this via drizzle-orm? I'm okay to use the "magical" sql function. But.. 1) When defining the policy, is there a place to define it in my pgSchema() call? 2) When querying, can I slot in the SET app.user_id = 'user1'; before querying in my typical query somehow? Oh I guess await db.execute(sqlSET app.user_id = 'user1';) works....? How about question 1 then? I guess also await db.execute(sql``)?

3 Replies

Angelelz•13mo ago

This is currently being worked on (by me)

Angelelz•13mo ago

Please take a look at the issue and share your thoughts on what you would like to see in this feature: https://github.com/drizzle-team/drizzle-orm/issues/594

GitHub

[FEATURE]: Support PostgreSQL's Row Level Security (RLS) · Issue #5...

Describe want to want Supabase is really nicely using Row Level Secruity for granular authorization rules. 🔗 Here's the link to their docs: https://supabase.com/docs/guides/auth/row-level-secur...

PaulOP•13mo ago

Thanks! Helpful as always!

Gaming

Programming

How to implement Row Level Security in Postgres?