An often requested feature for users migrating from Microsoft SQL Server to PostgreSQL is being able to use what Microsoft calls "Integrated Security". In a Microsoft environment, this means to automatically use your Windows account to log in to the database. It does not mean to use the same password. It means the login has to be transparent. (I believe at least one of the other major databases can do this already, but most requests come from MSSQL users)
Prior to PostgreSQL 8.3, this was possible to do using Kerberos 5 authentication, but this only worked if your PostgreSQL server was running on Unix (clients could be Unix or Windows). PostgreSQL 8.3 brings this to the native Windows version, and also makes things a lot easier to configure and use in a pure Windows environment. The core of the functionality for this is the addition of GSSAPI and SSPI authentication. GSSAPI is an open standard for authentication, that is typically used for Kerberos authentication. SSPI is a Microsoft implementation of this, that is wire compatible with GSSAPI (it's not API compatible, unfortunately). Native Kerberos 5 will still be supported in 8.3 for backwards compatibility, but it's deprecated in favor of GSSAPI in all installations that don't need to be compatible with 8.2 or earlier.
PostgreSQL 8.3 adds support for both these authentication methods. GSSAPI is supported on both Unix and Windows, and SSPI is supported only on Windows. The Windows build will always build with SSPI support. GSSAPI support requires the MIT Kerberos for Windows package to build (the same library used for the krb5 authentication used in pre-8.3 versions). The binary distribution will be shipped with GSSAPI enabled, but if you do your own build from source it's off by default.
There are a couple of ways to use this:
To achieve the same functionality as in pre-8.3 but using GSSAPI instead, configure the Linux server the exact same way as for Kerberos authentication. In pg_hba.conf, specify authentication method gss instead of krb5. Unix clients with GSSAPI support and Windows client with GSSAPI or SSPI support will then be able to authenticate. Note that a Windows SSPI client will be able to authenticate to the Unix server without needing the GSSAPI library.
To run the server on Windows and be able to authenticate to it from both Windows and Unix (or just Unix), configure things the same way as above. This setup requires the GSSAPI library to be linked in on the server.
To run the server on Windows and all clients on Windows, it's trivial to set up. In pg_hba.conf on the server, just specify sspi as authentication method, and you're done. All users connecting from the local machine, your domain, or a trusted domain will be automatically authenticated using the SSPI configured authentication (you can enable/disable things like NTLMv2 or LM using Group Policy - it's a Windows configuration, not a PostgreSQL one). You still need to create the login role in PostgreSQL, but that's it. Note that the domain is not verified at all, only the username. So the user Administrator in your primary and a trusted domain will be considered the same user if they try to connect to PostgreSQL. Note that this method is not compatible with Unix clients.
In almost no case do you actually need to configure and use the MIT Kerberos library on the client. The only case when this is needed is if you specifically don't want to use the Active Directory login, but instead use a standalone Kerberos login based on MIT Kerberos. If you need this, specify the connection parameter gsslib=gssapi (or use the environment variable PGGSSLIB). Otherwise, the client will default to using the SSPI library.
New comments can no longer be posted on this entry.
when you use sspi do you still new to create all AD user accounts in postgres