Impossible to use MSDTC/XA with SSPI - Mailing list pgsql-odbc

(Sent from the wrong account earlier, please disregard if you see
another copy)

Hi all

It doesn't seem to be possible to use MSDTC XA transactions alongside
SSPI authentication.

psqlODBC's MSDTC support assumes that the DSN supplied by the user is
also valid for a connection from MSDTC.exe via pgxalib.dll for XA
recovery. We've already seen one case where that's not a valid
assumption - where the 32-bit and 64-bit driver names differ when using
32-bit psqlODBC under SysWow64 on 64-bit Windows.

This is another case.

SSPI for TCP is like "peer" for unix socket connections on UNIX, except
that it uses an in-band handshake protocol rather than out-of-band
syscalls. It verifies that the connecting user is running as same
operating system user as the PostgreSQL username they've requested (or
is allowed to connect as that Pg user by the user mappings).

So what happens is:

- psqlODBC connects as the user
- User starts distributed tx manager, binds connection(s) to MSDTC
- User starts distributed tx; psqlODBC starts tx's on each session and
saves the user's DSN in the DTC transaction context.
- User does work on each session
- User asks DTC to commit tx; psqlODBC does a PREPARE TRANSACTION on
each session
- DTC tells user tx has committed successfully
- DTC asks psqlODBC to COMMIT PREPARED on each tx

If the app exits after MSDTC tells it the tx committed (which it might -
MSDTC doesn't offer synchronous 2-phase commit, it can only wait for the
first phase to complete) or the server crashes, etc, there are left-over
prepared transactions.

pgxalib.dll's job is to recover those. To do that it has to connect to
PostgreSQL. It's loaded within MSDTC.exe, where it establishes a
psqlODBC connection using the DSN it saved in the distributed
transaction context earlier.

This works only if the DSN is valid. The 32-bit to 64-bit mismatch was
one case of this being a problem earlier. SSPI is another - when
MSDTC.exe tries to connect to PostgreSQL it does so under Windows user
NETWORKSERVICE, which won't match the username the original user
connected to PostgreSQL as. So PostgreSQL will reject the connection.

I'm not sure how to solve this. "Use libpq" isn't an answer here, it
still wouldn't give us any way to acquire creds that'd permit connecting
safely.

Stashing creds in the Registry or a file would work, but would require a
.pgpass-like setup with matching for hostname/port/etc, since MSDTC
might be co-ordinating transactions for multiple Pg servers on multiple
machines. It'd also mean that MSDTC XA support for Pg would require
configuration, which it never has before. Anything else running as
NETWORKSERVICE could then steal these creds. Because you can only COMMIT
PREPARED a tx that you created unless you're superuser, they'd have to
be superuser creds too.

Trusting NETWORKSERVICE to SSPI-authenticate as anybody seems pretty
horrible - if Pg even has wildcard user mapping for SSPI that'll work
for this, I haven't checked yet. Anything could be running as
NETWORKSERVICE.

The only thing I can think of that seems secure in the face of other
things running as NETWORKSERVICE is to have a special cookie stored in
the DTC transaction context that PostgreSQL accepts as a one-time
password for a connection that can *only* commit or rollback that
particular prepared transaction. That'd require some pretty significant
changes to Pg core, so it's hardly a lightweight option.

Ideas?

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services