I n t r o t o P o s t g r e S Q L S e c u r i t y NordicPGDay - PowerPoint PPT Presentation

I n t r o t o P o s t g r e S Q L S e c u r i t y NordicPGDay 2014 Stockholm, Sweden Stephen Frost sfrost@snowman.net Resonate, Inc. • Digital Media • PostgreSQL • Hadoop • techjobs@resonateinsights.com • http://www.resonateinsights.com

S t e p h e n F r o s t •PostgreSQL •Major Contributor, Committer •Implemented Roles in 8.3 •Column-Level Privileges in 8.4 •Contributions to PL/pgSQL, PostGIS •Resonate, Inc. •Principal Database Engineer •Online Digital Media Company •We're Hiring! - techjobs@resonateinsights.com

D o y o u r e a d . . . •planet.postgresql.org

S e c u r i t y i n P o s t g r e S Q L •Role system •Role-level Privileges •Authentication

S e c u r i t y i n P o s t g r e S Q L •Authorization •Containers •GRANT / REVOKE •Defaults

S e c u r i t y i n P o s t g r e S Q L •Use-cases •Web-based •Enterprise DB / DW

R o l e s •Identities inside PostgreSQL •Each connection is assiged specific role •Roles encompass both users and groups •Nearly all objects are "owned" by a specific role •Shared across entire cluster (not per-DB)

R o l e s •Objects in PG with owners: * D a t a b a s e s * S c h e m a s * T a b l e s ( L o c a l a n d F o r e i g n ) * F u n c t i o n s * A g g r e g a t e s * C o l l a t i o n s * C o n v e r s i o n s * D o m a i n s * E v e n t T r i g g e r s * F o r e i g n D a t a W r a p p e r s * L a n g u a g e s * L a r g e O b j e c t s * S e q u e n c e s * F o r e i g n S e r v e r s * T a b l e s p a c e s * T y p e s * V i e w s ( N o r m a l a n d M a t e r i a l i z e d ) * O p e r a t o r s ( a n d C l a s s e s a n d F a m i l i e s ) * T e x t S e a r c h C o n f i g u r a t i o n a n d D i c t i o n a r i e s

R o l e M e m b e r s h i p •Roles can be members of other roles •GRANT used to add a role to another role •Loops are forbidden •WITH ADMIN allows the role to grant the role

R o l e M e m b e r s h i p •inherit / noinherit •inherit - privileges (not attributes) automatic •noinherit - "SET ROLE ..." required •Great for sudo-like DB administration •Create "barrier" role- eg: "admin", with noinherit •Grant "admin" to, uh, admins, postgres to "admin" •Supports traditional "User/Group", and then some

C h a n g i n g R o l e s •"SET ROLE" SQL command •Allows gaining "noinherit" privileges •Can be used to drop privileges too •DISCARD ALL; will reset role too •"$user" in search_path follows SET ROLE

C h a n g i n g R o l e s •Security Definer Functions run as owner •Need to be careful with search_path •Strongly recommend against superuser owned •Views also run as owner •Need to mark view 'security_barrier'

R o l e P r i v i l e g e s •SUPERUSER •Bypass ALL security (and some sanity..) checks •Use very sparingly •Never login to SUPERUSER role directly •Require "SET ROLE postgres;" to be superuser

R o l e P r i v i l e g e s •What's wrong with SUPERUSER? = # d e l e t e f r o m p g _ d a t a b a s e ; D E L E T E 3

R o l e P r i v i l e g e s •What's wrong with SUPERUSER? = # d e l e t e f r o m p g _ c l a s s ; D E L E T E 2 9 5

R o l e P r i v i l e g e s •What's wrong with SUPERUSER? = # C O P Y p g _ c l a s s T O ' / h o m e / s f r o s t / p g / s r c / c l e a n / i n s t a l l / d a t a / p o s t m a s t e r . c o n f ' W I T H C S V ; C O P Y 2 9 5

R o l e P r i v i l e g e s •What's wrong with SUPERUSER? = # C O P Y p g _ c l a s s T O P R O G R A M ' c a t > p o s t g r e s q l . c o n f ' ; C O P Y 2 9 5

R o l e P r i v i l e g e s •CREATEDB •Allows creating new databases •Give out sparingly- DBs are not free •User becomes database owner

R o l e P r i v i l e g e s •CREATEROLE •Allows creating new roles •ALSO allows modifying EXISTING roles •Can add CREATEDB to roles, et al •Non-superuser can't modify superuser •Use with caution

R o l e P r i v i l e g e s •REPLICATION •Use can connect to "replication" database •Only grant to dedicated replication accounts •Can read every file in the cluster

R o l e P r i v i l e g e s •LOGIN •Role is allowed to connect to PG •Roles with LOGIN will show up in "pg_user" •Roles with NOLOGIN will show up in "pg_group"

R o l e P r i v i l e g e s •CONNECTION LIMIT •Concurrent connection limit •Changing this will impact existing connections

R o l e P r i v i l e g e s •VALID UNTIL •Can't connect after this time •Does not impact existing connections

A u t h e n t i c a t i o n •Connection parameters •Database •PostgreSQL Role •Client IP / Unix Socket •SSL vs. non-SSL

A u t h e n t i c a t i o n •Based on parameters, auth method is chosen •Auth method can provide "system" username •System username can be mapped to PG role

p g _ h b a . c o n f •Processed top-to-bottom, first match wins •"User" can be "+role" to mean "member of role" •Database can be "all", "replication", "sameuser" # T Y P E D A T A B A S E U S E R A D D R E S S M E T H O D # " l o c a l " i s f o r U n i x d o m a i n s o c k e t c o n n e c t i o n s o n l y l o c a l a l l a l l p e e r m a p = u n i x m a p # I P v 4 l o c a l c o n n e c t i o n s : h o s t a l l a l l 1 2 7 . 0 . 0 . 1 / 3 2 m d 5 # I P v 6 l o c a l c o n n e c t i o n s : h o s t a l l a l l : : 1 / 1 2 8 m d 5 # A l l o w r e p l i c a t i o n c o n n e c t i o n s f r o m l o c a l h o s t , b y a u s e r w i t h t h e # r e p l i c a t i o n p r i v i l e g e . # l o c a l r e p l i c a t i o n r e p l _ u s e r m d 5 # h o s t r e p l i c a t i o n r e p l _ u s e r 1 2 7 . 0 . 0 . 1 / 3 2 m d 5 # h o s t r e p l i c a t i o n r e p l _ u s e r : : 1 / 1 2 8 m d 5

p g _ i d e n t . c o n f •Also processed top-to-bottom, by map name •Regexps can be used with "/" and "1" # M A P N A M E S Y S T E M - U S E R N A M E P G - U S E R N A M E u n i x m a p r o o t p o s t g r e s u n i x m a p / ^ ( . * ) $ \ 1 l o c a l r e a l m / ^ ( [ ^ @ ] * ) @ M Y R E A L M \ . C O M $ \ 1 l o c a l r e a l m j o w @ O T H E R R E A L M . c o m o t h e r j o e c l i e n t c e r t " c n = S t e p h e n P . F r o s t " s f r o s t c l i e n t c e r t " c n = J o h n D o e " j d o e

A u t h M e t h o d s •peer •Unix socket based- uses the unix username •punts on the authentication issue to the unix layer •(ident covers this but also identd, do not use)

A u t h M e t h o d s •gss / sspi / krb5 (krb5 deprecated) •Kerberos / Active Directory based authentication •Perfect for Enterprise deployments •Supports cross-realm, princ-based identification •SSL required only for data encryption (not authN) •No option for Kerberos/GSS data encryption today

A u t h M e t h o d s •cert •Client-side SSL certificates •Useful with OpenSSL support, eg: Smart Cards •SSL required for SSL certificates, of course •Requires full PKI setup, CAs, etc

A u t h M e t h o d s •md5 •Normal password-based authentication •("password" exists, but PW is sent in the clear) •Should use SSL with this

A u t h M e t h o d s •radius •RADIUS servers- relatively rare / special case •Need to use SSL to PG, and RADIUS encryption •reject •Special case- reject if matched

A u t h M e t h o d s •ldap •Allows for simple-bind, or LDAP lookup •Need to use SSL to PG, and TLS with LDAP •trust •Allows any connection to connect as any user

A u t h o r i z a t i o n •Container objects •Databases •Schemas •To access objects inside containers- •Must have CONNECT privs on the database •Must have USAGE privs on the schema

G R A N T / R E V O K E •GRANT <privs> ON <object> TO <roles>; •REVOKE <privs> ON <object> FROM <roles>; •GRANT ... ON ALL <objtype> IN <schema> ... •"PUBLIC" means "everyone" •WITH GRANT OPTION allows role to re-grant priv