Beyond Trust
PostgreSQL Client Authentication
Christoph Mönch-Tegeder
2ndQuadrant
http://www.2ndquadrant.com/
2017-02-05
Part I
Authentication
Why Authentication?
I Too complicated
I Nobody knows my database
I Nobody will attack us
Rich Data
https://blog.malwarebytes.com/cybercrime/2016/04/comelec- breach- data- released- online- fully- searchable/
Rich Data (2)
https://www.theregister.co.uk/2016/04/25/mexico_voter_data_breach/
Racketeering
https://krebsonsecurity.com/2017/01/extortionists- wipe- thousands- of- databases- victims- who- pay- up- get- stiffed/
Racketeering (2)
https://www.theregister.co.uk/2017/01/09/mongodb/
It’s not only that database
https://www.bleepingcomputer.com/news/security/mongodb- hijackers- move- on- to- elasticsearch- servers/
Why Authentication?
I Not the Open Data we wanted!
I Not the kind of support we want to pay!
Why Authentication?
I Not the Open Data we wanted!
I Not the kind of support we want to pay!
I Regulations
I Nobody wants to explain these incidents
You cannot hide
I https://www.shodan.io
I https://www.zoomeye.org
So. . . Secure Your Database!
I Network Security and Firewalls
I keep unauthorized users out
I PostgreSQL listens on localhost only by default
I Fine Grained Access Control
I do not run your app as postgres
I Secure your application
I Authenticate legitimate users
Some Definitions
I Identification declaration of identity
I ”I am user42”
I Authentication proof of identity
I what (only) you know, have, are
I Authorization allows actions on objects
I GRANT SELECT ON tbl TO user42, RLS, . . .
I Audit Log who did what and when
I log_connections, pgaudit
Secret Handling
I Shared Keys, Asymmetric Secrets
I hardcoded, configuration files, . . .
I How to change secrets?
I Do not put secrets on Github!
I To Backup or Not To Backup?
I how to guard backups? how to restore secrets?
I Hardware Security Module (HSM)
I what if lost/destroyed?
Authentication Security
I Passive Attacks
I sniffing authentication info off the net
I and all other traffic
I Active Attacks
I Man in the Middle (MitM)
I may modify traffic
I There is no safe authentication unless you authenticate whom
you’re authenticating against first. (Martin Seeger)
PostgreSQL Authentication
I Highly configurable, well documented
I Flexible: up to 13 methods
I Per database, user, source and connection type
I pg_hba.conf, Host Based Authentication
I checked from top to first match
I Document your configuration, check if it still matches reality
I Users always have to exist in PostgreSQL
I only authentication can be handled externally
Host Based Authentication Configuration
# IPv4 local connections:
host all all 127.0.0.1/32 md5
I type of connection
I local on unix-like platforms only
I database (all, @file, replication, sameuser, samerole)
I user (all, +group, @file)
I non-local: source network
I authentication method and options
Identification mapping
I pg_ident.conf allows mapping of external usernames to
PostgreSQL usernames
I mappings selected by map parameter in pg_hba.conf
I regular expression support
# MAPNAME SYSTEM-USERNAME PG-USERNAME
sslmap "Test User" ssluser
krbmap /^([^@]+)@MY.KRB5.REALM \1
Part II
Authentication Mechanisms
Trust - there is none
I no authentication - just identification
I do not use on servers
I for testing, some embedded systems
I sometimes used in initial pg_hba.conf
Ident - ask remote
I contact source host, ask for local user name
I uses Identification Protocol (RFC1413) - less common today
I additional TCP connection
I relies on security of source host
The Identification Protocol is not intended as an
authorization or access control protocol.
RFC1413 https: // www. rfc- editor. org/ rfc/ rfc1413. txt
Peer - Unix Credentials
I on local connections only
I get system user name from unix socket
I great for local administration
I as secure as the host OS
I limited use - no remote support, inflexible
Reject - Blacklisting
I explicitly reject access
I reject one, allow all
I temporary block during maintenance
I non-authentication method
Password - clear as text
I password authentication
I hashes stored in PostgreSQL – pg_authid
I sends password in clear
I do not use
I use md5 instead
MD5 - hashed password
I password authentication reloaded
I hashes stored in PostgreSQL – pg_authid
I on the wire:
’md5’ + md5(md5(password + username) + salt)
I replay: 50% chance after 2 billion connections (4 byte salt)
I MD5 hash considered broken – regulatory problems
I does not authenticate server
LDAP - Lightweight Directory Access Protocol
I authenticates against LDAP backend (not included)
I simple mode: use credentials to bind (authenticate) to the
LDAP server
I search+bind mode: bind to LDAP server and search for user
with given credentials
I can use TLS connection to the LDAP server
I STARTTLS only (as per RFC 5413) - no LDAP over SSL
I some servers do not support STARTTLS
I cleartext password, does not authenticate server
I TLS for PostgreSQL connection recommended
PAM – Pluggable Authentication Modules
I modules (plugins) handle authentication (and more)
I configuration in /etc/pam.d/ or similar
I cannot access /etc/shadow (when running in PostgreSQL)
I can be used to lock out clients or accounts after failed logins
I cleartext password, does not authenticate server
I TLS for PostgreSQL connection recommended
GSS - Generic Security Services API
I uses KerberosV (Kerberos infrastructure not included)
I realm, principal, ticket, TGT
I client authenticates Key Distribution Center (KDC)
I Service Server PostgreSQL must be known to KDC
I requires accurate time across all systems
I periodic ticket renewal
I no clear text passwords on the network, all entities
authenticated
GSS (2)
I KDC password store: local databases, LDAP, . . .
I Client-KDC authentication: password (most common),
PKINIT (public key), . . .
I keytab files for non-interactive processes: stored secrets
I mapping of Kerberos principals to PostgreSQL users
# MAPNAME SYSTEM-USERNAME PG-USERNAME
krbmap /^([^@]+)@MY.KRB5.REALM \1
GSS (3)
I Simple configuration on the PostgreSQL side
I postgresql.conf
krb_server_keyfile = ’krb5.keytab’
I pg_hba.conf
host all all 10.0.1.0/24 gss krb_realm=MY.KRB5.REALM
I DSN: krbsrvname=postgres
Cert - TLS Client Certificate
I only for TLS connections (hostssl)
I as the client verifies the server’s certificate, the server verifies
the client’s certificate
I requires: PKI (not included)
I possible to create and sign certificates by hand
I for more than a few hosts, use real CA software
Cert (2)
I minimal server configuration
ssl = on
ssl_cert_file = ’server_cert.pem’
ssl_key_file = ’server_cert.key’
ssl_ca_file = ’user_ca.pem’
I recommended: do not re-use server CA for user certificates
I does not require external CA for users
I DSN: sslcert=cert.pem sslkey=cert.key
Cert (3)
I private keys are stored secrets
I client certificates expire: can be changed on the fly
I server certificates and CAs expire: change needs restart
I disable client certificate: Certificate Revocation List (CRL)
I ssl_crl_file
I requires restart in PostgreSQL < 10
I map certificate Common Name (CN) to PostgreSQL user
# MAPNAME SYSTEM-USERNAME PG-USERNAME
sslmap "Test User" ssluser
Other Mechanisms
I radius Remote Authentication Dial-In User Service
I RADIUS uncommon, except for telco environments
I sspi Security Support Provider Interface
I Windows Single Sign On, Kerberos with NTLM fallback
I bsdauth OpenBSD authentication framework
I OpenBSD only, ideas like PAM
Part III
Considerations
Some Notes on TLS
I default cipher list too broad, set ssl_ciphers
I generate your own DH-parameters
I recent PostgreSQL supports ECC (ECDSA)
I new connections are expensive
I not that much overhead once connection is up
Connection Pooling
I Clients authenticate to pooler
I Pooler authenticates to PostgreSQL
I Forwarding of authentication with plaintext password only
I Terminates TLS
Summary
I Secure your databases
I Authenticate clients (and servers)
I At least, use passwords
I Encrypt connections (use TLS)
I Always verify certificates
Questions?
