PostgreSQL: Role-Based Authentication & Role-Based Authorization

PostgreSQL: Role-Based Authentication & Role-Based Authorization

Database Security is more than just a complex password. In a modern enterprise, we separate Authentication (Can you connect?) from Authorization (What can you touch?).

The Core Concept: Who vs. What

Ask two questions:

1. Who are you & Where are you? (Authentication) → pg_hba.conf and IP restrictions.
2. What can you do inside? (Authorization) → Database Roles and Schemas.

Phase 1: Authentication (Network-Level Gatekeeping)

In PostgreSQL, we use Group Roles to manage network access. Instead of white-listing every individual IP, we white-list the “Job Function.”

1.1 Creating Network Identity Groups

We create roles that cannot log in on their own but serve as labels for our connection policies.

CREATE ROLE net_internal_dev;   -- Office LAN (Engineers)
CREATE ROLE net_data_science;   -- DS Subnet / Model Servers
CREATE ROLE net_remote_analyst; -- VPN Range (Analysts)

1.2 Hardening pg_hba.conf

We restrict access to the Local Area Network (LAN) and specific subnets. This ensures that even with a stolen password, an attacker cannot connect from a rogue IP.

File: /pg_data/data/pg_hba.conf

# TYPE  DATABASE    USER                 ADDRESS            METHOD

# Data Engineers: High-bandwidth Office LAN
host    all         +net_data_eng        192.168.10.0/24    scram-sha-256

# Data Scientists: Dedicated Research Subnet
host    ml_research +net_data_sci        192.168.20.0/24    scram-sha-256

# Analysts: Global VPN Range (Wider mask for remote work)
host    reporting   +net_analytics       10.50.0.0/16       scram-sha-256

# SECURITY GUARDRAIL: Kill all other traffic
host    all         all                  0.0.0.0/0          reject

Phase 2: Authorization (Job-Based Permissions)

We create a custom schema named warehouse to isolate production data. We then build a hierarchy based on actual job roles.

2.1 The Foundation (Business Analyst)

The Analyst needs to see the data but never change it.

CREATE SCHEMA warehouse;

CREATE ROLE role_analyst;

GRANT USAGE ON SCHEMA warehouse TO role_analyst;

GRANT SELECT ON ALL TABLES IN SCHEMA warehouse TO role_analyst;
-- Ensure future tables are automatically visible

ALTER DEFAULT PRIVILEGES IN SCHEMA warehouse GRANT SELECT ON TABLES TO role_analyst;

2.2 The Researcher (Data Scientist)

Data Scientists need to read production data AND have a private area to build features and models.

CREATE ROLE role_data_scientist;

GRANT role_analyst TO role_data_scientist; -- Inherits Read-Only access

CREATE SCHEMA ds_sandbox; -- Create a Sandbox for experimentation

GRANT ALL PRIVILEGES ON SCHEMA ds_sandbox TO role_data_scientist;

2.3 The Architect (Data Engineer)

Data Engineers manage the pipeline. They inherit read rights but gain the power to modify structures.

CREATE ROLE role_data_engineer;

GRANT role_analyst TO role_data_engineer;

GRANT ALL PRIVILEGES ON SCHEMA warehouse TO role_data_engineer;

ALTER DEFAULT PRIVILEGES IN SCHEMA warehouse GRANT ALL ON TABLES TO role_data_engineer;

Phase 3: Onboarding Users (The “Hat” System)

Now, adding a user is like giving them two “hats”: one for their network access and one for their data access.

User Case: Sarah (Senior Data Scientist)

Sarah needs to work from the DS Subnet and access both the Warehouse and her Sandbox.

CREATE ROLE sarah LOGIN PASSWORD 'vault_secret_99';

GRANT net_data_science TO sarah;    -- Connection Hat

GRANT role_data_scientist TO sarah; -- Permission Hat

User Case: Kevin (Data Engineer)

Kevin works from the Office LAN and manages the pipelines.

CREATE ROLE kevin LOGIN PASSWORD 'eng_secure_88';

GRANT net_internal_dev TO kevin;   -- Connection Hat

GRANT role_data_engineer TO kevin; -- Permission Hat

Why This Architecture Wins

• Zero-Trust by Default: By revoking public schema rights, users see nothing unless explicitly granted.
• Audit-Ready: If a table is deleted, you know it was someone with the role_data_engineer hat.
• Scalable: When the team grows from 5 to 500, you don’t change your SQL scripts; you just assign the existing roles.

Pro Tip: Final Hardening

To truly secure the database, run this to prevent anyone from using the public schema for "trash" tables:

REVOKE ALL ON SCHEMA public FROM PUBLIC;