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 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 @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 > > 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/ > > 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/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/ 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/b0a3a566-4e69-42d0-a24a-d8139dff950d%40app.fastmail.com.
