pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to setup PostgreSQL using Windows Authentication?
8+ messages / 4 participants
[nested] [flat]
* How to setup PostgreSQL using Windows Authentication?
@ 2012-06-12 02:00 Peter Cheung <Pcheung@sfcu.org>
2012-06-12 08:11 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
2012-06-16 12:36 ` Re: How to setup PostgreSQL using Windows Authentication? Christian Ullrich <chris@chrullrich.net>
0 siblings, 2 replies; 8+ messages in thread
From: Peter Cheung @ 2012-06-12 02:00 UTC (permalink / raw)
To: pgsql-admin
Hi,
I'm new to PostgreSQL. I installed PostgreSQL on a Windows Server 2008 R2 server. I have created a database and an user in Windows Active Directory. How can I configure that user to access that database?
Thanks.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
@ 2012-06-12 08:11 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-12 18:37 ` Re: How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
1 sibling, 1 reply; 8+ messages in thread
From: Craig Ringer @ 2012-06-12 08:11 UTC (permalink / raw)
To: Peter Cheung <Pcheung@sfcu.org>; +Cc: pgsql-admin
On 06/12/2012 10:00 AM, Peter Cheung wrote:
>
> Hi,
>
> I'm new to PostgreSQL. I installed PostgreSQL on a Windows Server
> 2008 R2 server. I have created a database and an user in Windows
> Active Directory. How can I configure that user to access that database?
>
By default, Windows users and PostgreSQL users are completely separate.
Use PgAdmin-III to create the user, or a "CREATE USER" command in psql. See
http://www.postgresql.org/docs/9.1/static/user-manag.html
It is also possible to use SSPI authentication with PostgreSQL, so
PostgreSQL authenticates users against Active Directory. I haven't used
it myself. The user must still be created in PostgreSQL, SSPI just takes
care of authenticating them using their Windows credentials. See:
http://www.postgresql.org/docs/9.1/static/auth-methods.html
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-12 08:11 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
@ 2012-06-12 18:37 ` Peter Cheung <Pcheung@sfcu.org>
2012-06-13 02:20 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Cheung @ 2012-06-12 18:37 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: pgsql-admin
According to http://www.postgresql.org/docs/9.1/static/auth-methods.html, SSPI only works when GSSAPI is available and GSSAPI support has to be enabled when PostgreSQL is built. Does it mean that I need to uninstall PostgreSQL and reinstall it with GSSAPI support? I used the One click installer downloaded from http://www.postgresql.org/download/windows to install PostgreSQL and I'm not sure how to include GSSAPI support.
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Tuesday, June 12, 2012 1:11 AM
To: Peter Cheung
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] How to setup PostgreSQL using Windows Authentication?
On 06/12/2012 10:00 AM, Peter Cheung wrote:
Hi,
I'm new to PostgreSQL. I installed PostgreSQL on a Windows Server 2008 R2 server. I have created a database and an user in Windows Active Directory. How can I configure that user to access that database?
By default, Windows users and PostgreSQL users are completely separate.
Use PgAdmin-III to create the user, or a "CREATE USER" command in psql. See
http://www.postgresql.org/docs/9.1/static/user-manag.html
It is also possible to use SSPI authentication with PostgreSQL, so PostgreSQL authenticates users against Active Directory. I haven't used it myself. The user must still be created in PostgreSQL, SSPI just takes care of authenticating them using their Windows credentials. See:
http://www.postgresql.org/docs/9.1/static/auth-methods.html
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-12 08:11 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
2012-06-12 18:37 ` Re: How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
@ 2012-06-13 02:20 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-13 04:38 ` Re: How to setup PostgreSQL using Windows Authentication? Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 8+ messages in thread
From: Craig Ringer @ 2012-06-13 02:20 UTC (permalink / raw)
To: Peter Cheung <Pcheung@sfcu.org>; +Cc: pgsql-admin
On 06/13/2012 02:37 AM, Peter Cheung wrote:
>
> According to
> http://www.postgresql.org/docs/9.1/static/auth-methods.html, SSPI only
> works when GSSAPI is available and GSSAPI support has to be enabled
> when PostgreSQL is built. Does it mean that I need to uninstall
> PostgreSQL and reinstall it with GSSAPI support? I used the One click
> installer downloaded from http://www.postgresql.org/download/windows
> to install PostgreSQL and I'm not sure how to include GSSAPI support.
>
While I haven't used it myself, I'd be amazed if the one-click
installer's version of Pg wasn't built with GSSAPI and SSPI support. You
shouldn't have to do anything.
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-12 08:11 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
2012-06-12 18:37 ` Re: How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-13 02:20 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
@ 2012-06-13 04:38 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 8+ messages in thread
From: Magnus Hagander @ 2012-06-13 04:38 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: pgsql-admin; Peter Cheung <Pcheung@sfcu.org>
On Jun 13, 2012 4:21 AM, "Craig Ringer" <ringerc@ringerc.id.au> wrote:
>
> On 06/13/2012 02:37 AM, Peter Cheung wrote:
>>
>> According to http://www.postgresql.org/docs/9.1/static/auth-methods.html,
SSPI only works when GSSAPI is available and GSSAPI support has to be
enabled when PostgreSQL is built. Does it mean that I need to uninstall
PostgreSQL and reinstall it with GSSAPI support? I used the One click
installer downloaded from http://www.postgresql.org/download/windows to
install PostgreSQL and I’m not sure how to include GSSAPI support.
>
>
> While I haven't used it myself, I'd be amazed if the one-click
installer's version of Pg wasn't built with GSSAPI and SSPI support. You
shouldn't have to do anything.
Gssapi is *not* required on windows. Sspi support is always available on
windows. Gssapi is only required to use Sspi on non-windows platforms.
Perhaps that sentence in the docs need to be changed to be more clear?
/Magnus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
@ 2012-06-16 12:36 ` Christian Ullrich <chris@chrullrich.net>
2012-06-17 09:30 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
1 sibling, 1 reply; 8+ messages in thread
From: Christian Ullrich @ 2012-06-16 12:36 UTC (permalink / raw)
To: pgsql-admin
* Peter Cheung wrote:
> I’m new to PostgreSQL. I installed PostgreSQL on a Windows Server 2008
> R2 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:
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 is
POSTGRES/fully.qualified.host.name
In 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-16 12:36 ` Re: How to setup PostgreSQL using Windows Authentication? Christian Ullrich <chris@chrullrich.net>
@ 2012-06-17 09:30 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-17 09:34 ` Re: How to setup PostgreSQL using Windows Authentication? Christian Ullrich <chris@chrullrich.net>
0 siblings, 1 reply; 8+ messages in thread
From: Craig Ringer @ 2012-06-17 09:30 UTC (permalink / raw)
To: Christian Ullrich <chris@chrullrich.net>; +Cc: pgsql-admin
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
>> 2008
>> R2 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 is
>
> POSTGRES/fully.qualified.host.name
>
> In 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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to setup PostgreSQL using Windows Authentication?
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-16 12:36 ` Re: How to setup PostgreSQL using Windows Authentication? Christian Ullrich <chris@chrullrich.net>
2012-06-17 09:30 ` Re: How to setup PostgreSQL using Windows Authentication? Craig Ringer <ringerc@ringerc.id.au>
@ 2012-06-17 09:34 ` Christian Ullrich <chris@chrullrich.net>
0 siblings, 0 replies; 8+ messages in thread
From: Christian Ullrich @ 2012-06-17 09:34 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: pgsql-admin
* Craig Ringer wrote:
> On 06/16/2012 08:36 PM, Christian Ullrich wrote:
>> 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?
It's not in the main docs, at least.
> If not documented anywhere I'd like to add that to the wiki.
Sure, go ahead.
--
Christian
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-06-17 09:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-12 02:00 How to setup PostgreSQL using Windows Authentication? Peter Cheung <Pcheung@sfcu.org>
2012-06-12 08:11 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-12 18:37 ` Peter Cheung <Pcheung@sfcu.org>
2012-06-13 02:20 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-13 04:38 ` Magnus Hagander <magnus@hagander.net>
2012-06-16 12:36 ` Christian Ullrich <chris@chrullrich.net>
2012-06-17 09:30 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-17 09:34 ` Christian Ullrich <chris@chrullrich.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox