Can I do Kerberos delegation, ideally constrained, with SQLAlchemy?

245 views
Skip to first unread message

Dmitri Etkine

unread,
Feb 27, 2021, 3:22:55 PM2/27/21
to sqlalchemy
E.g. for MySQL? Using SQLAlchemy in a web app that will eventually run in K8s. Users would connect with their kerberos and service-specific ticket that I'd like SQLAlchemy to forward when establishing DB connection. Is it supported?

Mike Bayer

unread,
Feb 27, 2021, 6:48:18 PM2/27/21
to noreply-spamdigest via sqlalchemy


It's not really a normal pattern in a web application that database connections are user-specific.  This is usually too inefficient as it means you're connecting and disconnecting for every click.   Typically a web application uses a single password for the whole database, and then any kind of user-level security is done at a row level based on auth-related columns in the tables.

Otherwise, to connect to the database with kerberos you would have to resolve this with the database driver in question first.   From the SQLAlchemy side this just has to do with what parameters you pass to create_engine() that get sent through to the driver.   mysql/connector python, while the driver I prefer the least, might be the only one that supports kerberos auth.   But as for how to integrate this in a web application it would be a little bit awkard since you'd have to create_engine() for every web click and it will not be very efficient.




On Sat, Feb 27, 2021, at 3:22 PM, Dmitri Etkine wrote:
E.g. for MySQL? Using SQLAlchemy in a web app that will eventually run in K8s. Users would connect with their kerberos and service-specific ticket that I'd like SQLAlchemy to forward when establishing DB connection. Is it supported?


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Dmitri Etkine

unread,
Feb 27, 2021, 7:42:13 PM2/27/21
to sqlalchemy
Mike, thanks for quick reply! "Mysql/connector python" - it is "mysql+mysqldb://..." connectors, right? Will look into driver details
Several other points:
  - I don't see why create_engine() would have to be called on every request: connection pool of 1 connection maintained per user session, anything wrong with that? Would SQLAlchemy support that?
  - You are right, having single connection pool per data source shared between all webapp users is more efficient  
  - It would better be kerberos username + keytab  - really dislike clear-text passwords in config files (not a matter of dislike, actually - corporate policy)

Mike Bayer

unread,
Feb 28, 2021, 10:58:13 AM2/28/21
to noreply-spamdigest via sqlalchemy


On Sat, Feb 27, 2021, at 7:42 PM, Dmitri Etkine wrote:
Mike, thanks for quick reply! "Mysql/connector python" - it is "mysql+mysqldb://..." connectors, right? Will look into driver details




Several other points:
  - I don't see why create_engine() would have to be called on every request: connection pool of 1 connection maintained per user session, anything wrong with that? Would SQLAlchemy support that?

create_engine() creates one connection pool.   so that is one create_engine() per user.    SQLAlchemy is not optimized to be used in this way as each engine is a whole source of knowledge about a particular connection URL including statement caches.   having one per user is not scalable.

Also this would not be "per session", it's "per click", because in web applications "sessions" are not stateful parts of the program, it's not like a chat server where there's a persistent connection.    a "session" is only identified by state passed from the user's browser which you might never see again if they close their browser or leave the site.

  - You are right, having single connection pool per data source shared between all webapp users is more efficient  
  - It would better be kerberos username + keytab  - really dislike clear-text passwords in config files (not a matter of dislike, actually - corporate policy)

If you're writing a web service where users need to use kerberos auth to access the site, you should be implementing that in your web service.    take a look at https://flask-kerberos.readthedocs.io/en/latest/ which provides this for Flask web applications.


On Saturday, February 27, 2021 at 6:48:18 PM UTC-5 Mike Bayer wrote:



It's not really a normal pattern in a web application that database connections are user-specific.  This is usually too inefficient as it means you're connecting and disconnecting for every click.   Typically a web application uses a single password for the whole database, and then any kind of user-level security is done at a row level based on auth-related columns in the tables.

Otherwise, to connect to the database with kerberos you would have to resolve this with the database driver in question first.   From the SQLAlchemy side this just has to do with what parameters you pass to create_engine() that get sent through to the driver.   mysql/connector python, while the driver I prefer the least, might be the only one that supports kerberos auth.   But as for how to integrate this in a web application it would be a little bit awkard since you'd have to create_engine() for every web click and it will not be very efficient.




On Sat, Feb 27, 2021, at 3:22 PM, Dmitri Etkine wrote:
E.g. for MySQL? Using SQLAlchemy in a web app that will eventually run in K8s. Users would connect with their kerberos and service-specific ticket that I'd like SQLAlchemy to forward when establishing DB connection. Is it supported?


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.
Reply all
Reply to author
Forward
0 new messages