once you enable RLS for a table, you need re-enable access to what is allowed using policy.

authed users would only be able to select from that table if you add a policy, at least this is how it has worked for me when enabling RLS

ahh, so even with no policies defined, authed users can still select 🤔

no I'm saying they can't unless you add a policy

oooh I got you

thanks!

So if you want to make selects public but turn everthing else off, you enable RLS and then add a policy to make selects public.

as an example

I have some examples of procedures and policies for a voting app here https://github.com/jensen/vote-now/blob/main/src/services/schema/tables.sql

awesome, will check out

I wonder if you disagree, but, I've arrived at the conclusion that RLS policies are not flexible /powerful enough for large & serious production apps

and writing to DB from frontend in general

I'm still working on my conclusion there.

simple example = needing very advanced validation on what data is being inserted into a row

beyond type checks, etc...

Have you worked with PL/pgsql at all?

seems like there's always gonna be some edge case that breaks it

nope 🙂

So it seems that for what you are talking about, a lot of this works is pushed into that layer.

I can hide complexity behind rpc if I'm willing to write that in SQL (which I am)

I'm willing to write SQL, just not sure I want to use rpcs.... lol

ahh, looks interesting, might explore

It's worth it, even if the result is you know more SQL. SQL isn't going anywhere

I mean, you would need to run those checks on your express server otherwise - Postgres will run them for you if you configure it right. The thing which is a bigger conceptual smell for me is the need for partial tables (profiles and profiles_private)

Yeah, this is unfortunate. Without column security decisions are being made for me as to how I structure my relational db, and that is not exciting

Although I would need to find a better example of that not working, because the profiles/profiles_private hasn't resulted in the headache I expected

Yeah, you need to base your DB schema on access control first and normalization second, which can create complications in the future (new access control rules = changes in schema and data migration).

exactly the feeling i get

but I'm not against finding out what that means, since it's been pretty common to hide behind express, rails etc up until now

It's almost sounds like that versioning your tables isn't the worst idea v1_profiles, v2_profiles.. - the equivalent of REST /v1/profiles, /v2/profiles. Keeping them in sync through triggers... just the idea, because you will need to remove the old "access point" in the future, but you don't want to remove it immediately because it could break active client apps (web, and mainly mobile)