Hacker News Reader

Comment by cel1ne | Hacker News Reader

I do use PostgreSQL wherever possibly. Add http://postgrest.com/ and nginx as a url-rewriting proxy and you have a performant, highly adaptable REST-Server.

View on HN

Nice, Postgrest looks really amazing, especially for long-term production needs. I've previously used Sandman, which is much easier to set up, which makes it ideal for more quick-and-dirty needs: https://github.com/jeffknupp/sandman2

Me too, never failed me for the things I use it for. Unlike other data stores, it has predictable, consistent performance, production ready releases, and reasonable opensource contributors who really deliver the awesomeness. Its architecture is unique, at least in the FOSS word.

I looked at Postgrest and liked what I saw, until it came to authentication and authorization - the docs mention that this is an area that tends to blur the line between application and database layer, and then just sort of punts on the whole thing, suggesting that maybe you want to use the auth0 SaaS. Have you had to deal with this?

Yes, we found the same issue. We worked around this by organizing our application (an admittedly basic REST API) in two layers. The external one exposed to API consumers, developed in a language/framework you may like (Python/Tornado for us), and the internal data access layer which uses Postgrest, and is only accessed locally from within the REST API application servers

Why not just query the database using standard drivers then ? What am I missing here ?

Maybe the the exposed application acts like a proxy for the internal apis, only check authentication info and pass on the request to the postgrest servers.

I know the docs are lacking but have you guys looked into how roles + RLS + views can work together to solve authorization?

Yes the docs are lacking but the hope is that the users will get the basic idea and look into things like roles+RLS in the official Postgre docs (although those are lacking too :) it's a very powerful feature, this RLS thing, but since it's new, there are not a lot of examples explaining it)

That is of course until someone finds the time to write a long article/tutorial explaining how all the pieces fit together and how PG 90% of the time can solve all your authorization needs (in regard to the data it stores itself)

It's more than just roles though - it's validating email addresses at signup, resetting passwords... annoying to build yourself and easy to screw up.

In the end I went with Django Rest Framework.

Validating email is a CHECK constraint using a regexp on the email filed, resetting password is probably two short stored procedures. I get that you don't usually find code to copy/paste for something like this but it's not hard to implement either (i would say the whole signup/login/reset flow can be done in 100-150 LOC). Out of curiosity, how much time did it take to implement the API you needed?

A general rule of thumb is that Identity and Access Management aren't businesses you want to accidentally get into. Yes, you can write IAM code yourself, but at scale it's a way better plan to trust a dedicated IdP.

Is there a way to have combined permissions, based on both row and column?

For example, considering the `passwd` table example https://www.postgresql.org/docs/9.6/static/ddl-rowsecurity.h..., allowing user to select their own pwhash, but no other users' pwhashes.

I find owner-dependent field-level permissions to be a fairly frequent requirement.

I know of some relatively high end systems which are doing that, which used postgrest for prototyping purposes and then found it performed too well to give up!

Ha :)! can you maybe give some links?

I think internally the upcoming Kosovo property tax management system will use it.

This is one of those things you frame and put on the wall :)

Reply on news.ycombinator.com