Re: Impossible to use MSDTC/XA with SSPI

On 07/09/2014 12:00 PM, Inoue, Hiroshi wrote:
> It also occurs with certificate or ident authentication methods.
> It also occurs in case of ssl connections with sslmode=verify-[ca|full

Oh, delightful. Yes.

It's also a problem if they're using a User DSN, as the DSN won't be
defined in the HKEY_CURRENT_USER hive for NETWORKSERVICE and MSDTC won't
find it.

> One way is to reject such connections at enlistment.

I think that's going to be necessary at some point, no matter what.
There are just too many corner cases and there's too much room for
misconfiguration.

People who use XA transactions don't tend to like "Well, maybe it
worked". So rejecting it outright is a much better option.

The only question there is whether it's possible or sane to use a single
connection for each DSN, maintaining a connection pool. So we aren't
always disconnecting and reconnecting, and we aren't connecting once at
enlistment then again at recovery.

> One way is to register such DSNs as *users' manual recovery is
> required*.

Strong -1 for that one - it's pretty much what we have already, and it's
something people will only discover when something breaks. Then they'll
have to go chasing the problem. MSDTC isn't exactly a highly visible
component either.

> I'd like to test another way to use CreateProcessWithLogonW() with
> SSPI authentication method

Interesting for SSPI, or for file-based certificate access, but it
requires NETWORKSERVICE to have the right to create a process as any
user or know the user's credentials. Actually, I think it can only work
if you know the user's credentials - there doesn't seem to be anything
like the sudo/setuid/etc functionality where you don't know the target
account's password. I guess that makes sense when considering domains
and trust, but it means it can't be made transparent to psqlODBC users,
they'd have to put their user account password as an extra attribute the
DSN.

It might also be a performance problem. We'd have to LOGON_WITH_PROFILE
so we could access HKCU (in case there are any User DSNs we need), which
is documented as being slow.

So we'd probably need a persistent helper for each user that proxies XA
connections for that user. That's starting to get complicated (and
likely to cause profile unload problems).

MSDTC isn't pretty, is it?

I think it'll be better to just reject certain configurations as
unsupported at enlistment time:

- Any user DSNs

- Certificate based auth where MSDTC can't access the certificate or
  it's a relative path

- SSPI auth to a domain account where no pg_ident.conf mapping permits
  msdtc to log in as that user and no fallback account name has been
  provided for msdtc to use;

- ... etc

Essentially, we test the DSN at enlistment and reject it loudly (with a
nice informative message in the Windows Error Log) if it isn't usable as
an XA connection.


> but it's not easy to setup the environmemt.

I'm testing on a single Win2k8 server VM, running on AWS, using SSPI to
localhost.

If you like I can tidy it a little and wrap it as an AMI, so you can
launch one with MSVC, git, psqlodbc, WiX, etc etc already in place.

Otherwise I'm happy to carry on testing this myself - I have no right to
ask you to do my work, after all.

I'd appreciate advice on how to reject connections at enlistment time,
or more specifically how to pool and cache the connections so each
enlistment doesn't make a new connection. Otherwise I think I can see
where to go from here.

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