Getting back to this one - sorry, took me a while...
On Wed, Jan 15, 2020 at 10:02:26AM -0500, Stephen Frost wrote: ! Greetings Peter, Greetings back! ! ! * Peter (p...@citylink.dinoex.sub.org) wrote: ! > On Sun, Jan 12, 2020 at 05:58:56PM +0100, Peter wrote: ! > ! Stephen Frost (sfr...@snowman.net) wrote: ! > ! ! > ! ! That said, reminding myself that pgAdmin4 can be run under Apache, it ! > ! ! should be possible to have an Apache system set up with mod_auth_kerb ! > ! ! (to handle the incoming Kerberos authentication and the credential ! > ! ! delegation) and have pgAdmin4 pick up on the user as having been ! > ! ! authenticated via Kerberos thanks to environment variables provided by ! > ! ! Apache and, further, be able to connect to a downstream PostgreSQL ! > ! ! database using the delegated credentials thanks to mod_auth_kerb setting ! > ! ! up the KRB5CCACHE environment variable. ! > ! ! [...] ! > ! > ! So, since this quoted article is from quite a time back, may I kindly ! > ! ask for an update on the status of this matter, how it may have ! > ! proceeded in the meantime and what is currently considered best ! > ! practices in such a case of pure Krb5 operations? ! > ! > No answer, well then, it seems nobody interested whatsoever in this ! > matter. :( ! ! Interested, sure, but.. ! ! > Anyway, I made it working, so it works now. Multiuser, multithreading, ! > freestanding process behind a rig. ! ! Following the hints above, sounds like you were able to sort it out. Yeah. While not being straightaway viable, your thoughts were the necessary affirmation to push me forward to investigate deeper. ! Glad to hear it! Would love to have the specific details of what you ! did to make it work posted to this list or otherwise publicized, for ! others who are interested. Alright. 1. The KRB5CCACHE variable. pgadmin doc says this: "Some components of pgAdmin require the ability to maintain affinity between client sessions and a specific database connection [...] it requires that only a single Python process is used [etc.etc.]" This translates to: you cannot run it in a multi-process worker configuration. To get any parallelization, one must use threads. But: KRB5CCACHE is in the system env, and the system env is shared among threads. So even if we get this variable from the webserver to the pgadmin (which is also delicate, and might work only with "embedded" mode, but that one is discouraged on unix for the same reasons: apache uses some mixture of threads and processes), it will not simply work to have threads for different users running in parallel and use the proper credentials. 2. The kerberos module. For the apache I have two such modules: mod_auth_kerb2, which is said to be deprecated and replaced by mod_auth_gssapi. But, mod_auth_gssapi requires MIT kerberos, and my FreeBSD happens to have Heimdal integrated. Furthermore, the apache source/ABI/whatever is (imo) not a welcoming space for code modifications (had to do this once, don't want it again.) So, I had a look at the uwsgi rig, and there is something: https://uwsgi-docs.readthedocs.io/en/latest/SPNEGO.html https://uwsgi-docs.readthedocs.io/en/latest/Changelog-1.9.9.html#the-spnego-plugin As we can see, documentation is nonexistant, implementation is also somewhere between sparse and lacking - but that's good, because there is free space to do whatever we like - and I have an openssh that can do credential delegation, so I just took that code and pasted it in there (and made that working). So far is that part. Now back to the KRB5CCNAME problem: There seems to be no way to tell postgreSQL which credentials to use for connecting (when invoking the client - lib or executable), except this environment variable. (At least I found no other.) What can be done: let the web rig drop the creds into a file named by the principal that we got credentials for.(*) Then, within pgadmin4, we see the "REMOTE_USER" in the request variables set to the same name - so we know the filename. There seem to be only two places where pgadmin4 calls to postgres - one is for invocations of externals programs (i.e. forking), and that is simple because we can give a dedicated environment to these, and that should be safe. The other is a call to psycopg2.connect() - and I don't know what exactly that does. Anyway, I put the proper KRB5CCNAME into the environment before this call. Then I do a lock around that fork, and around that connect(), so that no two threads can run thru these regions at the same time. I assume this should be safe, and during invocations of postgres the KRB5CCNAME should always point to the credentials of the acting REMOTE_USER. Things I am not completely certain about: * I assume that the credentials are only used during the connect (as afterwards the connection is authenticated and they shouldn't have any use anymore for that connection). * There may be occasions when the database is connected independent of a user action from the web. I doubt that this is the case, because pgadmin would need to ask for the database password at that point, but I didn't analyze the code. In that case there would be no REMOTE_USER information (or a wrong one, which is worse). * There is a feature called "async", or PQconnectStart(). This seems to return *before* the connection is made, i.e. before the KRB5CCNAME from the environment is consumed. But at least for now it also seems that pgadmin does explicitely wait after this call until the connection is established, so that this wait can be included in the locked region. * There is a feature called pgAgent, which does some scheduled tasks, by whatever means. I didn't look into that, I have no idea how that would authenticate to the database, but it certainly does not work with my scheme. (*) I didn't like the idea of creating a file everytime; since this staye in the same process, it would be more appropriate to handover the cred in memory. But it would be difficult to pull them out of the kerberos library, and there seems no way to pass them on to postgres library. So I considered modifying this thing for the purpose: https://www.freebsd.org/cgi/man.cgi?query=kcm But I could not get that running even in its normal operation. So for now I do compare the received credential with the one in the file, and write only if they are different. So far, thats the storyline. Enjoy. cheerio, PMc