Best Practice to implement Postgres Row Level Security

[pratikpparikh]

What is the best way to implement Postgres Row Level Security using Kysely? Do you have a concept of interceptor in Kysely? Is WithSchemaPlugin the mechanism to do this? If so please provide a example.

Thank you in advance.

Regards, Pratik Parikh

[koskimas]

I have never done anything using postgres row level security, so can't really help you. There's nothing built-in for that in Kysely. We don't have interceptors. You could maybe achieve something useful with plugins, but if I understood correctly, you want to run extra queries that set the role. That won't be possible using plugins since the transform hook is not async.

You can see a bunch of examples in the codebase. For example the WithSchemaPlugin you mentioned.

[lancejpollard]

I found this link from Supabase's warning on all my tables I created through Kysely:

Clicking that link in the warning leads to an admin page saying:

Warning: RLS is disabled. Your table is publicly readable and writable.
Anyone with the anon. key can modify or delete your data. You should turn on RLS and create access policies to keep your data secure.

What should I do here exactly?

Maybe having a Kysely recommendation for this situation might be useful (to myself and others).

[igalklebanov]

@lancejpollard check https://github.com/kysely-org/awesome-kysely?tab=readme-ov-file#plugins, there are some security-related plugins there, maybe they'll help.

Kysely is an unopinionated query builder. Nothing more, nothing less. Security patterns are out of scope for Kysely's core and belong in community libraries (plugins, custom dialects, etc.). We do try to help support these plugins/dialects with improvements to the plugin/dialect API/internals.

Follow the official guidelines https://www.postgresql.org/docs/current/ddl-rowsecurity.html.

[Gaspero]

What should I do here exactly?

Maybe having a Kysely recommendation for this situation might be useful (to myself and others).

@lancejpollard You can find an example of RLS policies at Supabase docs https://supabase.com/docs/guides/auth/row-level-security#policies . Warning basically says that RLS is disabled (alter table {tablename} enable row level security).

After enabling RLS you will also need to define some policies.

Like Igal mentioned you can't use Kysely to build such query as it is an unopinionated query builder and PLS policies is something Postgres-specific. But you could execute such query with Kysely using raw sql. See an example https://kyse.link/?p=s&i=0kjyO4eBSXFs8rhK7sbW

I guess you can take this example even further if you create a helper https://kysely.dev/docs/recipes/extending-kysely

(Assuming you only use Kysely for DB schema management and not trying to query data with RLS policies applied).

PS: AFAIK the warning is only applied to requests made using PostgREST API. If you are are not using PostgREST API and haven't leaked your Anon key in any client app - you have nothing to worry about. External user might be able to call PostgREST API using Anon key. If no key is passed to request, it will be treated as unauthorized. But I am not a big Supabase expert and might be mistaking.

[stoicskyline]

PS: AFAIK the warning is only applied to requests made using PostgREST API. If you are are not using PostgREST API and haven't leaked your Anon key in any client app - you have nothing to worry about. External user might be able to call PostgREST API using Anon key. If no key is passed to request, it will be treated as unauthorized.

To clarify: anon key is fine in the client since it's used by @supabase/supabase-js to do anonymous PostgREST requests. What to not leak is the service role key in the client.

[stoicskyline]

If you already chose Supabase, then the path of least resistance may be to use their tooling where possible, and opt in Kysely/etc for other aspects.

Supabase has a huge feature set that they aspire to support, but it may not all be at the same quality. The closeness to raw SQL may not be easy for junior devs. Though if you start adding libs to replace parts of their offering, the higher your bus count.

You'll need consider if each lib will live longer, be more maintainable for your team size, etc for different aspects:

• supabase-js or another solution involving Kysely/etc
• RLS
• migrations
• seeding
• raw SQL queries - which is not easy to do https://github.com/orgs/supabase/discussions/3419

The last one it seems you have to roll Kysely or another lib to do.

[giorgiogross]

Kysely actually supports RLS out of the box, this is what I'm doing:

        // Cache the pool somewhere to be reused across connections
        const pool = new pg.Pool({
            // connectionString: '...you connection string...'
        });

        /*
                Then initialise dialect.pool, overriding the connect and end params as below.
        Ideally, you'd do this in some kind of factory that instantiates a new Kysely instance
        for each request, but reuses the cached pool.
                "vars" here is the request context variables passed by my gateway (eg express, hono, ...)
                which were initialised when authenticating incoming jwt bearer tokens.
                */
        const dialect = new PostgresDialect({
            pool: {
                connect: async () => {
                    const client = await pool.connect();

                    /*
                        Set JWT claims to enable RLS, mirroring what Supabase does.
                        See https://github.com/drizzle-team/drizzle-orm/issues/594#issuecomment-2016607868
                        */
                    await client.query(
                        "SELECT set_config('request.jwt.claims', $1, FALSE)",
                        [JSON.stringify(vars.jwtPayload)]
                    );
                    await client.query(
                        "SELECT set_config('request.jwt.claim.sub', $1, FALSE)",
                        [vars.userId]
                    );
                    await client.query(
                        "SELECT set_config('request.jwt.claim.email', $1, FALSE)",
                        [vars.userEmail]
                    );

                    // Only support authenticated for authed users:
                    await client.query(
                        "SELECT set_config('request.jwt.claim.role', 'authenticated', FALSE)"
                    );
                    await client.query('SET ROLE authenticated');
                    return client;
                },
                end: async () => pool.end(),
            },
        });

                // Then use the db with the initialised dialect. Pass in your own types.
        const db = new Kysely<Database>({
            dialect,
        });

Was taking in https://github.com/brianc/node-postgres/issues/2897 and https://github.com/drizzle-team/drizzle-orm/issues/594#issuecomment-2016607868

Every time a new client is used, connect() is invoked which initialises the client's config with set_config(...) and SET ROLE. You simply have to make sure that you don't reuse a client which was set up for another user, which I'm getting around with by instantiating Kysely for each request and just caching the pool.