PostgreSQL: Security and User Management

Role-Based Access Control (RBAC)#

Ah, database security—because nothing says “thrill ride” like preventing reckless users from dropping tables they shouldn’t even have access to. Enter Role-Based Access Control (RBAC), PostgreSQL’s way of keeping your data safe from chaos.

Understanding Role-Based Security in PostgreSQL#

PostgreSQL lets you assign roles to users, defining what they can and (more importantly) cannot do. Think of it like giving knives to chefs but keeping them away from toddlers.

Differences Between Users, Roles, and Groups#

• Users: Individuals who connect to the database. Hopefully, they know what they’re doing.
• Roles: A set of permissions that users can inherit.
• Groups: A fancy name for roles that multiple users can share.

Best Practices for Role Management#

• Use roles instead of assigning permissions directly to users. Trust me, future-you will thank past-you.
• Grant the least amount of privilege possible. Nobody needs SUPERUSER unless you enjoy living dangerously.
• Regularly audit roles to remove unused or overpowered accounts.

Managing Users and Roles#

You wouldn’t hand out admin keys to every intern, right? Same goes for databases.

Creating Users and Roles Using CREATE ROLE and CREATE USER#

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'securepassword';

or the alternative approach:

CREATE USER john_doe WITH PASSWORD 'supersecure';

Assigning Roles to Users (GRANT and REVOKE)#

GRANT readonly_user TO john_doe;

To take back what you foolishly granted:

REVOKE readonly_user FROM john_doe;

Using ALTER ROLE to Modify Permissions#

Need to make someone an admin? This is how bad decisions start:

ALTER ROLE john_doe WITH SUPERUSER;

Dropping Users and Roles Safely#

Before yeet-ing a user out of the system:

DROP ROLE IF EXISTS john_doe;

Granting and Revoking Privileges#

Understanding Privileges: SELECT, INSERT, UPDATE, DELETE, EXECUTE#

Your data isn’t just sitting there waiting to be plundered. It needs rules:

• SELECT: Read but don’t touch.
• INSERT: Add new rows.
• UPDATE: Modify existing rows.
• DELETE: Remove rows (hopefully on purpose).
• EXECUTE: Run stored procedures and functions.

Granting Privileges on Tables, Schemas, and Functions#

GRANT SELECT, INSERT ON users TO readonly_user;

Revoking Privileges to Restrict Access#

REVOKE DELETE ON users FROM readonly_user;

Using DEFAULT PRIVILEGES for Automatic Permission Assignments#

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

Securing Connections with SSL#

Because sending credentials over plain text is about as smart as posting your ATM PIN on Twitter.

Importance of SSL Encryption in PostgreSQL#

SSL encrypts data in transit, ensuring that man-in-the-middle attackers have to work really hard to eavesdrop.

Configuring PostgreSQL to Use SSL#

Update postgresql.conf:

ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

Generating and Using SSL Certificates#

openssl req -new -x509 -days 365 -nodes -out server.crt -keyout server.key

Enforcing SSL Connections for Secure Communication#

ALTER SYSTEM SET ssl = 'on';
SELECT pg_reload_conf();

Database Encryption and Best Practices#

Column-Level Encryption Using pgcrypto#

CREATE EXTENSION IF NOT EXISTS pgcrypto;
UPDATE users SET email = pgp_sym_encrypt(email, 'my_secret_key');

Transparent Data Encryption (TDE) Considerations#

PostgreSQL doesn’t have built-in TDE, so if you need it, look into file-system-level encryption.

Best Security Practices for PostgreSQL Databases#

• Use strong passwords (no, password123 doesn’t count).
• Restrict remote access—you don’t need the whole internet poking at your database.
• Regularly rotate credentials—because people write down passwords in places they shouldn’t.

Monitoring and Auditing User Activity#

SELECT * FROM pg_stat_activity;

Hands-On Exercise#

1. Create users and roles with different privilege levels.
2. Grant and revoke privileges on tables and schemas.
3. Configure PostgreSQL to enforce SSL for secure connections.
4. Implement column encryption using pgcrypto.
5. Audit user activity to track database access and changes.

Security isn’t glamorous, but neither is explaining to your boss why the database got wiped by an “accidental” query. Lock it down, grant only what’s necessary, and for the love of data integrity—use SSL.