3 replies

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

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';

SET app.user_id = 'user1';

before querying in my typical query somehow? Oh I guess

await db.execute(sql

await db.execute(sql

SET app.user_id = 'user1';

works....?

How about question 1 then? I guess also

await db.execute(sql``)

await db.execute(sql``)

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

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';

SET app.user_id = 'user1';

before querying in my typical query somehow? Oh I guess

await db.execute(sql

await db.execute(sql

SET app.user_id = 'user1';

works....?

How about question 1 then? I guess also

await db.execute(sql``)

await db.execute(sql``)

How to implement Row Level Security in Postgres?

Similar Threads

How to implement Row Level Security in Postgres?

Similar Threads

Similar Threads

Similar Threads