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 privileges on a database object without having the proper authority to do so. Specifically, this happens when the grantor does not own the object and does not hold the relevant privilege WITH GRANT OPTION. This error is commonly encountered in complex multi-tenant environments or role-based access control systems where privilege delegation chains are involved.

---

Top 3 Causes

1. Attempting to Re-Grant a Privilege Without GRANT OPTION

The most frequent cause: a user received a privilege but without WITH GRANT OPTION, and then tries to pass that privilege on to another user.

-- This works: superuser grants SELECT without GRANT OPTION
GRANT SELECT ON TABLE public.orders TO middle_user;

-- This FAILS: middle_user tries to grant to another user
-- ERROR: 0L000 invalid grantor
SET ROLE middle_user;
GRANT SELECT ON TABLE public.orders TO end_user; -- ERROR here
RESET ROLE;

-- Fix: Re-grant WITH GRANT OPTION
RESET ROLE; -- back to superuser
GRANT SELECT ON TABLE public.orders TO middle_user WITH GRANT OPTION;

-- Now this works
SET ROLE middle_user;
GRANT SELECT ON TABLE public.orders TO end_user; -- SUCCESS
RESET ROLE;

2. Non-Owner Granting Privileges on an Object

In PostgreSQL, only the object owner or a superuser can freely grant privileges. A non-owner user without GRANT OPTION cannot issue GRANT statements on that object.

-- Check object ownership
SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public' AND tablename = 'sales';

-- Non-owner attempting to grant (FAILS)
SET ROLE non_owner_user;
GRANT SELECT ON TABLE public.sales TO analyst; -- ERROR: 0L000
RESET ROLE;

-- Fix: Change ownership or have the owner grant WITH GRANT OPTION
ALTER TABLE public.sales OWNER TO correct_owner;

-- Or, have the owner delegate with grant option
SET ROLE correct_owner;
GRANT SELECT ON TABLE public.sales TO non_owner_user WITH GRANT OPTION;
RESET ROLE;

3. Broken Privilege Chain in Role Hierarchies

When roles are nested, WITH GRANT OPTION is not automatically inherited through the role hierarchy. Each link in the chain must explicitly carry the grant option.

-- Create role hierarchy
CREATE ROLE top_role;
CREATE ROLE mid_role;
CREATE ROLE end_role;

-- Superuser grants to top_role WITHOUT grant option
GRANT SELECT ON TABLE public.reports TO top_role;

-- mid_role is a member of top_role but still cannot re-grant
GRANT top_role TO mid_role;

SET ROLE mid_role;
GRANT SELECT ON TABLE public.reports TO end_role; -- ERROR: 0L000
RESET ROLE;

-- Fix: Rebuild the chain with GRANT OPTION at each step
GRANT SELECT ON TABLE public.reports TO top_role WITH GRANT OPTION;

SET ROLE top_role;
GRANT SELECT ON TABLE public.reports TO mid_role WITH GRANT OPTION;
RESET ROLE;

SET ROLE mid_role;
GRANT SELECT ON TABLE public.reports TO end_role; -- SUCCESS
RESET ROLE;

---

Quick Fix Solutions

-- 1. Check current grant options on a table
SELECT grantor, grantee, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_name = 'your_table'
  AND table_schema = 'public';

-- 2. Revoke and re-grant with proper GRANT OPTION (as superuser)
REVOKE ALL ON TABLE public.your_table FROM problem_user;
GRANT SELECT, INSERT ON TABLE public.your_table TO problem_user WITH GRANT OPTION;

-- 3. Grant on all tables in schema with delegation rights
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role WITH GRANT OPTION;

-- 4. Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO reporting_role;

---

Prevention Tips

Audit grant options regularly. Schedule a periodic query to review which roles hold WITH GRANT OPTION and ensure the privilege delegation structure matches your intended design.

-- Regular audit query
SELECT table_schema, table_name, grantor, grantee, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE is_grantable = 'YES'
ORDER BY table_schema, table_name;

Apply the Principle of Least Privilege. Only issue WITH GRANT OPTION when absolutely necessary. Design a clear role hierarchy upfront — document which roles can delegate privileges and to whom — and avoid ad-hoc GRANT statements in production without a review process. Use intermediate roles to keep the permission chain clean and auditable.

---

📖 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.