Access Control

Managing user permissions and database access securely

Proper access control ensures that only authorized users and applications can access your database resources. This guide covers best practices for managing permissions across your infrastructure.

Principle of Least Privilege#

Grant users and applications only the minimum permissions required to perform their tasks.

Database Role Management#

Creating Roles with Limited Permissions#

1
-- Create a read-only role
2
CREATE ROLE app_readonly;
3
GRANT CONNECT ON DATABASE mydb TO app_readonly;
4
GRANT USAGE ON SCHEMA public TO app_readonly;
5
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
6
7
-- Create a read-write role for the application
8
CREATE ROLE app_readwrite;
9
GRANT CONNECT ON DATABASE mydb TO app_readwrite;
10
GRANT USAGE ON SCHEMA public TO app_readwrite;
11
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
12
13
-- Create a user with the read-write role
14
CREATE USER app_user WITH PASSWORD 'secure_password';
15
GRANT app_readwrite TO app_user;

Role Hierarchy Best Practices#

IP Allowlisting#

Restrict database access to known IP addresses:

• Production applications: Allow only your application server IPs
• CI/CD pipelines: Allow runner IP ranges
• Developer access: Use VPN or bastion hosts

1
# Example: Allowed IPs configuration
2
allowed_ips:
3
  - 10.0.0.0/8        # Internal network
4
  - 192.168.1.100/32  # Specific server
5
  - 203.0.113.0/24    # Office network

Network Security#

Private Networking#

For maximum security, use private networking to keep database traffic off the public internet:

• AWS PrivateLink: Connect through AWS's private network
• VPC Peering: Direct connection between VPCs
• VPN: Encrypted tunnel for on-premises connectivity

Connection Security#

Always require encrypted connections:

1
# Python example with SSL required
2
import psycopg2
3
4
conn = psycopg2.connect(
5
    host="your-host",
6
    database="your-db",
7
    user="your-user",
8
    password="your-password",
9
    sslmode="verify-full",
10
    sslrootcert="/path/to/ca-certificate.crt"
11
)

Authentication Methods#

Password Authentication#

• Use strong, randomly generated passwords (60+ bits of entropy)
• Rotate passwords regularly (every 90 days)
• Never reuse passwords across environments

Certificate Authentication#

For enhanced security, use client certificates:

1
# Connect with client certificate
2
psql "host=your-host dbname=your-db user=your-user sslmode=verify-full sslcert=client.crt sslkey=client.key sslrootcert=ca.crt"

IAM Authentication#

Integrate with cloud provider IAM for centralized access management:

• AWS IAM database authentication
• Azure Active Directory authentication
• Google Cloud IAM

Audit Logging#

Enable audit logging to track access and changes:

• Who accessed the database
• When the access occurred
• What queries were executed
• Where the connection originated

Access Review Process#

Implement regular access reviews:

1. Quarterly reviews: Audit all database users and permissions
2. Offboarding: Immediately revoke access when employees leave
3. Role changes: Update permissions when responsibilities change
4. Service accounts: Review application credentials annually

Frequently Asked Questions#

How do I revoke access for a terminated employee? Immediately disable their database user, rotate any shared credentials they had access to, and review audit logs for recent activity.

Should applications use individual or shared credentials? Each application component should have its own credentials. This enables granular permissions, easier rotation, and better audit trails.

How do I grant temporary access for debugging? Create time-limited credentials or use a just-in-time access system. Never share permanent credentials for temporary access.

Related Resources#

• Secrets Management — Managing credentials securely
• Security Reporting — Report vulnerabilities responsibly