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

(2014/07/09 11:53), Craig Ringer wrote:
> (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.

It also occurs with certificate or ident authentication methods.
It also occurs in case of ssl connections with sslmode=verify-[ca|full
.
One way is to reject such connections at enlistment.
One way is to register such DSNs as *users' manual recovery is
required*.
I'd like to test another way to use CreateProcessWithLogonW() with
SSPI authentication method but it's not easy to setup the environmemt.

regards,
Hiroshi Inoue

--
I am using the free version of SPAMfighter.
SPAMfighter has removed 11479 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen