RLS with anonymous and authenticated
Hi there. I'm wondering if it's possible to use one single connection string to connect to either an anonymous or authenticated role, depending on the JWT provided by the connection string - i.e. if i can use only the
anonymous role (with a password) and provide the token to optionally move the user to authenticated - is something like this possible?20 Replies
national-goldOP•6mo ago
the idea of what i want to achieve here is to have one connection string and based on the params provided it will allow me to connect to different roles - if token is given then it will auto parse it and switch to
authenticated
the thing is that i use drizzle on the client so that i can fetch data there (just like with supabase) so i'm not 100% certain how to set it up right
i've tried a few things so far:
1. disable rls and just use the authenticated role with a password (works)
2. have RLS disabled with the authenticated role and pass the token (throws a 500, no response body)
3. have RLS enabled with the authenticated role, with no JWT (works, returns no data)
4. have RLS enabled with the authenticated role AND provide the JWT (throws 500 again with no body)
when i take the JWT (from the Authorization header) and verify it on jwt.io with the public key available to Neon, it is a valid JWT and the signature is valid as well - i'm not really sure what to do hereextended-salmon•6mo ago
neon rls assumes that you're using our serverless driver. under the hood serverless driver runs sql-over-http by connecting to our multi tentat proxy. this multi tenant proxy actually requires a passwordless connection string in the request header
extended-salmon•6mo ago
now if you don't want to use neon serveless driver then recently we've introduced one improvement into our postgres extention (
pg_session_jwt) -- you might be interested in responses under https://github.com/neondatabase/pg_session_jwt/issues/33
but it would require your backend to connect to the postgres via TCP (standard connection method with database drivers) and run some SET LOCAL opsGitHub
auth functions (session, user_id) return null after setting `...Steps to reproduce SET request.jwt.claims = '{"sub": "test-user"}' SELECT auth.user_id() -- returns null Expected result test-user Actual result null Environment Neon Co...
extended-salmon•6mo ago
also responsibility to validate the JWT and
SET LOCAL decoded JWT body would be on your backend
refs:
- https://github.com/neondatabase/pg_session_jwt/issues/33#issuecomment-2778427970
- https://github.com/neondatabase/pg_session_jwt/issues/33#issuecomment-2778441191national-goldOP•6mo ago
i am using the serverless driver -
@neondatabase/serverless with drizzle as a proxy as well
like i do one of these (they are equivalent)
the issue with the passwordless string is that i can't have a JWT for anonymous users - they just don't have one, so i need to fetch something differently there. the fact that neon also returns a 500 when fetching is also an issue
the way i know neon is throwing a 500 is because i'm running the query from my browser by just frontend fetching - pretty much the same as supabase which i migrated fromextended-salmon•6mo ago
having passwordless connection string that cant be used for TCP connection is actually a security feature - esp. when you include it in your frontend. Anyone could see that and connect to your database otherwise
if you need to use both
under the hood serverless driver runs sql-over-http by connecting to our multi tentat proxy. this multi tenant proxy actually requires a passwordless connection string in the request header
authenticated and anonymous I guess you should create two db objects for each role and your frontend logic would need to read the JWT to know when to use whichthe issue with the passwordless string is that i can't have a JWT for anonymous users - they just don't have oneI can see your point now. This is indeed problematic if you're using neon serverless driver directly from your frontend and want to use both authenticated and anonymous
optimistic-gold•6mo ago
@oof2win2 it's not a good idea to connect directly to the database from the frontend
national-goldOP•6mo ago
yeah. i guess i can switch the URI based on the presence of the JWT which is fine, it was just the 500s throwing me off - i'm not sure what to do to actually get the query to execute with RLS
why not? the issues i see are rate limiting but isn't that soon to be solved via allowed queries and rate limits on the serverless driver itself
extended-salmon•6mo ago
yeah. i guess i can switch the URI based on the presence of the JWT which is fineI think such request will be rejected because the absence of JWT -- that's why I said that it's problematic
national-goldOP•6mo ago
ah i see. so basically anonymous auth does not work, even on the anon role?
if that's the case, does it mean i need to move all of my data fetching from the client to the server?
extended-salmon•6mo ago
yeah I think that's a possible way of achieving what you described:
- using
authenticated + jwt + rls policies that rely on JWT
- using anonymous with the TCP connection in backend -- you will have to finegrain the anonymous access rights so that it can only SELECT only from specific tables I guessnational-goldOP•6mo ago
i have all of the policies setup right - my
anonymous role can only select from stuff that it has rights to. might try to figure out some other shenanigans with the anon role since most data fetching is in the frontend (i'm coming from supabase which is why)
@mrl5 is there a reason why i shouldn't just make the anonymous role have a password and fetch data through that? it would still be a nologin role so you can't directly connect to it and it would solve the issue of not having a password - is there something wrong with this approach?
it's granted only select + rls policies prevent it from fetching private dataextended-salmon•6mo ago
make the anonymous role have a password and fetch data through that?
it would still be a nologin role so you can't directly connect to itisn't it mutually exclusive? and actually
anonymous created by Neon RLS has a LOGIN postgres property, it's just have an empty password which prevents from logging innational-goldOP•6mo ago
hmm @mrl5 you were right on that one. how else can i get data as an anonymous user, if i don't want to fetch everything server side and keep it on the client?
and is there a difference between having NOLOGIN on it or not? like if a user just creates an account and then is
authenticated vs just querying on anonymous (security wise, assuming RLS is correct)extended-salmon•5mo ago
and is there a difference between having NOLOGIN on it or not?in our specific context - practically as long as you have empty password then you can't log in as this user anyway. theoretically you can check https://www.postgresql.org/docs/current/sql-createrole.html
national-goldOP•5mo ago
yeah i had to make it a standard LOGIN role, else it wouldn't work. i think it should be fine but i'll see i guess
extended-salmon•5mo ago
our backend bootstraps
authenticated and anonymous with empty password and LOGIN prop
if you'd like to connect as those roles with any postgres client (e.g. psql) it will be rejected
so in practice it's the same behavior as with NOLOGINnational-goldOP•5mo ago
yeah but i had to add a password to anon, else i wouldn't be able to use it - anonymous users don't have a JWT so there is no way for me to authenticate
extended-salmon•5mo ago
please remember about one of the previous posts from David:
David Gomes — 09/04/2025, 10:43 @oof2win2 it's not a good idea to connect directly to the database from the frontend
national-goldOP•5mo ago
yeah i know it's not due to no rate limiting being present as of now, but besides me rewriting all of my fetching logic i have no other way to do this