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
Recommend
More recommend