Lab 02: RDBMS Architecture — Mapping the Attack Surface¶

Overview¶

Understanding what a database exposes is the first step in defending it. In this lab you will perform a complete security-oriented inventory of your Neon PostgreSQL instance — querying every schema, table, extension, function, and privilege grant. This is the same reconnaissance workflow an attacker or a professional security auditor executes when first encountering a database. By the end you will have a comprehensive picture of your attack surface and will have hardened one well-known PostgreSQL default misconfiguration.

Part 1 — Complete Database Inventory¶

A full database inventory enumerates every object in every schema. Attackers run exactly these queries after gaining any authenticated foothold.

Step 1.1 — All Schemas¶

SELECT nspname AS schema_name,
       pg_get_userbyid(nspowner) AS owner
FROM pg_namespace
ORDER BY nspname;

Note every schema present — including pg_catalog, information_schema, and any user-created schemas from previous labs. Each schema is a potential target namespace.

📸 Screenshot checkpoint: Capture the full schema list.

Step 1.2 — All Tables and Their Security Flags¶

SELECT
  schemaname,
  tablename,
  tableowner,
  hasindexes,
  hasrules,
  hastriggers,
  rowsecurity
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema')
ORDER BY schemaname, tablename;

Pay particular attention to the rowsecurity column — tables with rowsecurity = false expose all rows to any role with SELECT. This will be examined again in Step 3.2.

📸 Screenshot checkpoint: Capture the full table inventory including rowsecurity values.

Step 1.3 — All Installed Extensions (Attack Surface Enumeration)¶

SELECT extname,
       extversion,
       pg_get_userbyid(extowner) AS owner
FROM pg_extension
ORDER BY extname;

Step 1.4 — All Functions and Their Security Model¶

SELECT
  n.nspname AS schema,
  p.proname AS function_name,
  CASE p.prosecdef
    WHEN TRUE THEN 'SECURITY DEFINER'
    ELSE            'SECURITY INVOKER'
  END AS security,
  pg_get_userbyid(p.proowner) AS owner
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY schema, function_name;

📸 Screenshot checkpoint: Capture the function list showing the security column for each function.

Part 2 — Privilege Inventory¶

Knowing who can do what is the core of access control auditing.

Step 2.1 — Which Roles Have Which Table Privileges¶

SELECT
  grantee,
  table_schema,
  table_name,
  string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog','information_schema')
GROUP BY grantee, table_schema, table_name
ORDER BY grantee, table_schema, table_name;

This query collapses all individual grants into one row per (grantee, table), making it easy to scan for over-privileged roles.

📸 Screenshot checkpoint: Capture the privilege summary.

Step 2.2 — Schema-Level Privileges¶

SELECT
  n.nspname AS schema,
  pg_get_userbyid(n.nspowner) AS owner,
  array_to_string(n.nspacl, ', ') AS access_control_list
FROM pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY schema;

The nspacl column (Access Control List) encodes schema-level USAGE and CREATE grants in PostgreSQL's internal ACL format. An entry like =UC means the PUBLIC pseudo-role has both USAGE and CREATE on that schema.

📸 Screenshot checkpoint: Capture the schema ACL output.

Part 3 — Security Misconfigurations¶

Step 3.1 — Find and Fix the PUBLIC Schema Risk¶

PostgreSQL versions prior to 15 granted CREATE on the public schema to PUBLIC (every authenticated user) by default. This allowed any user to inject objects — including malicious functions — into the search path of other users.

-- Check current state before hardening
SELECT has_schema_privilege('PUBLIC', 'public', 'CREATE') AS public_can_create_in_public;

-- Revoke the dangerous default (security hardening)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Verify the revocation took effect
SELECT has_schema_privilege('PUBLIC', 'public', 'CREATE') AS public_can_create_in_public;

Expected: First query returns true; after the revoke it returns false.

📸 Screenshot checkpoint: Capture both the before (true) and after (false) query results.

Step 3.2 — Find Tables Without Row-Level Security¶

SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema')
  AND rowsecurity = FALSE
ORDER BY schemaname, tablename;

📸 Screenshot checkpoint: Capture the full list of tables lacking RLS.

Cleanup / Reset¶

To remove all objects created in this lab and return to the Lab 01 baseline:

-- Nothing was created in this lab — only read and one revoke.
-- To restore the PUBLIC CREATE grant if needed for other testing:
GRANT CREATE ON SCHEMA public TO PUBLIC;

Assessment¶

Verification Script¶

Dr. Chen will run the following script against your Neon lab-02 branch connection string. All three checks must pass.

-- VERIFY LAB 02
SELECT
  has_schema_privilege('PUBLIC', 'public', 'CREATE')    AS public_create_revoked_false,

  (SELECT COUNT(*) FROM pg_extension)                   AS extension_count,

  (SELECT COUNT(*)
   FROM pg_proc p
   JOIN pg_namespace n ON n.oid = p.pronamespace
   WHERE n.nspname NOT IN ('pg_catalog','information_schema'))
                                                        AS user_function_count;

Expected results:

Additional Requirement¶

Security Inventory Report (20 pts)

Write a single SQL query that produces a unified Security Inventory Report for your Neon database. The result set must have exactly these columns: category, item, risk_level.

Requirements:

• At least one row of category = 'Extension' for each installed extension.
• At least one row of category = 'SECURITY DEFINER Function' for each SECURITY DEFINER function found (if none exist, include a single row with item = 'None found' and risk_level = 'LOW').
• One row per table that lacks RLS, with category = 'Table — No RLS'.
• Assign risk_level values of 'HIGH', 'MEDIUM', or 'LOW' based on your security judgment. Be prepared to defend your assignments.
• Use UNION ALL to combine the three (or more) SELECT statements into one result set.

Starter scaffold:

-- Security Inventory Report
SELECT
  'Extension'            AS category,
  extname || ' v' || extversion AS item,
  'MEDIUM'               AS risk_level   -- adjust based on extension
FROM pg_extension

UNION ALL

SELECT
  'SECURITY DEFINER Function' AS category,
  n.nspname || '.' || p.proname AS item,
  'HIGH'                AS risk_level
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef = TRUE
  AND n.nspname NOT IN ('pg_catalog','information_schema')

UNION ALL

SELECT
  'Table — No RLS'      AS category,
  schemaname || '.' || tablename AS item,
  'HIGH'                AS risk_level
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema')
  AND rowsecurity = FALSE

ORDER BY risk_level, category, item;

Customize the risk_level values and add additional UNION ALL blocks as you see fit. Submit: the final query and a screenshot of its output with at least 3 rows.

Reflection Questions¶

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

1. SECURITY DEFINER Escalation — You found that SECURITY DEFINER functions execute as the function owner, not the caller. Why is this a privilege escalation risk? Construct a concrete attack scenario: a low-privilege app_user role, a SECURITY DEFINER function owned by a superuser, and describe exactly what app_user can do that they should not be able to do.

2. Extensions as Attack Surface — The pg_extension catalog revealed every extension installed in your database. Why is an extension like dblink or pg_cron a security concern in a production database? Which security principle does keeping unnecessary extensions installed violate, and what is the correct remediation?

3. CVE-2018-1058 and the Public Schema — Before you ran REVOKE CREATE ON SCHEMA public FROM PUBLIC, any authenticated user could create objects in the public schema. Explain the specific attack this enables: how does a malicious user exploit PostgreSQL's search_path behavior to hijack legitimate function calls made by privileged users? Why was this rated as a significant vulnerability rather than a configuration choice?