Re:PostgREST – Serve a RESTful API from Any Postgres Database
by SoftTalker on Thursday December 29, 2022 @04:16PM
(#34178059)
The nice thing about this approach is that if you ever develop other applications or front-ends to this database, even in some other framework, the user auth and access rules are already there.
It's been a while but does postgres let you re-auth on an existing connection? I was able to do that in Oracle and that allowed the use of connection pools rather than one connection per user, which can become a bottleneck.
Re:PostgREST – Serve a RESTful API from Any Postgres Database
by timando on Thursday December 29, 2022 @05:19PM
(#34178823)
You can use SET ROLE to become another user you have permission to become (I think you need to be in the same group or something) and RESET ROLE to go back to your original user. If the initial connection was made with a superuser or whatever, you could change to the user in the connection manager and reset when the connection goes back in the pool.
I am not sure if I would do this on something serious, but it was a lot of fun to set up and saved me a lot of code by reusing the comprehensive auth system that lives with the actual data in question.