GRANT, REVOKE, and Default Privileges

Revoke the PUBLIC-access default, grant precise privileges to the analyst role, and make the fix apply to tables that do not exist yet

PUBLIC is not a role you create — it is an implicit group that every role belongs to, always. Any privilege granted to PUBLIC is available to literally everyone who can connect, including roles created tomorrow. A single forgotten "GRANT ALL ON SCHEMA analytics TO PUBLIC" from an earlier setup script is enough to let a brand-new analyst SELECT, INSERT, UPDATE, and DELETE on every table in that schema, on their very first day.\n\nFixing this is a two-part job. First, revoke the broad PUBLIC grant and replace it with a precise grant to the role that actually needs access. Second — and this is the part people forget — the fix must survive the next CREATE TABLE. Without ALTER DEFAULT PRIVILEGES, every new table reverts to the PostgreSQL default: only the owner has access, and analyst has to be re-granted by hand, every single time.

Finding What PUBLIC Can Do

-- In psql, \dp lists table-level privileges
\dp analytics.customer_ltv

-- Or query it directly
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'analytics' AND table_name = 'customer_ltv'
ORDER BY grantee, privilege_type;

Revoking PUBLIC Access

-- Schema-level: stop new PUBLIC grants from mattering at all
REVOKE ALL ON SCHEMA analytics FROM PUBLIC;

-- Table-level: remove what was already granted
REVOKE ALL ON analytics.customer_ltv FROM PUBLIC;

A famous instance of this same pattern: since PostgreSQL 15, CREATE on the built-in public schema is no longer granted to PUBLIC by default — the exact fix this lab applies by hand to analytics is now the out-of-the-box behaviour for public.

Granting Precisely Instead

GRANT USAGE ON SCHEMA analytics TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst;

USAGE on the schema lets a role reference objects in it at all — without it, even an explicit table-level SELECT grant is unreachable. SELECT ... ON ALL TABLES only covers tables that exist right now.

Making It Permanent: ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO analyst;

This does not touch any existing table. It changes what happens the next time a table is created in analytics: analyst is auto-granted SELECT the moment the table exists, with no follow-up GRANT required.

One nuance that catches people out: ALTER DEFAULT PRIVILEGES only affects objects later created by the same role that ran the ALTER DEFAULT PRIVILEGES statement (or, with FOR ROLE other_role, objects created by other_role). It does not apply cluster-wide to "whoever creates a table in this schema."

PUBLIC

An implicit pseudo-role that every role is always a member of — it is never created explicitly and cannot be dropped. Any privilege granted to PUBLIC becomes available to every current role and every role created afterwards, with no further action.

ALTER DEFAULT PRIVILEGES

Changes what privileges are automatically granted on objects created in the future, by a specific role, in a specific schema (or database-wide). It never modifies privileges on objects that already exist — for those, GRANT or REVOKE must still be run directly.

🔌 Connect as postgres

Connect to beer_db as postgres to audit and fix the analytics schema.

psql -U postgres -d beer_db

SET psql (18.4) Type "help" for help. beer_db=#

🔎 Audit Table-Level Access with \dp

List privileges on analytics.customer_ltv with the psql \dp meta-command.

\dp analytics.customer_ltv

🔎 Audit Schema-Level Access

Check the analytics schema itself for PUBLIC access, using information_schema for a more readable view than \dn+.

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_schema = 'analytics' AND table_name = 'customer_ltv' AND grantee = 'PUBLIC' ORDER BY privilege_type;

beer_db=# SELECT grantee, privilege_type beer_db-# FROM information_schema.role_table_grants beer_db-# WHERE table_schema = 'analytics' AND table_name = 'customer_ltv' AND grantee = 'PUBLIC' beer_db-# ORDER BY privilege_type; grantee | privilege_type ---------+---------------- PUBLIC | DELETE PUBLIC | INSERT PUBLIC | REFERENCES PUBLIC | SELECT PUBLIC | TRIGGER PUBLIC | TRUNCATE PUBLIC | UPDATE (7 rows)

🚫 Revoke Schema-Level PUBLIC Access

Revoke every privilege PUBLIC has on the analytics schema itself, closing off USAGE and CREATE.

REVOKE ALL ON SCHEMA analytics FROM PUBLIC;

REVOKE

🚫 Revoke Table-Level PUBLIC Access

Revoke every privilege PUBLIC has on the existing customer_ltv table.

REVOKE ALL ON analytics.customer_ltv FROM PUBLIC;

REVOKE

✅ Grant Precisely to analyst

Grant analyst exactly what it needs: USAGE on the schema, and SELECT on the existing table.

GRANT USAGE ON SCHEMA analytics TO analyst;

GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst;

GRANT GRANT

📌 Set Default Privileges for Future Tables

Configure ALTER DEFAULT PRIVILEGES so any table you create in analytics from now on is automatically SELECT-granted to analyst.

ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO analyst;

beer_db=# ALTER DEFAULT PRIVILEGES IN SCHEMA analytics beer_db-# GRANT SELECT ON TABLES TO analyst; ALTER DEFAULT PRIVILEGES

🆕 Create a New Table and Prove the Fix

Create a new table in analytics, then immediately query \dp to confirm analyst already has SELECT — with no additional GRANT.

CREATE TABLE analytics.churn_risk (customer_id int, risk_score numeric(4,2));

\dp analytics.churn_risk

🧪 Confirm jane_analyst Can Read the New Table

Connect as jane_analyst and query the table that did not exist when this lab started.

\q

psql -U jane_analyst -d beer_db

SELECT * FROM analytics.churn_risk;

beer_db=# \q student@lab:~$ psql -U jane_analyst -d beer_db SET psql (18.4) Type "help" for help. beer_db=> SELECT * FROM analytics.churn_risk; customer_id | risk_score -------------+------------ (0 rows)

🚫 Confirm jane_analyst Still Cannot Write

Attempt an INSERT as jane_analyst to confirm the SELECT-only grant holds — she should not be able to write anywhere in analytics.

INSERT INTO analytics.churn_risk VALUES (1, 0.5);

ERROR: permission denied for table churn_risk

Lab 2.2.2 complete. You can now find, close, and permanently fix a PUBLIC-access exposure:\n\n\n \dp / role_table_grants : ✅ PUBLIC exposure discovered\n REVOKE ALL ... FROM PUBLIC : ✅ schema + table access closed\n GRANT to analyst : ✅ precise, minimal access restored\n ALTER DEFAULT PRIVILEGES : ✅ future tables auto-granted\n New table proof : ✅ analyst read it with zero GRANTs\n

Enable JavaScript to run the live terminal and track your progress.