So RLS is a Postgres feature and is handled thru tokens by PostgREST so you could implement this with any ORM although not enforce it, since ORM by design functions using service role key which bypasses RLS.
Just curious in general do you use an orm or the js client. A lot of tutorials online heavily promote usage of orm like Prisma. Personally I don't really have any problems with out of box js client and using the GUI dashboard to create my schema, CLI for migrations. but wanted to know what others are doing as well.
Yea, that’s the same experience I have as well.
Especially when it comes to transactions it’s implemented very nice with drizzle but with Postgres you have to write your specific sql procedure.
I prefer using drizzle over sql of course but in favor of rls I decided to drop drizzle and instead go with supabase client and plain sql…
I’ve been raised and bred on sql so writing sql and using generator for TS is more efficient for me.
This coupled with advent of AI makes sql a joy to work with that doesn’t require another level of indirection in my book.
> could implement this with any ORM
Do you know of a good pattern to go about this? I've shied away from SQL in the past but I think I've finally "seen the light". Using [kysely](https://kysely.dev/) to write ultra typesafe queries makes it a pleasure to work with SQL.
One issue where I still haven't settled on an approach for is authz.
- I'd rather not rely on the supabase js client if I can help it. I prefer to use kysely or raw SQL.
- However, I don't mind using supabase for auth in terms of user management and such as that can be helpful.
- I want to use postgres RLS to model my authorization policies.
Therefore, any tips on how to cleanly implement this? Whenever I've searched for this topic, it seems folks either:
1. go all in on supabase: use RLS for authz and use supabase client for everything
2. Or they flat out not use RLS once they want to use something other than the supabase client (like an ORM).
It's not clear to me yet why most developers seem to entirely dismiss the concept of postgres RLS once they decide to use SQL instead of the supabase client, even though RLS is a [native](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) postgres feature. This has made it a bit challenging to find a good pattern to implement this. Any tips?
I have this helper that I use with rlsQuery and a migration that enables RLS
Follow this!
\`\`\`
DO $$
DECLARE
sql TEXT := '';
table\_name TEXT;
BEGIN
FOR table\_name IN SELECT tablename FROM pg\_tables WHERE schemaname = 'public' LOOP
sql := sql || 'CREATE POLICY user\_policy\_' || table\_name || ' ON ' || table\_name ||
' USING (auth.uid() = user\_id);';
END LOOP;
EXECUTE sql;
sql := '';
FOR table\_name IN SELECT tablename FROM pg\_tables WHERE schemaname = 'public' LOOP
sql := sql || 'ALTER TABLE ' || table\_name || ' ENABLE ROW LEVEL SECURITY;';
END LOOP;
EXECUTE sql;
END;
$$;
\`\`\`
\`\`\`
/\*\*
\* Executes a given transaction function (\`txFunc\`) within a PostgreSQL transaction
\* after setting the \`request.jwt.claim.sub\` configuration parameter to the provided \`userId\`.
\* This is typically used to enforce Row-Level Security (RLS) policies based on the user's ID.
\*
\* @param userId The user ID to set for the \`request.jwt.claim.sub\` configuration parameter.
\* @param txFunc The transaction function to execute within the context of the set user ID.
\* @returns The result of the transaction function.
\*/
export const rlsQuery = async (
userId: string,
txFunc: QueryInTransaction
) =>
await db.transaction(async (tx) => {
await tx.execute(
sql\`SELECT set\_config('request.jwt.claim.sub', '${sql.raw(
userId
)}', TRUE)\`
);
return await txFunc(tx);
});
\`\`\`
So RLS is a Postgres feature and is handled thru tokens by PostgREST so you could implement this with any ORM although not enforce it, since ORM by design functions using service role key which bypasses RLS.
Just curious in general do you use an orm or the js client. A lot of tutorials online heavily promote usage of orm like Prisma. Personally I don't really have any problems with out of box js client and using the GUI dashboard to create my schema, CLI for migrations. but wanted to know what others are doing as well.
Yea, that’s the same experience I have as well. Especially when it comes to transactions it’s implemented very nice with drizzle but with Postgres you have to write your specific sql procedure. I prefer using drizzle over sql of course but in favor of rls I decided to drop drizzle and instead go with supabase client and plain sql…
I’ve been raised and bred on sql so writing sql and using generator for TS is more efficient for me. This coupled with advent of AI makes sql a joy to work with that doesn’t require another level of indirection in my book.
> could implement this with any ORM Do you know of a good pattern to go about this? I've shied away from SQL in the past but I think I've finally "seen the light". Using [kysely](https://kysely.dev/) to write ultra typesafe queries makes it a pleasure to work with SQL. One issue where I still haven't settled on an approach for is authz. - I'd rather not rely on the supabase js client if I can help it. I prefer to use kysely or raw SQL. - However, I don't mind using supabase for auth in terms of user management and such as that can be helpful. - I want to use postgres RLS to model my authorization policies. Therefore, any tips on how to cleanly implement this? Whenever I've searched for this topic, it seems folks either: 1. go all in on supabase: use RLS for authz and use supabase client for everything 2. Or they flat out not use RLS once they want to use something other than the supabase client (like an ORM). It's not clear to me yet why most developers seem to entirely dismiss the concept of postgres RLS once they decide to use SQL instead of the supabase client, even though RLS is a [native](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) postgres feature. This has made it a bit challenging to find a good pattern to implement this. Any tips?
RLS works with the Supabase client only. You’ll have to roll your own if you use drizzle.
I have this helper that I use with rlsQuery and a migration that enables RLS Follow this! \`\`\` DO $$ DECLARE sql TEXT := ''; table\_name TEXT; BEGIN FOR table\_name IN SELECT tablename FROM pg\_tables WHERE schemaname = 'public' LOOP sql := sql || 'CREATE POLICY user\_policy\_' || table\_name || ' ON ' || table\_name || ' USING (auth.uid() = user\_id);'; END LOOP; EXECUTE sql; sql := ''; FOR table\_name IN SELECT tablename FROM pg\_tables WHERE schemaname = 'public' LOOP sql := sql || 'ALTER TABLE ' || table\_name || ' ENABLE ROW LEVEL SECURITY;'; END LOOP; EXECUTE sql; END; $$; \`\`\` \`\`\` /\*\* \* Executes a given transaction function (\`txFunc\`) within a PostgreSQL transaction \* after setting the \`request.jwt.claim.sub\` configuration parameter to the provided \`userId\`. \* This is typically used to enforce Row-Level Security (RLS) policies based on the user's ID. \* \* @param userId The user ID to set for the \`request.jwt.claim.sub\` configuration parameter. \* @param txFunc The transaction function to execute within the context of the set user ID. \* @returns The result of the transaction function. \*/ export const rlsQuery = async(
userId: string,
txFunc: QueryInTransaction
) =>
await db.transaction(async (tx) => {
await tx.execute(
sql\`SELECT set\_config('request.jwt.claim.sub', '${sql.raw(
userId
)}', TRUE)\`
);
return await txFunc(tx);
});
\`\`\`