Neon RLS and Clerk Authorization
I'm looking for some docs that explain how my clerk user is recognized by postgres? Aside from comparing a user_id from a token to a table field value, what if I just want to define read access to a role? Or what if I want to create a new role and use that to secure read access to a table? I feel like the end to end explanation is not documented anywhere that I have seen. At this point I have to resort to assumptions but it's not the best way to evaluate something I will be spending time and money on in the near future.
My Assumptions are:
- When I create a user in Clerk, I also have to create the same user in postgres via sql script? I'll assume I can use a webhook on the Clerk side to call an API on my side to handle this script execution.
- The roles for this user also must be managed within postgres and possibly synchronized with Clerk user metadata so that they are available in the front end app via session token. I'll assume that this is what the Clerk APIs are for as well.
- Somehow, when accessing the postgres db with Neon, postgres knows what user I am based on the token from Clerk and can enforce data/schema security.
- - This is where the black box for me is. How does this work? When I create a user in Clerk, is the user_id "user_2qtqzh0iCtQWckXTMjE0zDcDaJi" from that system used to create the user within my postgres db?
- - To make matters more complicated, if I connect to my Neon db via connection string "postgresql://authenticated@my-project.us-west-2.aws.neon.tech/myDB?sslmode=require" does my new Clerk user need to be granted access to this role "authenticated"?
- - I have never implemented RLS so my next issue is just lack of experience but... What if I have several roles defined in my schema, I would not want to manage several connections in my app code. What is the proper way to connect to Neon within application code when your schema has many roles and any given user could have multiple roles.
My Assumptions are:
- When I create a user in Clerk, I also have to create the same user in postgres via sql script? I'll assume I can use a webhook on the Clerk side to call an API on my side to handle this script execution.
- The roles for this user also must be managed within postgres and possibly synchronized with Clerk user metadata so that they are available in the front end app via session token. I'll assume that this is what the Clerk APIs are for as well.
- Somehow, when accessing the postgres db with Neon, postgres knows what user I am based on the token from Clerk and can enforce data/schema security.
- - This is where the black box for me is. How does this work? When I create a user in Clerk, is the user_id "user_2qtqzh0iCtQWckXTMjE0zDcDaJi" from that system used to create the user within my postgres db?
- - To make matters more complicated, if I connect to my Neon db via connection string "postgresql://authenticated@my-project.us-west-2.aws.neon.tech/myDB?sslmode=require" does my new Clerk user need to be granted access to this role "authenticated"?
- - I have never implemented RLS so my next issue is just lack of experience but... What if I have several roles defined in my schema, I would not want to manage several connections in my app code. What is the proper way to connect to Neon within application code when your schema has many roles and any given user could have multiple roles.
