Download to read offline
![© Copyright EnterpriseDB Corporation, 2021. All rights reserved. 21 Data Redaction Username [enterprisedb]: privilegeduser mycompany=> select * from employees; id | name | ssn | phone | birthday ----+--------------+-------------+------------+-------------------- 1 | Sally Sample | 020-78-9345 | 5081234567 | 02-FEB-61 00:00:00 1 | Jane Doe | 123-33-9345 | 6171234567 | 14-FEB-63 00:00:00 1 | Bill Foo | 123-89-9345 | 9781234567 | 14-FEB-63 00:00:00 (3 rows) Username [enterprisedb]: redacteduser mycompany=> select * from employees; id | name | ssn | phone | birthday ----+--------------+-------------+------------+-------------------- 1 | Sally Sample | xxx-xx-9345 | 5081234567 | 02-FEB-02 00:00:00 1 | Jane Doe | xxx-xx-9345 | 6171234567 | 14-FEB-02 00:00:00 1 | Bill Foo | xxx-xx-9345 | 9781234567 | 14-FEB-02 00:00:00 (3 rows)](https://image.slidesharecdn.com/kangarootpartnerwebinarbestpracticesinsecuritywithpostgresql-210311144131/75/Kangaroot-EDB-Webinar-Best-Practices-in-Security-with-PostgreSQL-21-2048.jpg)
Uploaded byKangaroot
Kangaroot EDB Webinar Best Practices in Security with PostgreSQL
The document presents best practices for securing PostgreSQL databases, covering key concepts such as authentication, authorization, auditing, and data encryption. It provides general recommendations, detailed explanations of security models, and techniques to prevent unauthorized access and SQL injection attacks. Key takeaways include implementing the principle of least privilege, keeping systems updated, and utilizing encryption for data protection.
Kangaroot EDB Webinar Best Practices in Security with PostgreSQL
- 1. Best Practices in Securitywith PostgreSQL Dave Page Marc Linster March 9, 2021
- 2. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 2 • Slides and recording will be available in next 48 hours • Submit questions via the platform chat Welcome – Housekeeping Items
- 3. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 3 Agenda • Introduction to EDB • Aspects of Data Security • General recommendations • Key Concepts: • Authentication • Authorization • Auditing • Data encryption • Summary • Q&A
- 4. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 4 Who is EDB? 1986 The design of PostgreSQL 1996 Birth of PostgreSQL 2004 EDB is founded 2021 EDB acquires 2nd Quadrant Materialized Views Parallel Query JIT Compilation Heap Only Tuples (HOT) Serializable Parallel Query 2007 2ndQuadrant launched Logical Replication Transaction Control Hot Standby Generated Columns We’re database fanatics who care deeply about PostgreSQL • Largest dedicated PostgreSQL company • Enterprise PostgreSQL innovations • Major PostgreSQL community leadership EDB supercharges PostgreSQL
- 5. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 5 Aspects of Data Security Data Security Unauthorized access Data corruption Loss of access Data breaches (Un)intentional corruption Hardware failure Operator error Process failure Loss of encryption keys Network failure Disaster recovery Notification and compliance
- 6. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 6 General Recommendations • Keep your operating system and your database patched. • Don’t put a postmaster port on the internet • Isolate the database port from other network traffic • Grant users the minimum access they require to do their work, nothing more • Restrict access to configuration files (postgresql.conf and pg_hba.conf) • Disallow host system login by the database superuser roles • Provide each user with their own login • Don’t rely solely on your front-end application to prevent unauthorized access • Keep backups, and have a tested recovery plan.
- 7. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 7 DB Host Database files Data base Data base Data base Data access control: • Tables • Columns • Rows • Views • Security barriers DB Server Authentication: • Users • Roles • Password profiles Data Center Physical access Host access DB Server network access File system encryption Data file encryption Data encryption • Column based encryption DML/DDL Auditing SQL Injection Attack Prevention Encryption in transit w. host authentication Data redaction/masking Key Management System Defence in Depth
- 8. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 8 AAA Model Popular model for security architectures • Authentication: verify that the user is who they claim to be. • Authorization: verify that the user is allowed access. • Auditing (or Accounting): record all database activity, including the user name and the time in the log files.
- 9. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 9 Authentication Defined in hba.conf ⇐ make sure you understand how this works and protect that file! • Kerberos/GSSAPI Single Sign-On (SSO) authentication • Data sent over the database connection is unencrypted unless SSL or GSS encryption is in use. • SSPI — Windows Single Sign-On (SSO) authentication • LDAP and RADIUS • LDAP (specifically, LDAP+STARTTLS) should only be used if Kerberos is out of the question. • LDAP passwords are forwarded to the LDAP server, and it can easily be set up in an insecure way. • RADIUS should not be used because it has weak encryption, using md5 hashing for credentials. • Cert — TLS certificate authentication; often used in machine-to-machine communication. • md5 and scram — stores username and password information in the database • Scram is highly preferred over md5 as the passwords are securely hashed. • Use with EDB Postgres password profiles
- 10. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 10 Password Profiles EDB Postgres Advanced Server 9.5 and above Oracle compatible password profiles can be used to: • specify the number of allowable failed login attempts • lock an account due to excessive failed login attempts • mark a password for expiration • define a grace period after a password expiration • define rules for password complexity • define rules that limit password reuse
- 11. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 11 Password Profiles - Setup ( 1 of 4) -- Create profile and a user CREATE PROFILE myprofile; CREATE USER myuser IDENTIFIED BY mypassword; -- Assign profile to a user ALTER USER myuser PROFILE myprofile; -- Check the user-profile mapping SELECT rolname, rolprofile FROM pg_roles WHERE rolname = 'myuser'; rolname | rolprofile ---------+------------ myuser | myprofile
- 12. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 12 Password Profiles - Definition of Rules ( 2 of 4) ALTER PROFILE myprofile LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2; SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles WHERE rolname = 'myuser'; rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate ---------+------------+---------------------+-----------------+------------- myuser | myprofile | OPEN | 0 |
- 13. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 13 Password Profiles - 1st failed login ( 3 of 4) c - myuser Password for user myuser: FATAL: password authentication failed for user "myuser" SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles WHERE rolname = 'myuser'; rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate ---------+------------+---------------------+-----------------+------------- myuser | myprofile | OPEN | 1 |
- 14. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 14 Password Profiles - Account Locked ( 4 of 4) c - myuser Password for user myuser: FATAL: role "myuser" is locked Previous connection kept SELECT rolname, rolprofile, edb_get_role_status(oid), rolfailedlogins, rollockdate FROM pg_roles WHERE rolname = 'myuser'; rolname | rolprofile | edb_get_role_status | rolfailedlogins | rollockdate ---------+------------+---------------------+-----------------+---------------------------------- myuser | myprofile | LOCKED(TIMED) | 0 | 13-NOV-18 12:25:50.811022 +05 Super user interaction ALTER USER myuser ACCOUNT UNLOCK;
- 15. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 15 Authorization We know who you are - what are you allowed to do? ● Standard method: Manage access privileges to tables, views and other objects ● Best Practice: ○ Revoke CREATE privileges from all users and grant them back to trusted roles only: ■ Principle Of Least Privilege ○ Don't allow the use of functions or triggers written in untrusted procedural languages. ○ Database objects should be owned by a secure role
- 16. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 16 Roles and Permissions Manage permissions through roles • Roles represent users and groups • Roles take on the permissions of other roles they are members of • Organise permissions around "group" roles, and make "login" roles members of them • Avoid granting permissions directly to individual login roles to avoid management pain • Roles have attributes, as well as being used in ACLs, e.g. • LOGIN, SUPERUSER, CREATEDB, CREATEROLE, VALID UNTIL
- 17. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 17 ACLs Secure access to tables, views, functions and more • GRANT or REVOKE operations from specific roles • Operations are dependent on the object type: • Tables/views: SELECT, INSERT, UPDATE, DELETE… • Functions: EXECUTE • Database: CONNECT • Languages: USAGE
- 18. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 18 Views Provide access to data without granting access to the underlying tables • SELECT permission can be revoked from sensitive tables • Views can be created to access a subset (rows and/or columns) of the sensitive data • SELECT permission can be granted to the view • Don't forget the SECURITY BARRIER option! • This tells the planner that the view is used for security reasons • Prevents leakage through functions that may be called in the query by ensuring selectivity clauses are applied before functions can see any of the data, at the cost of some optimisation • Don't forget the LEAKPROOF option on functions! • Superusers can set this to indicate a function doesn't leak data, to avoid the optimization loss
- 19. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 19 Security Definer Functions Similar to the setuid bit in a Unix file ACL • Secure underlying tables and other objects • Provide a function that executes with the privileges of its definer rather than its caller • Allows sensitive operations through a tightly defined interface • Use with care!
- 20. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 20 Row Level Security (a.k.a. Virtual Private Database) Restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands 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); DBMS_RLS provides key functions for Oracle’s Virtual Private Database in EDB Postgres Advanced Server
- 21. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 21 Data Redaction Username [enterprisedb]: privilegeduser mycompany=> select * from employees; id | name | ssn | phone | birthday ----+--------------+-------------+------------+-------------------- 1 | Sally Sample | 020-78-9345 | 5081234567 | 02-FEB-61 00:00:00 1 | Jane Doe | 123-33-9345 | 6171234567 | 14-FEB-63 00:00:00 1 | Bill Foo | 123-89-9345 | 9781234567 | 14-FEB-63 00:00:00 (3 rows) Username [enterprisedb]: redacteduser mycompany=> select * from employees; id | name | ssn | phone | birthday ----+--------------+-------------+------------+-------------------- 1 | Sally Sample | xxx-xx-9345 | 5081234567 | 02-FEB-02 00:00:00 1 | Jane Doe | xxx-xx-9345 | 6171234567 | 14-FEB-02 00:00:00 1 | Bill Foo | xxx-xx-9345 | 9781234567 | 14-FEB-02 00:00:00 (3 rows)
- 22. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 22 Auditing PostgreSQL provides basic logging • Uses the standard server log, so output may be mixed with errors, warnings and other info • Log connections and disconnections • Log DML and/or DDL queries • Beware: when log_statement is set to 'ddl' or higher, ALTER ROLE command can result in password exposure in the logs, except in EDB Postgres Advanced Server 11 • Use edb_filter_log.redact_password_command to redact stored passwords from the log file
- 23. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 23 Auditing EDB Postgres Advanced Server offers enhanced auditing to dedicated logs • Track and analyze database activities • Record connections by database Users • Successful and failed • Record SQL activity by database Users • Errors, rollbacks, all DDL, all DML, all SQL statements • Session Tag Auditing • Associate middle-tier application data with specific activities in the database log (e.g. track application Users or IP addresses not just database users)
- 24. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 24 Audit Configuration Params • postgresql.conf parameter: edb_audit (Values = XML or CSV ) • edb_audit_directory & edb_audit_filename • edb_audit_rotation_day, edb_audit_rotation_size, edb_audit_rotation_seconds • edb_audit_connect and edb_audit_disconnect • edb_audit_statement • Specifies which SQL statements to capture • edb_filter_log.redact_password_commands ⇐ Redacts passwords from audit file! edb_audit_connect = 'all' edb_audit_statement = create view,create materialized view,create sequence,grant'
- 25. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 25 SQL Injection Prevention • SQL Injection attacks are possible where applications are designed in a way that allows the attacker to modify SQL that is executed on the database server. • By far the most common way to create a vulnerability of this type is by creating SQL queries by concatenating strings that include user-supplied data. From: https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom
- 26. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 26 SQL Injection Prevention Example • Consider a website which will login a user using a query constructed as follows: login_ok = conn.execute("SELECT count(*) FROM users WHERE name = '" + username + "' AND password = '" + password + "';"); • If the user enters their username as dave and their password as secret' OR '1' = '1 , the generated SQL will become: SELECT count(*) FROM users WHERE name = 'dave' AND password = ' secret' OR '1' = '1'; • If the code is testing that login_ok has a non-zero value to authenticate the user, then the user will be logged in regardless of whether the username/password is correct.
- 27. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 27 SQL Injection Prevention Protecting against it in the application - sanitize the user input! • Don't use string concatenation to include user supplied input in queries! • Use parameterised queries instead, and let the language, driver, or database handle it. • Here's a Python example (using the psycopg2 driver): cursor.execute("""SELECT count(*) FROM users WHERE username = %s AND password = %s;""", (username, password))
- 28. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 28 SQL Protect EDB Postgres Advanced Server: Additional SQL Injection Prevention at the Database Level • Utility Commands • Any DDL commands: DROP TABLE • SQL Tautologies • SQL WHERE predicates such as "… AND 1=1" • Empty DML • DML commands with no WHERE filter, such as: DELETE FROM EMPLOYEE; • Unauthorized Relations • Results from Learn mode associating roles with tables
- 29. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 29 Encryption Encrypt at rest and in transit -- key: Understand the threat vector! • Password storage hashing/encryption • Encryption for specific columns • Data partition encryption • Encrypting passwords across a network • Encrypting data across a network • SSL host authentication • Client-side encryption
- 30. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 30 VTE - Advanced Option for PCI Compliant Storage Encryption Compatible with EDB Postgres Advanced Server - Used for PCI compliance https://www.brighttalk.com/webcast/2037/396902?utm_source=Thales&utm_medium=brighttalk&utm_campaign=396902
- 31. © Copyright EnterpriseDBCorporation, 2021. All rights reserved. 31 Conclusion Security comes in layers! AAA (Authorization, Authentication, Auditing) reference model Encryption at rest and on the wire has to be part of the plan Least privilege approach is key Read, read, and read some more! ● EDB Security Technical Implementation Guidelines (STIG) for PostgreSQL on Windows and Linux ● Blog: How to Secure PostgreSQL: Security Hardening Best Practices & Tips ● Blog: Managing Roles with Password Profiles: Part 1 ● Blog: Managing Roles with Password Profiles: Part 2 ● Blog: Managing Roles with Password Profiles: Part 3 Thank You