How to fix 01007: privilege_not_granted in PostgreSQL

-- For table privileges on objects you can grant
SELECT grantee, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND table_name = 'your_table'
  AND grantee = current_user;

-- Check if you have WITH GRANT OPTION (is_grantable = YES)
-- If is_grantable = NO, you cannot grant these privileges to others

-- For schema privileges
SELECT grantee, privilege_type, is_grantable
FROM information_schema.schema_privileges
WHERE schema_name = 'public'
  AND grantee = current_user;

-- Connect as the table/schema owner
psql -U table_owner -d your_database

-- Or connect as the postgres superuser
psql -U postgres -d your_database

-- Then grant the privileges
GRANT SELECT, INSERT ON TABLE employees TO app_user;

-- Verify the grant succeeded
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND table_name = 'employees'
  AND grantee = 'app_user';

-- As the object owner, grant privileges WITH GRANT OPTION
GRANT SELECT, INSERT ON TABLE employees TO delegate_user WITH GRANT OPTION;

-- Now delegate_user can grant these privileges to others
GRANT SELECT, INSERT ON TABLE employees TO app_user;

-- See what roles the current user belongs to
SELECT rolname FROM pg_roles
WHERE pg_has_role(current_user, oid, 'member');

-- Check what roles you can grant
SELECT rolname FROM pg_roles
WHERE has_role_option(rolname, 'CREATEROLE')
   OR pg_has_role(current_user, oid, 'admin')
   OR current_user = 'postgres';

-- If you're a member of a role with GRANT OPTION,
-- you may be able to grant through membership
GRANT SELECT ON TABLE employees TO app_user;

-- Pattern 1: Use a setup script run as superuser or owner
-- setup.sql (run as postgres or object owner)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Pattern 2: For multi-level delegation, ensure WITH GRANT OPTION
-- Admin user grants to intermediate delegators
GRANT SELECT ON TABLE employees TO admin_user WITH GRANT OPTION;

-- Intermediate delegators can now pass it on
GRANT SELECT ON TABLE employees TO app_user;

-- Pattern 3: Create roles with appropriate permissions
CREATE ROLE app_role;
GRANT app_role TO new_user;
-- new_user inherits app_role's privileges without needing explicit grants

-- Set up logging for privilege commands
ALTER DATABASE yourdb SET log_statement = 'ddl';

-- Or monitor via event triggers
CREATE OR REPLACE FUNCTION audit_grant_operations()
RETURNS event_trigger AS $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    IF r.command_tag = 'GRANT' THEN
      INSERT INTO privilege_audit_log (event_time, username, command_text, executed_by)
      VALUES (now(), current_user, r.command, session_user);
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER audit_grants ON ddl_command_end
WHEN TAG IN ('GRANT')
EXECUTE FUNCTION audit_grant_operations();

-- Review completed grants vs. attempted grants
SELECT * FROM privilege_audit_log
WHERE event_time > now() - interval '1 day'
  AND command_text LIKE 'GRANT%'
ORDER BY event_time DESC;