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

G R A N T / R E V O K E

• Owning the object grants all rights, and then some
• Only owner of object can DROP the object

G R A N T { { S E L E C T | I N S E R T | U P D A T E | D E L E T E | T R U N C A T E | R E F E R E N C E S | T R I G G E R } [ , . . . ] | A L L [ P R I V I L E G E S ] } O N { [ T A B L E ] t a b l e _ n a m e [ , . . . ] | A L L T A B L E S I N S C H E M A s c h e m a _ n a m e [ , . . . ] } T O { [ G R O U P ] r o l e _ n a m e | P U B L I C } [ , . . . ] [ W I T H G R A N T O P T I O N ] . . .

D a t a b a s e P r i v i l e g e s

• CREATE (Able to create schemas)
• CONNECT (Granted to PUBLIC by default)
• TEMPORARY (Able to create temporary objects)
• Owners can use ALTER DATABASE to
• RENAME
• OWNER
• SET TABLESPACE
• SET other config options

S c h e m a P r i v i l e g e s

• CREATE (Able to create objects in the schema)
• USAGE (Able to see objects- need rights on them)
• "public" schema defaults with CREATE to PUBLIC
• Owners can use ALTER SCHEMA to
• RENAME
• OWNER

T a b l e P r i v i l e g e s

• SELECT (SELECT any/all columns)
• INSERT (INSERT any/all columns)
• UPDATE (UPDATE any/all columns)
• DELETE
• TRUNCATE (Not the same as DELETE FROM ...)
• REFERENCES (Can create a FK to the table)
• TRIGGER (Can create a trigger on the table)

T a b l e P r i v i l e g e s

• Table owners can use ALTER TABLE to
• ADD/DROP COLUMN
• ADD/DROP Constraints
• OWNER
• CLUSTER
• INHERIT / NOINHERIT
• Lots of stuff...

C o l u m n P r i v i l e g e s

• SELECT (Only select out specified column)
• INSERT (Can only insert non-default values)
• UPDATE (Can only update these columns)
• REFERENCES (Can only reference specified column)
• Table owners can ALTER TABLE .. ALTER COLUMN to
• SET/DROP DEFAULT expression
• SET STATISTICS (target)
• SET DATA TYPE
• SET STORAGE

S e q u e n c e P r i v i l e g e s

• USAGE (currval && nextval)
• SELECT (Only currval)
• UPDATE (nextval && setval / reset sequence)

F u n c t i o n P r i v i l e g e s

• EXECUTE
• Granted to "PUBLIC" by default!
• Use caution with SECURITY DEFINER

T a b l e s p a c e P r i v i l e g e s

• CREATE
• User allowed to create objects in tablespace
• Any kind of object allowed
• Can be temp or non-temp (even if temp tablespace)
• Database Default Tablespace
• Skips tablespace priv checking
• Only for connections to that DB

U s a g e P r i v i l e g e s

• Objects with just USAGE privs
• DOMAIN
• FOREIGN DATA WRAPPER
• FOREIGN SERVER
• LANGUAGE
• TYPE

W e b - S c a l e

• Roles exist in a PG shared catalog
• Common across all DBs
• Unable to be partitioned
• Could be sharded..
• Unable to set CHECK constraints
• No triggers
• etc..
• BUT- use roles also

R o l e s f o r W e b - S c a l e

• Use tables for website users
• Use roles for permissions management
• Independent roles for ETL, daemon, etc

R o l e s f o r W e b - S c a l e

• Read-only role
• Only has read access
• Useful for scaling out with read slaves
• Read/write role(s)
• Possibly more than one (eg: per site)
• Minimize access to what code "should" do

E n t e r p r i s e D e p l o y m e n t

• Individual logins per user
• Roles for permissions management
• Roles to manage access to databases
• Kerberos / GSS / AD integration / Pass-thru

E n t e r p r i s e D e p l o y m e n t

• Views
• Limit rows individual users can see
• Security Barrier
• PL/PgSQL Functions
• Control writes- include auditing
• Security Definer

S e c u r i t y L a b e l s

• Defines labels for objects in PG
• Hooks for security providers (eg: sepgsql)

S E C U R I T Y L A B E L [ F O R p r o v i d e r ] O N { T A B L E o b j e c t _ n a m e | C O L U M N t a b l e _ n a m e . c o l u m n _ n a m e | A G G R E G A T E a g g r e g a t e _ n a m e ( a g g r e g a t e _ s i g n a t u r e ) | D A T A B A S E o b j e c t _ n a m e | D O M A I N o b j e c t _ n a m e | E V E N T T R I G G E R o b j e c t _ n a m e | F O R E I G N T A B L E o b j e c t _ n a m e F U N C T I O N f u n c t i o n _ n a m e ( [ [ a r g m o d e ] [ a r g n a m e ] a r g t y p e [ , . . . ] ] ) | L A R G E O B J E C T l a r g e _ o b j e c t _ o i d | M A T E R I A L I Z E D V I E W o b j e c t _ n a m e | [ P R O C E D U R A L ] L A N G U A G E o b j e c t _ n a m e | R O L E o b j e c t _ n a m e | S C H E M A o b j e c t _ n a m e | S E Q U E N C E o b j e c t _ n a m e | T A B L E S P A C E o b j e c t _ n a m e | T Y P E o b j e c t _ n a m e | V I E W o b j e c t _ n a m e } I S ' l a b e l '

A d d i t i o n a l S e c u r i t y

• SELinux Integration
• sepgsql security provider
• Works with SECURITY LABEL
• EVENT Triggers
• Can prevent certain actions
• Row-Level Security being worked on
• Updatable security-barrier views

Q u e s t i o n s ?

T h a n k y o u !

Stephen Frost sfrost@snowman.net @net_snow