Using connection pools with individual PostgreSQL user logins for a backend REST API

[Nate J]

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.

[Mike Bayer]

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

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/DCF5536D-71FD-46BA-B831-0755588A79DD%40gmail.com.

[Nate J]

Thanks Mike! That’s a great idea. Yes indeed, that helps a lot. I really appreciate it.

Thanks again!

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b0a3a566-4e69-42d0-a24a-d8139dff950d%40app.fastmail.com.