PART 3 · ROLES, SCHEMES, AND ACCESS RIGHTS (RBAC)
Why the service should not access the database under the "postgres" role
The Principle of Least Privilege — The Foundation of Data Security
What happens when `User=postgres` is specified in the service's .env file?
How the Role-Based Access Control (RBAC) Model Is Implemented in PostgreSQL
1. Any error or SQL injection is fatal:
• The superuser `postgres` bypasses all permission checks (`GRANT`, `REVOKE`, and Row-Level Security policies).
• An accidental call to a test cleanup command or an injection could execute `DROP SCHEMA game CASCADE` or `TRUNCATE`.
2. There is no protection for immutable logs:
• A bug in the store’s code could overwrite the price of a past purchase (UPDATE purchases) or delete a player’s receipt (DELETE FROM purchases).
3. Traffic cannot be distinguished in monitoring:
• In pg_stat_activity, all connections look the same—it’s unclear who is putting a load on the database: the backend, the migrator, or an analyst in DBeaver.
In PostgreSQL, users and groups are grouped under the concept of a ROLE:
• A role with LOGIN privileges is an account used by a service or a person to connect.
• A group role (NOLOGIN) is a set of privileges that can be granted to other roles using the command `GRANT role_name TO user_name`.
PostgreSQL checks the following three levels of permissions in this order:
1. CONNECT ON DATABASE — whether to allow access to the database at all.
2. USAGE ON SCHEMA — whether the role can view objects within the `game` schema (without this, access will be denied even if the role has permissions on the table!).
3. SELECT / INSERT / UPDATE / DELETE on specific tables, and USAGE and SELECT on sequences (SEQUENCES).
Note: The superuser `postgres` is used only for the initial creation of the database and roles—no application processes run under this user.