Roles, Grants, and Least Privilege
PostgreSQL privileges govern actions such as connecting to a database, using a schema, selecting from a table, and inserting or updating rows. A role may inherit permissions from other roles. Separate runtime application access from migration or administrative access, and avoid using a superuser role for ordinary application queries.
Remember that schema privileges and table privileges are different. A role may need USAGE on a schema and specific privileges on its tables. Default privileges affect objects created in the future by a particular role; they do not retroactively grant access to all existing objects.
Grant narrowly scoped access
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE customers TO app_user;💡 The example grants only selected table operations. Production permissions should match the application's responsibilities and may require sequence privileges or default privileges depending on how IDs are generated and objects are created.
Key Takeaways
- Avoid superuser privileges for application runtime roles.
- Schema USAGE and table privileges are separate.
- Default privileges affect future objects, not all existing objects.
Knowledge Check
Which principle should govern production application permissions?