Securing Your Data in Postgres Payal Singh DBA@OmniTI - - PowerPoint PPT Presentation

Securing Your Data in Postgres

Payal Singh DBA@OmniTI payal@omniti.com

Contents

Authentication Encryption Row-Level Security SSL / TLS ACLs Event Triggers Auditing Replication

https://www.postgresql.org/support/security/

1. Authentication

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128

md5 scram Password trust

peer reject

ldap ident

#> SET password_encryption = 'scram'; SET #> CREATE ROLE sales PASSWORD 'ReallyBadPassword'; CREATE ROLE #> SELECT substring(rolpassword, 1, 14) FROM pg_authid WHERE rolname = 'sales'; substring

• scram-sha-256:

(1 row)

Trust TRUST !

Initdb -A md5

Don’t

Reject

psql#> SELECT pg_realod_conf( ) ;

Grand Total Of Fall Throughs Allowed:

2. Access Control Lists

“

list st of permissions attached to an

• bject. An ACL specifies which

users or system processes are granted ac acces cess to objects, as well as what operations are allowed on given objects.

Grant Revo voke ke

rolename=xxxx -- privileges granted to a role =xxxx -- privileges granted to PUBLIC r -- SELECT ("read") w -- UPDATE ("write") a -- INSERT ("append") d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) * -- grant option for preceding privilege /yyyy -- role that granted this privilege

Transactional DDLs

psql#> BEGIN; BEGIN psql#> GRANT SELECT ON test TO payal; GRANT psql#> ROLLBACK; ROLLBACK postgres@postgres:5432# \dp test Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies

• -------+------+-------+-------------------+-------------------+----------

public | test | table | | | (1 row)

ALTER IN SCHEMA <schema_name> GRANT <privilege> ON TABLES TO <role>;

DEFAULT PRIVILEGES

Roles and Groups Roles

Sales Bob: Rachel:

Column Level ACLs

postgres@postgres:5432# GRANT ON test TO rachel; GRANT postgres@postgres:5432# \dp test Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies

• -------+------+-------+-------------------+---------------------+----------

public | test | table | | +| | | | | | (1 row) UPDATE(name) name: rachel=w/postgres

PUBLIC Schema

3. Row Level Security

Row Level Security

CREATE TABLE accounts (manager text, company text, contact_email text); ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);

RLS

BYPASSRLS Table Owner ALTER TABLE accounts FOR

FORCE CEROW LEVEL SECURITY;

Default Policy Exceptions

4. SSL / TLS

ssl = o

• n

# (change e requires es restart) #ssl_cipher ers = 'ALL:! :!ADH:! :!LOW:! W:!EXP:! :!MD5:@ :@STRE TRENGTH GTH' ' # allow

• wed

ed SSL ciphers #ssl_prefer er_ser erver er_cipher ers = on # (change e requires es restart) #ssl_ecdh_curve e = 'prime2 e256v1' ' # (change e requires es restart)

ssl_ce cert_fi t_file = '/etc tc/ssl ssl/post stgre res/ s/sta starr rry. y.io.

• .crt'

crt'

# (change e requi uires es restart)

ssl_key_ y_fi file = '/etc tc/ssl ssl/postg

• stgre

res/ s/sta starry. ry.io.

• .key'

y'

# (change e requires es restart) ssl_ca_file le = '' # (change e requires es restart) #ssl_crl_ l_file le = '' # (change e requires es restart)

OpenSSL

• -with-openssl at build time

Encryption Permissions New Certificates

No SSL connections allowed

Allow SSL Connections if connections without SSL fails

Allow non-SSL connection if SSL connection fails

Certificate verification required to connect

Verify server certificate

Server HostName same as name in certificate

Modes

Tunneling

ssh -L 63333:localhost:5432 joe@foo.com psql -h localhost -p 63333 postgres

5. Event Triggers

Event Triggers

Auditing Unwanted modification of data Accidental data loss Trigger-based replication Ownership

ddl_command_start ddl_command_end sql_drop

table_rewrite in pg10!

6. Auditing

Storage !

Pg_Audit

Monitoring Roles (PG10 and onwards)

Pg_monitor Pg_read_all_settings Pg_read_all_stats Pg_stat_scan_tables

7. Encryption & PCI

At-Rest

pgcrypto

• AES-128, AES-192, or AES-256
• Performance impact

Backups Volumes instance-level - 3rd party patch!

https://www.postgresql.org/message-id/CA%2BCSw_tb3bk5i7if6inZFc3yyf%2B9HEVNTy51QFBoeUk7UE_V%3Dw@mail.gmail.com

Shared_preload_libraries = ‘pg_stat_statements’

Monitors

Key Management

Key Storage Location Encryption/Decryption Location Encrypted query processing

8. Replication

Binary Replication

READ-ONLY Replicas Orchestrators

• Chef Data Bags
• Ansible Vault

Recovery File

Logical Replication

Create publication CREATE privilege Add tables table owner Add all tables superuser Create subscription superuser Subscriptions apply process

9. Procedural Languages

Trusted VS Untrusted Security Definer LEAKPROOF

Upcoming Features in PG11

SCRAM-SHA-256-Plus

• Channel Binding SASL mechanism
• Mutual Authentication
• tls-unique
• tls-server-end-point

PG_TEST_EXTRA

• More authentication type tests

Large object ACL permissions

Desired Features

Data Redaction Oracle TDE - key management

http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf

SHOW GRANTS

Thank You !

Twitter: @postgres_women Slack: #pgsql-women