Section 2 of 3 · 7 minutes
Grant only the required operations
Your goal
What you will be able to do
Translate an application requirement into the smallest useful schema and table privileges.
Why this matters at work
The practical reason
An application that only reads reports should not be able to delete tables or change unrelated data.
Learn
The idea in plain English
Start from the actions the workload must perform: connect, use a schema, read tables, change selected tables, or execute named functions. Grant only those actions.
Schema USAGE lets a role resolve objects in that schema, but it does not grant table access. Default privileges control future objects and must be set by the role that will create them.
Remember these points
- Grant CONNECT, schema USAGE, and object privileges separately.
- Avoid broad grants to PUBLIC unless they are intentional.
- Plan privileges for future objects as well as current objects.
See it in SQL
Check effective table privileges
Confirm what an application role can really do to a table.
SELECT
has_table_privilege('reporting_app', 'reports.daily_totals', 'SELECT') AS can_select,
has_table_privilege('reporting_app', 'reports.daily_totals', 'INSERT') AS can_insert,
has_table_privilege('reporting_app', 'reports.daily_totals', 'DELETE') AS can_delete;The `has_table_privilege` function evaluates effective access, including inherited privileges.
For a read-only reporting role, `can_select` should be true while insert and delete should be false.
A row of booleans showing SELECT allowed and unneeded write operations denied.
Useful words
Important terms
- Least privilege
- Giving a role only the access required for its current job.
Quick check · Not graded
Check your understanding
What does schema USAGE allow by itself?
Your progress is saved to your signed-in account.