Thanks Mike! That’s a great idea. Yes indeed, that helps a lot. I really appreciate it.
Thanks again! > On Feb 26, 2023, at 11:53 PM, Mike Bayer > <mike_not_on_goo...@zzzcomputing.com> wrote: > > I think you should use a single master login for create_engine() so that the > pool deals with connections only at that top role. > > Then when you **use** connections, use PostgreSQL SET ROLE: > https://www.postgresql.org/docs/current/sql-set-role.html > <https://www.postgresql.org/docs/current/sql-set-role.html> to set the > current role on the connection / session you are using, and RESET ROLE to > turn it back before connections are returned to the pool. > > When you start a request, set the role: > > await session.connection().exec_driver_sql("SET ROLE current_user") > > Then, assuming you are using SET ROLE and not SET LOCAL ROLE, you want to > reset this role upon return to the pool. Establish a pool return handler > that calls RESET ROLE using the "checkin" event: > https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.PoolEvents.checkin > > <https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.PoolEvents.checkin> > > > @event.listens_for(engine, "checkin") > def receive_checkin(dbapi_connection, connection_record): > cursor = dbapi_connection.cursor() > cursor.execute("RESET ROLE") > cursor.close() > > > > hope this helps > > > > > > > On Sun, Feb 26, 2023, at 11:10 PM, Nate J wrote: >> Hi List, >> >> I have no Google-fu. This is especially obvious when trying to search for >> something like I’m asking about. >> >> Does anyone have any info or links to docs, blog posts, etc. that covers how >> to use connection pools with individual PostgreSQL user logins for a backend >> REST API? >> >> The reason I ask is I’m using row level security (RLS) like in the example >> below from the PostgreSQL docs. I’ve only ever used a single >> application-specific account for accessing the database for all tenants. >> Now, however, each API key used in an API request will be tied to an account >> table and will have a PostgreSQL database user created for them so >> “current_user” works for RLS. >> >> I’d like for requests to complete as fast as possible and I’m guessing >> there’s a better way than logging in to the database for each request. >> >> The software involved is PostgreSQL 14, SQLAlchemy with asyncpg and FastAPI. >> >> From the page: https://www.postgresql.org/docs/current/ddl-rowsecurity.html >> <https://www.postgresql.org/docs/current/ddl-rowsecurity.html> >> >> One example uses the DDL and policy: >> >> CREATE TABLE accounts (manager text, company text, contact_email text); >> >> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; >> >> CREATE POLICY account_managers ON accounts TO managers >> USING (manager = current_user); >> >> Thanks for your help. >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve >> <http://stackoverflow.com/help/mcve> for a full description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/DCF5536D-71FD-46BA-B831-0755588A79DD%40gmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/DCF5536D-71FD-46BA-B831-0755588A79DD%40gmail.com?utm_medium=email&utm_source=footer>. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve > <http://stackoverflow.com/help/mcve> for a full description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/b0a3a566-4e69-42d0-a24a-d8139dff950d%40app.fastmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/b0a3a566-4e69-42d0-a24a-d8139dff950d%40app.fastmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/30CACE00-92BC-47D9-A4E3-B9CB0454C468%40gmail.com.
