3 ms·

I've made some automatic schema dumping scripts for supabase/postgres code to become searchable and git-diffable and PR-friendly. And I eventually was able to m

I've made some automatic schema dumping scripts for supabase/postgres code to become searchable and git-diffable and PR-friendly. And I eventually was able to make unit testing to work good enough that I even wrote migrations in a test-first way, it was just quicker to iterate. But overall experience felt like I'm constantly combating problems, solved looong time ago for other languages and ecosystems. It was weirdly fun, but what killed my interest is that row level security policies kill performance of even simple queries so much, and EXPLAIN doesn't help to well with it.

nextaccountic 3y ago
Is it on github?
- codesnik 3y ago
  no. it was a private project, and my solution wasn't general enough. for functions/views/etc: I've used https://github.com/omniti-labs/pg_extractor https://github.com/omniti-labs/pg_extractor then removed roles from dump (because the fluctuated between developers) and executed perl -000 -i -lne 'print if ! /^--/ && ! /^SET /' `find schema/ -name '*.sql'` || exit 1 to remove a lot of useless comments and flags from the dump (pg_dump output isn't too readable). This oneliner can strip too much, though, comments in functions shouldn't start from the 0 column. The same script had been run on CI too, to verify that developer didn't forget to run it in the PR.
nextaccountic 3y ago
> what killed my interest is that row level security policies kill performance of even simple queries so much That's shocking to hear Do you feel that doing access control outside the db is faster overall? (considering it most likely involves more round trips into the db)
ttfkam 3y ago
Had the opposite experience. Row-level policies had minimal overhead (<5%?) but improved security at the app layer tremendously. It was intensely satisfying to have cases where UI developers were complaining that data was missing. Turned out the access tags were wrong, and the data as tagged shouldn't have been visible in the first place. App had set the wrong tags but turns out would have happily returned the data if the policies hadn't been there. Apps forget that extra AND on the WHERE clause *all the time*. Just one ad hoc script querying the database can ruin your whole security-oriented day. Do policies make schema design slower? Yes. Do they make queries slower? Not in my experience, but that may be due to our familiarity with Postgres and its planner. Do they basically eliminate data leaks to the end user? Absolutely. DB policies to me are like Rust vs C++. Someone maybe able to write C++ faster and with less training, but having those extra checks at the outset can save so much time and heartache down the road. It's an investment, not a cost.

nextaccountic 3y ago

Is it on github?

codesnik 3y ago
no. it was a private project, and my solution wasn't general enough. for functions/views/etc: I've used https://github.com/omniti-labs/pg_extractor https://github.com/omniti-labs/pg_extractor then removed roles from dump (because the fluctuated between developers) and executed perl -000 -i -lne 'print if ! /^--/ && ! /^SET /' `find schema/ -name '*.sql'` || exit 1 to remove a lot of useless comments and flags from the dump (pg_dump output isn't too readable). This oneliner can strip too much, though, comments in functions shouldn't start from the 0 column. The same script had been run on CI too, to verify that developer didn't forget to run it in the PR.

nextaccountic 3y ago

> what killed my interest is that row level security policies kill performance of even simple queries so much That's shocking to hear Do you feel that doing access control outside the db is faster overall? (considering it most likely involves more round trips into the db)

ttfkam 3y ago

Had the opposite experience. Row-level policies had minimal overhead (<5%?) but improved security at the app layer tremendously. It was intensely satisfying to have cases where UI developers were complaining that data was missing. Turned out the access tags were wrong, and the data as tagged shouldn't have been visible in the first place. App had set the wrong tags but turns out would have happily returned the data if the policies hadn't been there. Apps forget that extra AND on the WHERE clause *all the time*. Just one ad hoc script querying the database can ruin your whole security-oriented day. Do policies make schema design slower? Yes. Do they make queries slower? Not in my experience, but that may be due to our familiarity with Postgres and its planner. Do they basically eliminate data leaks to the end user? Absolutely. DB policies to me are like Rust vs C++. Someone maybe able to write C++ faster and with less training, but having those extra checks at the outset can save so much time and heartache down the road. It's an investment, not a cost.