|[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]|
On 06/16/2012 08:36 PM, Christian Ullrich wrote:
* Peter Cheung wrote:I’m new to PostgreSQL. I installed PostgreSQL on a Windows Server 2008R2 server. I have created a database and an user in Windows Active Directory. How can I configure that user to access that database?The one-click installer (assuming you used that) left you with PostgreSQL running under a local account named "postgres". First, you have to change that, because SSPI requires that the service uses a domain account:
That's a great explanation. I didn't see anything equivalent in the docs - am I just blind?
If not documented anywhere I'd like to add that to the wiki.
1. Create a user account in your domain. 2. Change the ownership of the data directory and everything within it to the new account, and grant it full control. 3. Change the service log on credentials so the service uses your domain account. 4. Start the service to see if everything works. Try logging on as before, create a database, drop some tables, call pg_switch_xlog(). If you can log on at all, just about anything that goes wrong later indicates missing permissions on the data files.Now, you have to tell Active Directory that your service account is running the database. For that, you add a Service Principal Name to your service account. You can do that with a command line tool named setspn.exe, of which I cannot remember the command line. You can also just change the attribute (servicePrincipalName) directly using either the "Users and Computers" MMC, or whatever 2008R2's replacement for that is, or ADSIedit. Anyway, your new SPN isPOSTGRES/fully.qualified.host.nameIn my experience (which may be incomplete), you also have to make sure that all your clients use the full host name, because otherwise they may not get service tickets. Adding a second SPN with just the host name without the domain may help with that, but using the full name is better anyway.The last step is to allow SSPI logon to the database. For that, you need to create some login roles that have the same name as your domain users, and an entry in pg_hba.conf with authentication method "sspi". Remember that only the first entry in pg_hba.conf that matches database, client address, and claimed user name is used.-- Christian
-- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[Postgresql General] [Postgresql Performance] [Postgresql PHP] [Postgresql Jobs] [PHP Users] [PHP Databases] [PHP Home] [PHP on Windows] [Kernel Newbies] [PHP Classes] [PHP Databases] [Yosemite]