What is the principle of least privilege, and how do you apply it to database user management?

Question

Accepted Answer

The principle of least privilege states that every user, application, and process should have only the minimum permissions necessary to perform their intended function. This is the most important security principle a junior DBA should understand and apply because it limits the potential damage from both accidental mistakes and malicious actions.

In database terms, this means granting users access only to the specific databases, schemas, tables, and operations they need. A reporting application that only reads data should have SELECT permissions and nothing else. A web application that reads and writes customer data should have SELECT, INSERT, UPDATE, and DELETE on the customer-related tables but not on administrative or audit tables. A developer working on a specific feature should not have access to the entire production database.

Implementing least privilege starts with understanding the different privilege levels available. In PostgreSQL, privileges can be granted at the database level, schema level, table level, column level, and function level. The GRANT statement assigns privileges and the REVOKE statement removes them. For example, GRANT SELECT ON TABLE customers TO reporting_user gives the reporting user read-only access to the customers table. GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO web_app gives the web application write access to all tables in the app schema.

Roles are the primary mechanism for managing privileges efficiently. Rather than granting permissions directly to individual users, create roles that represent job functions. A read_only role might have SELECT on all relevant tables. A data_entry role adds INSERT and UPDATE. An admin role adds full privileges. Users are then assigned to appropriate roles. When someone changes job function, you update their role assignment rather than modifying individual privileges.

In PostgreSQL, roles serve as both users and groups. You create a login role for individual users and non-login roles for groups. Users inherit the privileges of the roles they belong to. This hierarchical approach scales well as the number of users grows.

Application accounts deserve special attention. Applications should never connect to the database using superuser or administrative credentials. Create dedicated service accounts with exactly the permissions the application needs. If the application is compromised, the attacker's access is limited to what the application account can do rather than having full control of the database.

Regularly review and audit privileges. Users accumulate permissions over time, especially when they change roles within the organization. Schedule quarterly privilege reviews where you compare each user's current permissions against their actual job requirements and revoke anything unnecessary. Most databases provide system views or functions that list all granted privileges for audit purposes.

Default deny is the companion to least privilege. New users should have no permissions by default, and access should be explicitly granted as needed. In PostgreSQL, revoking the default CREATE privilege on the public schema from PUBLIC prevents new users from creating objects without explicit permission.