GRANT / REVOKE

grant_statement ::= GRANT privilege_list ON object_type object_name TO grantee [ WITH GRANT OPTION ]

revoke_statement ::= REVOKE [ GRANT OPTION FOR ] privilege_list ON object_type object_name FROM grantee

privilege_list ::= privilege [, ...]

privilege ::= SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER   -- for tables
            | USAGE | SELECT | UPDATE                                                 -- for sequences
            | EXECUTE                                                                 -- for functions/procedures
            | USAGE                                                                   -- for types/domains

object_type ::= TABLE | SEQUENCE | FUNCTION | PROCEDURE | TYPE

object_name ::= [schema.]name
              | [schema.]function_name(argument_types)   -- for functions/procedures

grantee ::= role_name | PUBLIC

-- Grant SELECT to a role
GRANT SELECT ON users TO readonly_role;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_role;

-- Grant with ability to re-grant
GRANT SELECT ON products TO admin_role WITH GRANT OPTION;

-- Grant EXECUTE on a function (requires full signature)
GRANT EXECUTE ON FUNCTION calculate_total(integer, numeric) TO api_role;

-- Revoke default PUBLIC access to a function
REVOKE EXECUTE ON FUNCTION get_user_data(integer) FROM PUBLIC;

-- Grant USAGE and SELECT on a sequence
GRANT USAGE, SELECT ON SEQUENCE order_id_seq TO app_role;

-- Grant USAGE on a domain type
GRANT USAGE ON TYPE email_address TO app_role;

-- Change privileges: revoke INSERT, add UPDATE and DELETE
-- Before: GRANT SELECT, INSERT ON inventory TO app_role;
-- After:  GRANT SELECT, UPDATE, DELETE ON inventory TO app_role;
-- Migration generates:
REVOKE INSERT ON TABLE inventory FROM app_role;
GRANT DELETE, UPDATE ON TABLE inventory TO app_role;

-- Revoke only the grant option, keeping the privilege
-- Before: GRANT SELECT ON employees TO manager_role WITH GRANT OPTION;
-- After:  GRANT SELECT ON employees TO manager_role;
-- Migration generates:
REVOKE GRANT OPTION FOR SELECT ON TABLE employees FROM manager_role;

-- For granting
GRANT privilege_list ON object_type object_name TO grantee[ WITH GRANT OPTION];

-- For revoking
REVOKE privilege_list ON object_type object_name FROM grantee;

-- For revoking only grant option
REVOKE GRANT OPTION FOR privilege_list ON object_type object_name FROM grantee;