Deploying PostgreSQL in a Windows Enterprise
Magnus Hagander
[email protected]
PGCon 2008
Ottawa, Canada May 2008
Agenda
Definition Installation Active
Directory
Authentication
- integrated Authentication - LDAP Data access
Monitoring
What is a Windows Enterprise?
Servers Clients
What is a Windows Enterprise?
Servers Clients
WEB
4
What is a Windows Enterprise?
Servers Active Directory
5
Clients
What is a Windows Enterprise?
Servers Active Directory
6
Clients
Agenda
Definition Installation Active
Directory
Authentication
- integrated Authentication - LDAP Data access
Monitoring
Installation
MSI
installer Integrates with existing products Installs all dependencies Create account, sets permissions Supports silent install Server only, Server+client, Client only
Installation
xcopy
Well,
deployment No registry entries required!
there's ODBC...
binaries-no-installer.zip Dependencies, Custom
account, permissions
build
Agenda
Definition Installation Active
Directory
Authentication
- integrated Authentication - LDAP Data access
Monitoring
10
Active Directory authentication
Integrated
Already
authentication
logged in, why do it again?
Fat
clients
apps usually uses password to db
Web
Very
common for SQL Server/Access Still need to create db user!
11
Active Directory authentication
Client
interface dependent libpq or built on libpq ODBC JDBC npgsql
12
Active Directory authentication
Windows-to-windows
trivial
host all all 0.0.0.0/0 sspi
Set
your AD policies! Always included
13
Active Directory authentication
Windows-to-unix Kerberos
a bit more work
only
14
Kerberos 101
Cross
platform, standards-based, secure, distributed authentication Shared secrets between hosts Maintained and controlled by KDC Trusted tickets Single sign-on
15
Kerberos 101
1. Logi n 2. Tick e t-grant reques t T)
ing-tick
Client
KDC
et (TG
Server
16
Kerberos 101
5. Ticket request P OSTGRE S@ FO O
6. Ticket P
OSTGRE
S@FOO
Client
KDC
t icke t cc ros be 3. A et Ker ick t s uire st w q ue . Re 4 req ss cce 7. A
ess
t ues req
Server
17
Kerberos 101
5. Ticket request P OSTGRE S@ FO O
6. Ticket P
OSTGRE
S@FOO
Client
KDC
t icke t cc ros be 3. A et Ker ick t s uire st w q ue . Re 4 req ss cce 7. A
ess
t ues req
Server
18
Active Directory authentication
Windows-to-unix
AD
a bit more work Kerberos only, requires service principals
enforces non-standard name
Basic
Kerberos first!
/etc/krb5.conf
[libdefaults] default_realm = DOMAIN.COM [domain_realm] domain.com = DOMAIN.COM .domain.com = DOMAIN.COM
19
Active Directory authentication
Verify
kinit
with kinit/klist
[email protected]
20
Active Directory authentication
Install
required build packages ./configure --with-gssapi Build + install as usual Initdb as usual
21
Active Directory authentication
Create
service principal (ordinary user)
22
Active Directory authentication
Create
Kerberos principal mappnig
ktpass -princ POSTGRES/[email protected] -crypto DES-CBC-MD5 -mapuser lab83 -pass FooBar991 -out postgres.keytab
23
Active Directory authentication
Verify
account is mapped
24
Active Directory authentication
postgresql.conf
listen_addresses = '*' krb_server_keyfile = '/var/pgsql/data/postgres.keytab' krb_srvname = 'POSTGRES'
pg_hba.conf
host all all 0.0.0.0/0 gss
25
Active Directory authentication
Client
side principal name Environment: PGKRBSRVNAME Connection string: krbsrvname Needed on both Windows and Unix
26
Active Directory authentication
Client
side principal name Environment: PGKRBSRVNAME Connection string: krbsrvname Needed on both Windows and Unix
27
LDAP Authentication
For
clients that don't support GSS/SSPI If you actually want passwords Looks like password prompt to client pg_hba.conf
host all all 0.0.0.0/0 ldap ldap://dc.domain.com/dc=domain,dc=com;DOMAIN\
28
Agenda
Definition Installation Active
Directory
Authentication
- integrated Authentication - LDAP Data access
Monitoring
29
Access AD data
dblink-ldap
(pgfoundry) Build from source only Create VIEWs of LDAP data Read-only
30
Access AD data
CREATE VIEW users AS SELECT * FROM dblink_ldap( 'dc.domain.com', 'CN=Users, DC=domain, DC=com', E'DOMAIN\\User', 'password', '(objectClass=user)', 'distinguishedName,cn,displayName') t(dn, cn, displayName)
31
Access AD data
postgres=# SELECT * FROM users; dn CN=mha,CN=Users,DC=domain,DC=com (2 rows) | | mha cn | displayname ----------------------------------------------------------------------------| Magnus Hagander CN=Administrator,CN=Users,DC=domain,DC=com | Administrator | Admin
32
Agenda
Definition Installation Active
Directory
Authentication
- integrated Authentication - LDAP Data access
Monitoring
33
Monitoring
Performance
Monitor for system
parameters pgsnmpd (unix only) pg_stat_xyz views
34
Future directions
schannel
encryption schannel certificate authentication Better monitoring support
pgsnmpd
on windows or native performance monitor plugin
35
Thank you!
Questions?
36
