PostgreSQL manages access control through roles (which serve as both users and groups) and privileges (permissions granted on objects). This system controls who can connect, and what they can do — fundamental to database security.
ROLE app_user LOGIN PASSWORD ;
ROLE readonly;
readonly app_user;
In Postgres, a role is a unified concept — it can log in (acting as a user) and/or contain other roles (acting as a group). This flexible model handles both users and permission groups.
-- grant specific privileges on objects
GRANT SELECT ON users TO readonly; -- read-only on a table
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user; -- full data access
GRANT USAGE ON SCHEMA sales TO app_user; -- access a schema
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;
REVOKE INSERT ON orders FROM app_user; -- take away a privilege
Privileges (SELECT, INSERT, UPDATE, DELETE, USAGE, etc.) are granted on objects (tables, schemas, databases) to roles — controlling exactly what each role can do.
-- ✅ grant only the permissions each role actually needs
GRANT SELECT ON reports TO analyst; -- analyst can only READ reports
-- ❌ don't grant ALL PRIVILEGES or superuser broadly — minimize the blast radius
Least privilege — granting each role only the permissions it needs — is a core security practice that limits damage from compromised credentials or mistakes.
-- the object's OWNER has full control; new objects need explicit grants
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT ON TABLES TO readonly; -- auto-grant on FUTURE tables
Understanding PostgreSQL's roles and privileges is important for database security — controlling who can access the database and what they can do is fundamental to protecting data, so this knowledge is valuable for any production database.
Understanding the role concept (Postgres's unified model where roles serve as both users — that can log in — and groups — collections of permissions that other roles inherit) is necessary for managing access, as it's how you create database users and organize permissions.
Knowing how to grant and revoke privileges (SELECT, INSERT, etc.) on objects (tables, schemas, databases) to roles is essential for controlling exactly what each user or application can do.
Critically important is the principle of least privilege — granting each role only the permissions it genuinely needs (e.g. a read-only analyst role, an application role with only the access it requires) rather than broad or superuser permissions — which is a core security practice that limits the damage from compromised credentials, bugs, or mistakes (a real, important defense).
Understanding default privileges and ownership (the object owner has full control; future objects may need explicit grants) rounds out practical access management.
Since database security is critical (databases hold sensitive, valuable data), and since proper access control through roles and least-privilege grants is how you protect that data and limit risk, understanding PostgreSQL's roles and privileges — the unified role model, granting/revoking privileges, and especially the least-privilege principle — is important security-relevant knowledge for administering and securing production databases, a frequently-relevant responsibility and a common topic for anyone managing database access.