5 ms·

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

A nice list but I'd also add row-level security (RLS): https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies 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.

paulryanrogers 7y ago
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.
- tellak 7y ago
  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.
usgroup 7y ago
Couldn’t agree more ... row level security is brilliantly simple too. Access defined by query.
natmaka 7y ago
> 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 http://postgrest.org
- everdev 7y ago
  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.
- BerislavLopac 7y ago
  I've also been a happy user of Sandman: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
  - natmaka 7y ago
    There is a list at https://github.com/dbohdan/automatic-api/ https://github.com/dbohdan/automatic-api/
- paulddraper 7y ago
  Check out https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/

paulryanrogers 7y ago

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.

tellak 7y ago
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.

usgroup 7y ago

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

natmaka 7y ago

> 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 http://postgrest.org

everdev 7y ago
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.
BerislavLopac 7y ago
I've also been a happy user of Sandman: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
- natmaka 7y ago
  There is a list at https://github.com/dbohdan/automatic-api/ https://github.com/dbohdan/automatic-api/
paulddraper 7y ago
Check out https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/