All About PostgreSQL's Security

Summary

The presentation covers database security concepts including authentication to validate users, authorization to control user access, accounting to track user activity, and encryption to protect data. It focuses on specific techniques used in PostgreSQL 13 for access control via pg_hba.conf, row-level security policies, built-in encryption, and auditing. Best practices are provided such as using firewalls, restricting connections, and securing backups. Overall it aims to help configure PostgreSQL for robust security.

Takeaways

• 😀 Database security comprises securing the network, host, database, application and data
• 🔑 Authentication, authorization and accounting (AAA) covers main security aspects
• 🔐 PostgreSQL has internal, OS and external authentication methods like LDAP
• 🗝 Grant/revoke privileges control what actions users can/can't perform
• 🛡 Row Level Security allows granting access to parts of a table per user
• 📝 Built-in and 3rd party tools enable logging user activity for auditing
• 🔒 File system and column encryption protect data confidentiality
• 😷 Use firewall rules and restricted IP access to reduce attack surface
• 🙈 Don't expose database ports directly to the internet
• 👀 Carefully configure the pg_hba.conf access control file

Q & A

What are the three authentication methods available in PostgreSQL?

The three authentication methods in PostgreSQL are: 1) PostgreSQL internal authentication (trust, reject, md5, scram, cert) 2) OS authentication (pam, peer, ident) 3) External authentication (ldap, radius)

How can you control database access in PostgreSQL?

Database access can be controlled in PostgreSQL using the pg_hba.conf file. This file allows you to specify which IP addresses, databases, and users can connect to the database and what authentication method will be used.

What is row level security in PostgreSQL?

Row level security in PostgreSQL allows granting or revoking permissions to access specific rows in a table, rather than the entire table. This is done by creating policies that compare the current user to values in a column to determine access.

What are some best practices for securing backups in PostgreSQL?

Some best practices are: encrypt backups, store backups securely offline or on separate secured systems, use strict access controls on backups, test restoration procedures regularly.

What logging capabilities does PostgreSQL provide?

PostgreSQL provides detailed logging of statements and activity via views like pg_stat_activity. There are also third party tools like pgAudit and pg_stat_monitor for auditing and monitoring.

How can you encrypt column data in PostgreSQL?

The pgcrypto module in PostgreSQL provides functions to encrypt data at the column level. For example, pgp_sym_encrypt() can be used to encrypt a value that can later be decrypted with the key.

What encryption does PostgreSQL provide out of the box?

PostgreSQL provides SSL encryption for data in transit as well as column level encryption via the pgcrypto module. It does not provide transparent data encryption at the table or database level.

How does PostgreSQL authentication work?

PostgreSQL authentication works by specifying which IP addresses, users, and databases are allowed to connect in the pg_hba.conf file along with the authentication method to use. This controls who can access the database server.

What are some security best practices related to the PostgreSQL configuration?

Best practices include: not using 'trust' authentication in production, avoid allowing connections from all IP addresses, disable or restrict direct access to port 5432 from the internet, and be cautious about allowing access from 'all' databases or users.

What utilities does PostgreSQL provide for auditing database activity?

PostgreSQL provides the pg_stat_activity view for monitoring current activity as well as allowing log_statement=all for logging all statements. There are also various third party tools focused on auditing and monitoring PostgreSQL databases.