Part 146: Data Control Language: Users, Roles, GRANT & REVOKE

Managing user privileges using the Principle of Least Privilege.

1. Creating a Read-Only Reporting Role

CREATE ROLE analyst_role;

GRANT CONNECT ON DATABASE devify_db TO analyst_role;
GRANT USAGE ON SCHEMA public TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_role;

-- Revoke dangerous permissions:
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM analyst_role;

Key Takeaways

• Never connect web applications using the root or postgres superuser account.

In Part 147, we explore Row-Level Security (RLS) for Multi-Tenant Software!