Security and Access Control

65 min

PostgreSQL provides comprehensive security features that protect your data from unauthorized access and ensure compliance with security requirements. Security in PostgreSQL operates at multiple levels: network security (who can connect), authentication (proving identity), authorization (what they can do), and data security (encryption). Understanding and implementing these security layers is essential for protecting sensitive data in production environments.

Role-based access control (RBAC) is PostgreSQL's primary authorization mechanism. Roles can represent users or groups of users, and roles can be granted to other roles, creating role hierarchies. Permissions are granted to roles on database objects (tables, functions, schemas). The GRANT and REVOKE commands manage permissions. Understanding role hierarchies and permission inheritance enables you to create flexible, maintainable access control systems.

Row Level Security (RLS) enables fine-grained access control at the row level, allowing different users to see different rows in the same table. RLS policies define which rows users can access based on conditions. For example, employees can only see their own salary records, or managers can only see records for their department. RLS is essential for multi-tenant applications and scenarios requiring data isolation within shared tables.

RLS policies can be created for SELECT, INSERT, UPDATE, and DELETE operations separately, enabling different access rules for different operations. Policies use USING clauses for row visibility and WITH CHECK clauses for row modification. Policies can reference session variables, user roles, or data in other tables to make access decisions. Understanding policy syntax and execution enables you to implement complex access control requirements.

SSL/TLS encryption protects data in transit between clients and the database server. PostgreSQL supports SSL connections, requiring certificates for server authentication and optionally for client authentication. Encrypted connections prevent eavesdropping and man-in-the-middle attacks. For sensitive applications, SSL should be required for all connections. PostgreSQL also supports encrypted passwords stored using SCRAM-SHA-256, providing secure password storage.

Additional security features include connection limits (preventing resource exhaustion), password policies (enforcing strong passwords), and audit logging (tracking access and changes). The pg_hba.conf file controls who can connect from where and using what authentication method. Understanding these security features and implementing them appropriately creates a defense-in-depth security posture that protects your data from various threats.

Key Concepts

PostgreSQL uses role-based access control for authorization.
Roles can represent users or groups and can be granted to other roles.
Row Level Security enables fine-grained row-level access control.
RLS policies define which rows users can access based on conditions.
SSL/TLS encryption protects data in transit.

Learning Objectives

Master

Creating and managing roles and permissions
Implementing Row Level Security policies
Configuring SSL/TLS for encrypted connections
Understanding PostgreSQL security architecture

Develop

Understanding database security best practices
Designing secure access control systems
Implementing compliance and audit requirements

Tips

Use roles for groups, users for individuals: CREATE ROLE managers;
Enable RLS: ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
Create policies: CREATE POLICY name ON table FOR SELECT USING (condition);
Require SSL: Set ssl = on in postgresql.conf and configure pg_hba.conf.

Common Pitfalls

Not enabling RLS, exposing all rows to all users.
Creating overly permissive policies, violating security requirements.
Not using SSL in production, exposing data to network attacks.
Not regularly reviewing permissions, accumulating unnecessary access.

Summary

PostgreSQL provides comprehensive security features at multiple levels.
Role-based access control manages user permissions.
Row Level Security enables fine-grained row-level access control.
SSL/TLS encryption protects data in transit.

Exercise

Implement Row Level Security and role-based access control.

-- Create roles
CREATE ROLE read_only_role;
CREATE ROLE manager_role;
CREATE ROLE admin_role;

-- Grant permissions
GRANT CONNECT ON DATABASE my_database TO read_only_role;
GRANT USAGE ON SCHEMA public TO read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;

GRANT ALL PRIVILEGES ON DATABASE my_database TO admin_role;

-- Create a table with RLS
CREATE TABLE employee_salaries (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    manager_id INTEGER
);

-- Enable RLS
ALTER TABLE employee_salaries ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY emp_salary_policy ON employee_salaries
    FOR ALL
    USING (manager_id = current_setting('app.current_user_id')::integer);

CREATE POLICY emp_own_salary_policy ON employee_salaries
    FOR SELECT
    USING (employee_id = current_setting('app.current_user_id')::integer);

-- Test RLS
SET app.current_user_id = '123';
SELECT * FROM employee_salaries; -- Only shows rows where manager_id = 123 or employee_id = 123

Exercise Tips

Test RLS policies thoroughly: policies can be complex and hard to debug.
Use current_setting() to access session variables in policies.
Create separate policies for different operations (SELECT, INSERT, UPDATE, DELETE).
Use EXPLAIN to see which policies are applied to queries.

Security and Access Control