Granular Database User Privileges: GRANT, Roles, Least Privilege

1. Why granular privileges are not a nice-to-have

Granular database user privileges are the first line of defense when an account is compromised, whether through a stolen password, a vulnerable application, or an insider with malicious intent. Without granular database user privileges, a single compromised account often has full read and write access to every table, every function, and sometimes even administrative rights to create new accounts.

The typical path there is convenient but dangerous: a developer creates a database account, gives it full rights under time pressure, and that account is never restricted again, because restricting it later seems risky enough to break something. Over years, dozens of accounts pile up with far more rights than necessary. Granular database user privileges reverse this pattern: every account gets, from the start, only the privileges it needs for its specific task, nothing more.

Regulatory requirements such as GDPR, PCI-DSS or ISO 27001 explicitly demand traceable database user privileges with a documented rationale for every grant. An audit meant to show who has access to personal data regularly fails on databases where ten different accounts have blanket full access to all tables, without anyone able to explain why anymore.

2. GRANT and REVOKE in detail: object and column level

GRANT and REVOKE are the fundamental SQL commands used to grant and withdraw database user privileges. GRANT operates at several levels: database, schema, table, column, and in many systems also at the function and sequence level. A common mistake is always granting database user privileges at the schema level with GRANT ALL ON SCHEMA, even though an account only actually needs three out of twenty tables.

The granular alternative grants database user privileges per table and per action: a reporting account gets only SELECT, never INSERT, UPDATE or DELETE. A batch job that exclusively writes new rows into a log table gets only INSERT on exactly that one table, no access to the rest of the schema. This precision makes every account predictable: anyone reading an account's database user privileges immediately knows the worst-case impact of a compromise.

-- Coarse-grained (avoid): full schema access for a reporting account
GRANT ALL ON SCHEMA sales TO reporting_user;   -- too broad

-- Granular database user privileges: table and action specific
GRANT SELECT ON sales.orders TO reporting_user;
GRANT SELECT ON sales.order_items TO reporting_user;
GRANT SELECT ON sales.customers TO reporting_user;
-- No INSERT, UPDATE, DELETE — reporting never writes

-- A batch job only needs to append to one log table
GRANT INSERT ON audit.import_log TO batch_import_user;
REVOKE SELECT, UPDATE, DELETE ON audit.import_log FROM batch_import_user;

-- Revoke a previously overly broad privilege
REVOKE ALL ON SCHEMA sales FROM reporting_user;

An often overlooked detail when granting database user privileges: default privileges for new objects must be configured separately. Without ALTER DEFAULT PRIVILEGES in PostgreSQL, a newly created table inherits none of the previously granted rights, which causes applications to suddenly throw access errors after every migration, simply because the new table was forgotten.

3. Role hierarchies instead of hundreds of individual grants

With more than a handful of accounts, granting database user privileges directly per user quickly becomes impractical. The solution is role hierarchies: privileges are not granted to individual users but to roles, and users get assigned to roles. If what a role can do changes, that change automatically applies to every user of that role, without having to adjust a hundred individual GRANT statements.

A typical role hierarchy for database user privileges defines base roles such as app_readonly, app_readwrite and app_admin, to which concrete privileges are assigned, and then assigns individual users to exactly one of these roles. PostgreSQL even allows nested roles, where one role inherits the rights of another, which maps complex organizational structures without maintaining privileges twice.

-- Define base roles once, grant privileges to the role, not the user
CREATE ROLE app_readonly NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_readonly;

CREATE ROLE app_readwrite NOLOGIN;
GRANT app_readonly TO app_readwrite;  -- inherits read access
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA sales TO app_readwrite;

-- Actual login users only get assigned to a role, never granted directly
CREATE ROLE analytics_service LOGIN PASSWORD 'change_me';
GRANT app_readonly TO analytics_service;

CREATE ROLE order_service LOGIN PASSWORD 'change_me';
GRANT app_readwrite TO order_service;

-- Changing what app_readonly can do updates every member automatically
GRANT SELECT ON sales.new_reporting_view TO app_readonly;

4. Implementing the principle of least privilege in practice

The principle of least privilege demands that every account has exactly the database user privileges strictly necessary for its task, nothing more. In practice, implementation starts with an inventory: for every existing account, document what operations it actually performs, usually through query logs or application code analysis, and reconcile current database user privileges against this need.

A practical approach for new systems is the reverse path: an account starts with zero database user privileges, and rights are only added once a concrete error message ("permission denied") shows that an operation is actually required. This reactive but disciplined approach prevents the accumulation of unused rights that nobody ever tears down again later.

Time-limited database user privileges are a complement for exceptional situations: a developer who needs to run a one-time data correction gets a temporary GRANT that is automatically withdrawn again after a defined time window via cron job or database event, instead of keeping permanently elevated privileges.

5. Column level: column-level grants for sensitive fields

Not every access restriction can be solved at the table level. A customer table often contains both non-critical fields such as name and address and highly sensitive fields such as credit card data or social security numbers. Column-level database user privileges allow granting an account access to the non-critical columns while sensitive columns remain completely invisible.

In PostgreSQL and most relational databases, GRANT SELECT (column1, column2) ON table supports exactly this restriction. A support agent account can thus view names and order status without ever seeing payment data, even on a direct SELECT * attempt, which simply gets rejected with a permission error for the non-granted columns.

-- Column-level database user privileges: expose only safe fields
GRANT SELECT (id, first_name, last_name, order_status)
  ON customers TO support_agent;

-- Sensitive columns remain completely inaccessible
-- SELECT * FROM customers; -- fails: permission denied for column ssn

-- Combine with a view for even cleaner separation
CREATE VIEW customers_support_view AS
  SELECT id, first_name, last_name, order_status FROM customers;
GRANT SELECT ON customers_support_view TO support_agent;
REVOKE SELECT ON customers FROM support_agent;

6. Cleanly separating service accounts and application roles

A pattern often missing in grown systems: separate database user privileges per microservice or application part, instead of a single shared database account for the entire application. If a payment service and a product catalog service use the same account, a vulnerability in the less critical catalog service automatically gains access to payment data, because database user privileges are not separated by service.

Separate service accounts with their own, minimal database user privileges per service limit the blast radius of a compromised service to exactly the tables that service genuinely needs. In Kubernetes environments, this pattern combines well with secrets management, so every pod only receives credentials for its own, minimally privileged service account, never a shared master account.

-- Separate service accounts, each with its own scoped privileges
CREATE ROLE payment_service LOGIN PASSWORD 'change_me';
GRANT SELECT, INSERT, UPDATE ON payments, payment_methods TO payment_service;

CREATE ROLE catalog_service LOGIN PASSWORD 'change_me';
GRANT SELECT ON products, categories TO catalog_service;
GRANT INSERT, UPDATE ON products TO catalog_service;

-- catalog_service has zero visibility into payment tables
-- A compromised catalog_service cannot read payments at all

7. Privilege audits: who can do what, and why

Granular database user privileges without regular auditing decay over time into exactly the state they were meant to prevent. Rights granted once for a project remain in place long after the project ended. A periodic audit queries the database's system catalogs and lists, for every account, exactly which database user privileges are currently active.

In PostgreSQL, information_schema.table_privileges and information_schema.role_table_grants deliver exactly this overview. An automated script that regularly compares this view against a documented target list uncovers deviations before they become a security risk, for instance when an account suddenly has more rights than in the last documented review.

-- Audit query: which database user privileges exist per role
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name;

-- Find accounts with dangerously broad privileges
SELECT grantee, count(*) AS granted_tables
FROM information_schema.role_table_grants
WHERE privilege_type IN ('DELETE', 'TRUNCATE')
GROUP BY grantee
HAVING count(*) > 5
ORDER BY granted_tables DESC;

8. Common mistakes when granting privileges

The most common mistake: running all database user privileges through a single superuser account, because setting up separate roles takes more effort at first. That effort is minimal compared to the damage of a compromised superuser account. A second common mistake: rights are granted generously "to save time," with the intention of restricting them later, which in practice almost never happens because there is no concrete occasion left to do so.

A third mistake concerns forgotten default privileges: new tables in PostgreSQL inherit none of the previously granted database user privileges without ALTER DEFAULT PRIVILEGES, which leads to inconsistent states where older tables are restrictive but newer tables are accidentally open to all accounts because nobody explicitly included the new table in the privilege setup.

9. Privilege models compared

Different approaches to granting database user privileges differ significantly in maintenance overhead and security level. The table shows which pattern is the better choice in which situation.

In all cases: role-based, granular database user privileges with regular audits are more predictable and secure than individually granted rights that pile up over years until eventually nobody has a complete overview anymore.

10. Summary

Granular database user privileges are not a bureaucratic exercise but the most effective measure to limit the damage of a compromised account. GRANT and REVOKE at table, action and column level, combined with role hierarchies instead of individual grants per user, make privilege management traceable and centrally changeable.

The principle of least privilege demands starting every account with minimal rights and expanding only on proven need, never the reverse. Separate service accounts per application part limit the blast radius of a compromise, and regular audits against the database's system catalogs ensure that database user privileges do not sprawl uncontrolled over years.

11. FAQ: Granular Database User Privileges