GRANT / REVOKE

grant_statement ::= GRANT privilege_list ON object_type object_name TO grantee [ WITH GRANT OPTION ]
                  | GRANT column_privilege_list ON TABLE table_name TO grantee

revoke_statement ::= REVOKE [ GRANT OPTION FOR ] privilege_list ON object_type object_name FROM grantee
                   | REVOKE column_privilege_list ON TABLE table_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

column_privilege_list ::= column_privilege [, ...]

column_privilege ::= SELECT ( column_name [, ...] )
                   | INSERT ( column_name [, ...] )
                   | UPDATE ( column_name [, ...] )
                   | REFERENCES ( column_name [, ...] )

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 SELECT on specific columns only
GRANT SELECT (id, username, email) ON users TO readonly_role;

-- Grant UPDATE on specific columns
GRANT UPDATE (status, updated_at) ON orders TO app_role;

-- Combine table-level and column-level grants
GRANT SELECT ON users TO readonly_role;
GRANT UPDATE (email) ON users TO readonly_role;

-- 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;