Lab 04: Database Authentication Hardening¶

Overview¶

Authentication is the database's first security gate — it determines who is allowed in at all. In this lab you will:

1. Inspect the default authentication state of your Neon database.
2. Create properly constrained application service roles with connection limits, password expiry, and least-privilege flags.
3. Observe how PostgreSQL stores passwords as SCRAM-SHA-256 hashes — never plaintext.
4. Practice account locking (immediate incident response) and forced password rotation.
5. Audit the privilege flags that should never appear on a service account.

Part 1 — Understanding Default Authentication¶

Step 1.1 — Inspect Current Roles¶

The pg_authid system catalog contains the authoritative role information including hashed passwords and expiry dates. Note: only superusers can read rolpassword from pg_authid.

SELECT
  rolname,
  rolsuper,
  rolcreaterole,
  rolcreatedb,
  rolcanlogin,
  rolconnlimit,
  rolpassword,
  rolvaliduntil
FROM pg_authid
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

Observe the existing roles provided by Neon (typically neon_superuser and your project user). Note which have rolcanlogin = true and what their rolconnlimit is set to.

📸 Screenshot checkpoint: Capture the full pg_authid output.

Step 1.2 — Password Encryption Algorithm¶

SHOW password_encryption;

Expected: scram-sha-256

📸 Screenshot checkpoint: Capture the password_encryption setting.

Part 2 — Creating Hardened Application Roles¶

Step 2.1 — Create Properly Constrained Roles¶

We create three roles demonstrating different security profiles:

-- Application service account:
--   minimal privileges, connection limit, expiry, no inheritance
CREATE ROLE app_service
  LOGIN
  PASSWORD 'AppService_Secure#2026'
  CONNECTION LIMIT 10
  VALID UNTIL '2027-06-30'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT;

-- Read-only reporting account:
--   low connection limit, cannot escalate
CREATE ROLE reporting_user
  LOGIN
  PASSWORD 'Reporting_Secure#2026'
  CONNECTION LIMIT 3
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE;

-- Group role for RBAC (never connects directly):
CREATE ROLE data_analyst
  NOLOGIN
  NOSUPERUSER;

What each constraint does:

Step 2.2 — Verify Constraints¶

SELECT
  rolname,
  rolcanlogin,
  rolconnlimit,
  rolvaliduntil,
  rolsuper,
  rolcreaterole,
  rolcreatedb
FROM pg_roles
WHERE rolname IN ('app_service', 'reporting_user', 'data_analyst')
ORDER BY rolname;

Confirm each role has the expected flags. app_service should show rolconnlimit = 10 and a future rolvaliduntil. data_analyst should show rolcanlogin = false.

📸 Screenshot checkpoint: Capture the three-row output confirming all constraints.

Part 3 — Password Security¶

Step 3.1 — How PostgreSQL Stores Passwords¶

-- View the SCRAM hash prefix (safe to display — this is not the plaintext password)
SELECT
  rolname,
  LEFT(rolpassword, 30) AS password_prefix,
  LENGTH(rolpassword)   AS password_hash_length
FROM pg_authid
WHERE rolname IN ('app_service', 'reporting_user');

Expected: The password_prefix column starts with SCRAM-SHA-256$ — confirming passwords are stored as salted hashes, never as plaintext or reversibly encoded values.

SCRAM-SHA-256$4096:<base64-salt>$<base64-stored-key>:<base64-server-key>

📸 Screenshot checkpoint: Capture the SCRAM hash prefix output for both roles.

Step 3.2 — Lock an Account (Immediate Security Response)¶

When a credential is believed compromised, the first response is to lock the account immediately — before rotating credentials.

-- Simulate locking a compromised account
ALTER ROLE reporting_user NOLOGIN;

-- Verify locked
SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname = 'reporting_user';

Expected: rolcanlogin = false

-- Restore access after investigation is complete
ALTER ROLE reporting_user LOGIN;

-- Verify restored
SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname = 'reporting_user';

Expected: rolcanlogin = true

📸 Screenshot checkpoint: Capture both the locked (false) and restored (true) states.

Step 3.3 — Force Password Rotation¶

Setting VALID UNTIL 'NOW' immediately expires the credential, causing the next connection attempt to fail until a new password is set.

-- Expire the account immediately (incident response or scheduled rotation)
ALTER ROLE app_service VALID UNTIL 'NOW';

SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolname = 'app_service';

Expected: rolvaliduntil is set to the current timestamp or earlier.

-- Reset with new password and updated expiry
ALTER ROLE app_service
  PASSWORD 'AppService_NewPass#2026'
  VALID UNTIL '2027-12-31';

SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolname = 'app_service';

Expected: rolvaliduntil = 2027-12-31 00:00:00+00

📸 Screenshot checkpoint: Capture both the expired and reset rolvaliduntil values.

Part 4 — Privilege Principle Verification¶

Step 4.1 — Confirm Absence of Dangerous Flags¶

A service account audit verifies that application roles do not hold any flags that could be used to escalate privileges or bypass access controls.

SELECT
  rolname,
  CASE WHEN rolsuper
    THEN 'DANGER: superuser!'
    ELSE 'OK: not superuser'
  END AS superuser_status,
  CASE WHEN rolcreaterole
    THEN 'RISK: can create roles'
    ELSE 'OK: cannot create roles'
  END AS createrole_status,
  CASE WHEN rolcreatedb
    THEN 'RISK: can create dbs'
    ELSE 'OK: cannot create dbs'
  END AS createdb_status
FROM pg_roles
WHERE rolname IN ('app_service', 'reporting_user')
ORDER BY rolname;

Expected: All status columns should show the OK: prefix for both roles.

📸 Screenshot checkpoint: Capture the privilege audit output.

Cleanup / Reset¶

To remove all roles created in this lab:

-- Full reset for Lab 04
DROP ROLE IF EXISTS app_service;
DROP ROLE IF EXISTS reporting_user;
DROP ROLE IF EXISTS data_analyst;

Assessment¶

Verification Script¶

Dr. Chen will run the following script against your Neon lab-04 branch connection string.

-- VERIFY LAB 04

SELECT
  -- app_service: can login, connection limit = 10, not superuser
  (SELECT COUNT(*)
   FROM pg_roles
   WHERE rolname      = 'app_service'
     AND rolcanlogin  = TRUE
     AND rolconnlimit = 10
     AND rolsuper     = FALSE)::INT                       AS app_service_ok,

  -- reporting_user: can login, not superuser
  (SELECT COUNT(*)
   FROM pg_roles
   WHERE rolname     = 'reporting_user'
     AND rolcanlogin = TRUE
     AND rolsuper    = FALSE)::INT                        AS reporting_user_ok,

  -- data_analyst: NOLOGIN group role
  (SELECT COUNT(*)
   FROM pg_roles
   WHERE rolname     = 'data_analyst'
     AND rolcanlogin = FALSE)::INT                        AS data_analyst_nologin,

  -- app_service password stored as SCRAM hash
  (SELECT rolpassword LIKE 'SCRAM-SHA-256%'
   FROM pg_authid
   WHERE rolname = 'app_service')                         AS using_scram;

Expected results:

Additional Requirement¶

Service Account Audit Query (20 pts)

Research and implement a service account audit query that identifies all roles in your database that violate the principle of least privilege for service accounts.

Definition of a violation: A role that has rolcanlogin = TRUE AND has any of the following dangerous flags: rolsuper = TRUE, rolcreaterole = TRUE, or rolcreatedb = TRUE.

Complete these steps:

1. Write the audit query:

-- Service Account Privilege Violation Audit
SELECT
  rolname,
  rolsuper,
  rolcreaterole,
  rolcreatedb,
  rolconnlimit,
  rolvaliduntil,
  ARRAY_REMOVE(ARRAY[
    CASE WHEN rolsuper     THEN 'SUPERUSER'     END,
    CASE WHEN rolcreaterole THEN 'CREATEROLE'   END,
    CASE WHEN rolcreatedb  THEN 'CREATEDB'      END
  ], NULL) AS violations
FROM pg_roles
WHERE rolcanlogin = TRUE
  AND (rolsuper = TRUE OR rolcreaterole = TRUE OR rolcreatedb = TRUE)
ORDER BY rolname;

1. Explain each dangerous flag (in your lab report, 2–3 sentences each):

   • What rolsuper = TRUE enables and why it is dangerous on a service account.
   • What rolcreaterole = TRUE enables and the specific privilege escalation it allows.
   • What rolcreatedb = TRUE enables and why lateral movement is a concern.

2. For any violations found, write and run ALTER ROLE statements to remediate them:

-- Example remediation template (run for each violating role found)
ALTER ROLE <rolname> NOSUPERUSER NOCREATEROLE NOCREATEDB;

1. Re-run the audit query after remediation to confirm it returns zero rows.

Submit: The audit query, its initial output, your remediation statements, and a screenshot of the empty result after remediation.

Reflection Questions¶

Answer each question in 3–5 sentences in your lab report.

1. SCRAM-SHA-256 vs bcrypt — PostgreSQL stores passwords as SCRAM-SHA-256 hashes rather than plaintext. If an attacker gains read access to pg_authid, what can they do with the SCRAM hash? How does SCRAM-SHA-256 differ architecturally from bcrypt (used in most web applications), and which provides stronger offline-attack resistance and why?

2. VALID UNTIL and Availability — The VALID UNTIL constraint causes a role's password to expire. If a database service account's credential expires and the application cannot connect, what is the business impact? How should organizations balance the security benefit of frequent credential rotation against the availability risk, and what operational controls help manage the trade-off?

3. NOINHERIT and Least Privilege — The NOINHERIT flag prevents an application role from automatically inheriting permissions from group roles it belongs to. Why might you want NOINHERIT on a service account even though it seems to make privilege management less convenient? Describe a specific scenario where INHERIT (the default) on an application service account creates an unintended privilege escalation that NOINHERIT would prevent.