PostgreSQL Security Fundamentals

1. Introduction

Security is crucial in database management. PostgreSQL provides various mechanisms to secure data, manage user access, and protect against unauthorized actions. This lesson covers fundamental concepts of PostgreSQL security.

2. User Authentication

PostgreSQL supports several authentication methods:

• Trust
• MD5
• Peer
• GSSAPI
• SSPI

To configure authentication, edit the pg_hba.conf file located in the data directory.

# Example pg_hba.conf entry
host    all             all             192.168.1.0/24          md5
                    

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql
                    

3. Roles and Permissions

PostgreSQL uses roles to manage database access. Roles can own database objects and have privileges granted to them.

Creating a Role

CREATE ROLE new_user WITH LOGIN PASSWORD 'securepassword';
                    

Granting Permissions

GRANT SELECT, INSERT ON my_table TO new_user;
                    

Revoking Permissions

REVOKE INSERT ON my_table FROM new_user;
                    

4. Data Encryption

Data encryption protects sensitive information. PostgreSQL supports:

• SSL/TLS for data in transit
• pgcrypto for data at rest

Enabling SSL

Add the following lines to postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
                    

5. Network Security

To enhance security, configure PostgreSQL to listen on specific IP addresses:

listen_addresses = 'localhost,192.168.1.100'
                    

Implement firewall rules to restrict access to the PostgreSQL port (default 5432).

6. Best Practices

• Use strong passwords for users.
• Regularly update PostgreSQL to the latest version.
• Regularly back up your data.
• Limit privileges using roles.
• Monitor database activity for suspicious behavior.