Hacker News Reader

Comment by everdev | Hacker News Reader

A nice list but I'd also add row-level security (RLS): https://info.crunchydata.com/blog/a-postgresql-row-level-sec...

It's amazing how much effort we put into restricting access on the server, but ignore user roles on the data layer.

IME at scale you need a connection pool. Pools usually cache by connection credentials. And maintaining a large variety of roles with disparate access levels is cumbersome. (I've tried.)

The end result is that column, much less row, level controls end up unused in most cases.

Very security conscious places may just use stored procedures for everything, and limit access to those.

Note that you don’t have to use the connection credentials to make use of row level security in pg. the security policy can be based on session variables (or anything really) which can be changed during the connection.

Edit: it’s not the same thing but fwiw pg row level security is completely decoupled from the dbms access control.

> It's amazing how much effort we put into restricting access on the server, but ignore user roles on the data layer.

That's one of the reasons PostgREST seems promising to me. http://postgrest.org

I've looked into it as well and it looks really interesting. The biggest downside for me was that at some point I still needed a backend server to hold secrets or run business logic. At that point, Postgresql was just another server / dependency that I couldn't justify as all of the nifty RLS was done inside Postgres.

It definitely introduced me to RLS though.

I've also been a happy user of Sandman: https://github.com/jeffknupp/sandman2

There is a list at https://github.com/dbohdan/automatic-api/

Check out

https://www.graphile.org/postgraphile/

Couldn’t agree more ... row level security is brilliantly simple too. Access defined by query.

Reply on news.ycombinator.com