Advanced level · Module 1: PostgreSQL Administration · Lesson 2 of 4
Roles, Privileges and Least Privilege
Create roles for people and applications and grant only the privileges each needs.
In this lesson you will
- Create login and group roles
- Grant and revoke privileges, including default privileges
Key concepts
roleGRANT and REVOKEschema privilegesdefault privileges
The lesson
PostgreSQL uses roles for both users and groups. Create group roles for jobs — clinician, data_officer, auditor — grant privileges to them and add people's login roles as members.
Grant only what is needed: an auditor gets SELECT, a data officer SELECT and INSERT on certain tables, the application role the specific operations it performs. Never let applications connect as a superuser.
ALTER DEFAULT PRIVILEGES makes new tables receive the right privileges automatically, so permissions do not drift as the schema grows. Review privileges regularly.
Step by step
- Create group roles for each job.
- Grant privileges table by table.
- Create login roles and add them to groups.
- Test each role's access with allowed and forbidden queries.
Practice activity
Implement the health programme's role and privilege policy.
Expected result: Each role can perform exactly its allowed operations, demonstrated by test queries.
Check your understanding
-
Why grant privileges to group roles rather than individuals?
Show answer
Access is managed by job, and people are simply added or removed from groups.
-
Should an application connect as a superuser?
Show answer
No; it should use a role with only the privileges it needs.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.