Loading Depth Study
Preparing your workspace
PostgreSQL uses roles and privileges to control which users or application accounts can access or modify database objects.
Database security should follow the principle of least privilege. An application account should receive only the permissions it needs. PostgreSQL roles can own objects and receive privileges such as SELECT, INSERT, UPDATE, DELETE, and usage on schemas. Sensitive credentials should be stored outside source code and rotated appropriately. Strong database permissions provide an additional protection layer even if an application bug occurs.
A reporting service may only need SELECT permission, while the application service may need controlled read and write permissions.
CREATE ROLE app_reader LOGIN PASSWORD 'use-a-secure-secret'; GRANT USAGE ON SCHEMA public TO app_reader; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
A database role is created with read-only access to tables in the public schema.