Re: [sqlalchemy] Using connection pools with individual PostgreSQL user logins for a backend REST API

[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






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
>  
> .

-- 
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.

[sqlalchemy] 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.

-- 
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.