3 ms·

Not sure that I would do authentication directly in PG (although it has many different methods [0]), I think it's still doable. You could move whole oAuth to PG

by doh 9y ago

Not sure that I would do authentication directly in PG (although it has many different methods [0]), I think it's still doable. You could move whole oAuth to PG through PL/SQL (maybe there is an extension for it?). I would also encourage to introduce a proxy in between (e.g. pgbouncer [1]) to help you to handle connections and deal with the authentication. We're using PG as "the brain" of our service since 2014 and it never failed us. The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. However once you have functional logic, you can write fairly lightweight application layer around it and change it as often as you want. You can also scale this setup almost indefinitely thanks to extension like citus [2] and still keep your application layer fairly thin. [0] https://www.postgresql.org/docs/current/static/auth-methods.html https://www.postgresql.org/docs/current/static/auth-methods.... [1] https://pgbouncer.github.io https://pgbouncer.github.io [2] https://www.citusdata.com https://www.citusdata.com

deleted 9y ago
[deleted]
combatentropy 9y ago
> Not sure that I would do authentication directly in PG I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache. db.exec('set role to $1', req.remote_user); Apache 2.4's form-based authentication makes this attractive. > The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. Isn't there the explain command (https://www.postgresql.org/docs/current/static/using-explain.html https://www.postgresql.org/docs/current/static/using-explain...) and the \timing option for the command-line client, psql (https://www.postgresql.org/docs/10/static/app-psql.html https://www.postgresql.org/docs/10/static/app-psql.html)?
- doh 9y ago
  > I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache. I figured. If you isolate the data well enough, it could work. I'm always paranoid when it comes to DB. > Isn't there the explain command and the \timing option for the command-line client, psql? Yes, there is. This however doesn't help you with triggers and UDF, which is how you usually create the logic in the DB.

by doh 9y ago

deleted 9y ago

[deleted]

combatentropy 9y ago

> Not sure that I would do authentication directly in PG I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache. db.exec('set role to $1', req.remote_user); Apache 2.4's form-based authentication makes this attractive. > The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. Isn't there the explain command (https://www.postgresql.org/docs/current/static/using-explain.html https://www.postgresql.org/docs/current/static/using-explain...) and the \timing option for the command-line client, psql (https://www.postgresql.org/docs/10/static/app-psql.html https://www.postgresql.org/docs/10/static/app-psql.html)?

doh 9y ago
> I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache. I figured. If you isolate the data well enough, it could work. I'm always paranoid when it comes to DB. > Isn't there the explain command and the \timing option for the command-line client, psql? Yes, there is. This however doesn't help you with triggers and UDF, which is how you usually create the logic in the DB.