> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# GRANT / REVOKE

## Syntax

```sql theme={null}
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
```

pgschema understands the following `GRANT`/`REVOKE` features:

* **Table privileges**: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
* **Column-level privileges**: SELECT, INSERT, UPDATE, REFERENCES on specific columns
* **Sequence privileges**: USAGE, SELECT, UPDATE
* **Function/Procedure privileges**: EXECUTE (with function signature)
* **Type/Domain privileges**: USAGE
* **WITH GRANT OPTION**: Allow grantee to grant the same privileges to others
* **REVOKE GRANT OPTION FOR**: Revoke only the grant option while keeping the privilege
* **PUBLIC**: Special grantee representing all roles

## Examples

### Grant table privileges

```sql theme={null}
-- 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 column-level privileges

```sql theme={null}
-- 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 function privileges

```sql theme={null}
-- 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 sequence privileges

```sql theme={null}
-- Grant USAGE and SELECT on a sequence
GRANT USAGE, SELECT ON SEQUENCE order_id_seq TO app_role;
```

### Grant type privileges

```sql theme={null}
-- Grant USAGE on a domain type
GRANT USAGE ON TYPE email_address TO app_role;
```

### Alter privileges

```sql theme={null}
-- 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 grant option

```sql theme={null}
-- 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;
```

## Canonical Format

When generating migration SQL, pgschema produces privileges in the following canonical format:

```sql theme={null}
-- 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;
```

**Key characteristics of the canonical format:**

* Always includes explicit `TABLE` keyword for table privileges
* Privileges are sorted alphabetically (e.g., `DELETE, INSERT, SELECT, UPDATE`)
* Function signatures include parameter names when available
* Each privilege change is a separate statement (no combining multiple roles)