Re: msdtc with 32-bit app fails to resolve in-doubt or not-notifed transactions

On 06/23/2014 08:34 AM, Inoue, Hiroshi wrote:
> (2014/06/21 17:02), Craig Ringer wrote:
>> On 06/21/2014 12:52 PM, Craig Ringer wrote:
>>>
>>>> Oops, it's my mistake. It may be better to rewrite the code completely
>>>> using libpq APIs instead of ODBC APIs.
>>>
>>> I don't know if that'll work.
>
> The driver doesn't pass original connection strings to XARMCreate().
> It seems possible to pass LIBPQ connection strings.

Ah, I misunderstood that then.

Great, that makes life easier.

>> If the DSN is using SSPI, MSDTC.exe probably won't be able to
>> authenticate to the DB as them. Same with any other kind of connection
>> that depends on the properties of the current user account.
>
> Yes it's a severe problem.
> I'll try to check the good way to do it.

Thanks for thinking about it.

>> Where possible I'd like to find ways to produce useful errors though,
>> preferably at transaction enlistment time rather than commit phase II.
>> Suggestions would be valued.
>
> It's possible to reject transaction enlistments at XARMCreate() though
> I'm not sure if it's possible to tell the cause clearly to users.

That'll be handy. As for telling users, we can always emit a message to
the Windows Event Log. Competent admins should be looking there anyway.


However, AFAIK we have no way to find out in advance if a connstring or
DSN passed by the driver to msdtc for use in pgxalib will actually be
usable - in particular we have no way to query `pg_hba.conf` to see (a)
how our current connection was authenticated and (b) whether the same
connection settings would apply for the pgxalib connection. For example,
I don't think there's any way to tell if our current user/host/db combo
has a special cased 'trust' entry in pg_hba.conf that means other combos
will require a password.

So I don't think there'll be any robust solution to this that doesn't
involve just documenting it away to some degree.

I suspect we'll land up wanting a multi-part solution:

- Use libpq in pgxalib and pass libpq connstrings generated from the dsn
by the odbc driver during tx enlistment to bypass the driver name issue;

- Provide a configuration option (an odbcinst.ini registry key?) to
allow the operator to override the driver-generated dsn/connstr used by
pgxalib with one the user knows will allow superuser connections to the
DB. If it exists this is used instead of any driver-generated connstring
for 2PC.

- Provide an ODBC parameter to set an escaped connection string for use
in XA management (?). Alternative to / override for the above registry
setting. This probably isn't necessary, as apps already have to set a
few registry keys to make XA work with MSDTC at all.

- Document XA configuration, noting that if SSPI is in use a connstring
*must* be configured. A user should be created for the purpose of XA 2PC
management and given precedence above SSPI connections in pg_hba.conf;
otherwise if SSPI works at all for NETWORKSERVICE you'd be giving every
NETWORKSERVICE app potential access to the db(s), which would be
undesirable.


I'm happy to write the required documentation, not least because I need
to document how to configure pgxalib with msdtc anyway - adding the
missing registry key for older versions, enabling XA support in msdtc in
component services, where to find the logs, how to enable msdtc's
tracing, how to enable pgxalib's tracing (if I can figure that out), how
to monitor for incomplete 2PC transactions, etc.

How *does* one correctly enable pgxalib tracing? I tried creating a
registry key "PostgreSQL" under "odbcinst.ini" in
HKLM\SOFTWARE\ODBC\ODBCINST.INI and adding a String value "Msdtclog"
with content '1' (no quotes). The key didn't seem to get found by
SQLGetPrivateProfileString though. So I'm not sure what the right way to
turn it on is.

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