Обсуждение: Impossible to use MSDTC/XA with SSPI
(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
(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
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