PostgreSQL 0L000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 0L000: invalid grantor

The 0L000 invalid grantor error in PostgreSQL occurs when a user attempts to grant or revoke privileges, but the system determines that the user acting as the grantor is not valid or does not hold the necessary grant authority. This typically happens when a user tries to delegate a privilege they received without the WITH GRANT OPTION, or when the grant chain has been broken by a prior REVOKE. Understanding this error is critical for DBAs managing complex role hierarchies in production environments.

---

Top 3 Causes

1. Missing WITH GRANT OPTION

The most common cause. A user who received a privilege without WITH GRANT OPTION cannot pass that privilege to another user.

-- Check if a user has GRANT OPTION on a table
SELECT grantee, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_name = 'orders'
  AND grantee = 'app_user';

-- Fix: Re-grant WITH GRANT OPTION from the object owner or superuser
GRANT SELECT ON TABLE orders TO app_user WITH GRANT OPTION;

-- Now app_user can delegate the privilege
SET ROLE app_user;
GRANT SELECT ON TABLE orders TO readonly_user;
RESET ROLE;

2. Broken Grant Chain

When an intermediate role in a privilege delegation chain has its WITH GRANT OPTION revoked, all downstream grants made through that role become invalid.

-- Inspect the full grant chain for a table
SELECT
    pr.rolname AS grantor,
    pe.rolname AS grantee,
    dp.privilege_type,
    dp.is_grantable
FROM pg_class c
JOIN LATERAL aclexplode(c.relacl) dp ON TRUE
JOIN pg_roles pr ON pr.oid = dp.grantor
JOIN pg_roles pe ON pe.oid = dp.grantee
WHERE c.relname = 'orders';

-- Fix: Rebuild the chain from the top
GRANT SELECT ON TABLE orders TO role_a WITH GRANT OPTION;

SET ROLE role_a;
GRANT SELECT ON TABLE orders TO role_b WITH GRANT OPTION;
RESET ROLE;

3. Acting as a Role Without SET ROLE

PostgreSQL evaluates the grantor based on the current effective role of the session. If you are a member of a role but haven't switched to it via SET ROLE, you cannot grant privileges as that role.

-- Check current session role context
SELECT current_user, current_role, session_user;

-- Wrong: trying to grant as admin_role without switching
-- This may trigger 0L000 if session role lacks GRANT OPTION
GRANT INSERT ON TABLE orders TO dev_user; -- may fail

-- Fix: Switch role first
SET ROLE admin_role;
GRANT INSERT ON TABLE orders TO dev_user;
RESET ROLE;

---

Quick Fix Solutions

-- 1. Identify all grantable privileges in your schema
SELECT grantee, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND is_grantable = 'YES'
ORDER BY table_name, grantee;

-- 2. As superuser, safely re-establish broken grant chains
-- Revoke the broken grants first, then re-grant cleanly
REVOKE SELECT ON TABLE orders FROM role_b;
REVOKE SELECT ON TABLE orders FROM role_a;

GRANT SELECT ON TABLE orders TO role_a WITH GRANT OPTION;
SET ROLE role_a;
GRANT SELECT ON TABLE orders TO role_b;
RESET ROLE;

---

Prevention Tips

1. Standardize on Role-Based Access Control (RBAC)
Never grant privileges directly to individual users. Instead, create well-defined roles, assign privileges to roles with documented grant chains, and add users as role members. This keeps the grantor hierarchy flat and predictable, drastically reducing the chance of a broken grant chain triggering 0L000.

-- Example: Clean RBAC setup
CREATE ROLE readonly_role;
CREATE ROLE readwrite_role;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO readwrite_role;

-- Assign users to roles, never grant directly to users
GRANT readonly_role TO analyst_user;
GRANT readwrite_role TO dev_user;

2. Audit Privilege Chains Regularly
Schedule periodic audits of your privilege structure using information_schema and pg_catalog views. Catch broken grant chains before they cause runtime errors in production.

-- Weekly audit: find privileges without a valid grantable source
SELECT grantee, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND is_grantable = 'NO'
  AND grantee NOT IN (SELECT rolname FROM pg_roles WHERE rolsuper = TRUE)
ORDER BY table_name;

---

Related Errors

• 0LP01 invalid_grant_operation — Raised when a GRANT/REVOKE is attempted on an object type that does not support that operation; part of the same 0L error class.
• 42501 insufficient_privilege — A more fundamental error indicating the user lacks the privilege entirely, as opposed to being an invalid grantor.
• 2BP01 dependent_objects_still_exist — Triggered when dropping a role that still owns active grant entries; if ignored, often leads to 0L000 errors afterward.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.