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 Event Triggers Authentication ACLs Auditing Row-Level Security SSL / TLS Encryption 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

ident peer trust scram reject ldap md5 Password

#> 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)

Don’t Trust TRUST ! Initdb -A md5

Reject

psql#> SELECT pg_realod_conf( ) ;

Grand Total Of Fall Throughs Allowed: 0

2. Access Control Lists

“ list st of permissions attached to an object. An ACL specifies which Grant users or system processes are granted ac acces cess to objects, as well Revo voke ke as what operations are allowed on given objects.

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 DEFAULT PRIVILEGES IN SCHEMA < schema_name > GRANT < privilege > ON TABLES TO < role >;

Roles and Groups Roles Bob: Sales Rachel:

Column Level ACLs postgres@postgres:5432# GRANT UPDATE(name) ON test TO rachel; GRANT postgres@postgres:5432# \dp test Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+-------------------+---------------------+---------- public | test | table | | +| name: | | | | | rachel=w/postgres (1 row)

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 CE ROW LEVEL SECURITY; Default Policy Exceptions

4. SSL / TLS

ssl = o on # (change e requires es restart) #ssl_cipher ers = 'ALL:! :!ADH:! :!LOW:! W:!EXP:! :!MD5:@ :@STRE TRENGTH GTH' ' # allow owed 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. o.crt' crt' # (change e requi uires es restart) ssl_key_ y_fi file = '/etc tc/ssl ssl/postg ostgre res/ s/sta starry. ry.io. o.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

Modes DISABLE No SSL connections allowed ALLOW Allow SSL Connections if connections without SSL fails PREFER Allow non-SSL connection if SSL connection fails REQUIRE Certificate verification required to connect VERIFY-CA Verify server certificate VERIFY-FULL Server HostName same as name in certificate

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