Drizzle TeamDT
Drizzle Team•3y ago
Paul

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;


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``)?
Was this page helpful?