Re: [SOLVED] Re: pgAdmin 4 + python wheel + kerberos - Mailing list pgadmin-support

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 (pmc@citylink.dinoex.sub.org) wrote:
! > On Sun, Jan 12, 2020 at 05:58:56PM +0100, Peter wrote:
! > ! Stephen Frost (sfrost@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