SQL 90 🛢️ User Access Control: GRANT and REVOKE - KandZ

⏱️ Estimated read: 15 min | |

A database is a shared resource. The application connects with one account, the reporting tool with another, the migration script with a third. Each connection can read some tables, write others, and is denied the rest. The mechanism that defines this is the access control: the GRANT gives the privilege, the REVOKE takes it away, and the roles group the privileges into the named sets. The four questions are: who is the user, what is the object, what is the privilege, and can the user pass it on. The answers form the permission model, and the model is the first line of defense against the accidental and the malicious.

Key point: A privilege is the right to perform a specific operation on a specific object. The GRANT adds the privilege, and the REVOKE removes it. The privilege is granted to a user or a role, and the object is a table, a view, a sequence, a schema, or the database itself.

Why the access control exists

The least-privilege problem. The application does not need the DROP TABLE. The reporting tool does not need the INSERT. The developer does not need the production database. The privilege is granted at the granularity of the operation, and the account holds only what it needs.

The separation problem. The read-only account and the read-write account are separate. The reporting queries use the read-only, and the application uses the read-write. The compromise of the reporting account does not allow the data modification.

The audit problem. The privilege is the record of what the account can do. The review of the grants answers the question “who can delete the customer records?” and the answer is the list of the accounts with the DELETE on the customers table.

The role problem. The privileges are the fine-grained, and the roles are the named bundles. The app_read role holds the SELECT on the application tables, and the new account is granted the role. The management is the one grant, not the twenty.

The revocation problem. The employee leaves, and the account must be disabled. The REVOKE removes the privileges, and the DROP ROLE removes the account. The revocation is the immediate, and the audit trail is the record.

The default problem. The new database has the public role with the default privileges. The PostgreSQL grants the CONNECT and the TEMPORARY to the public, and the rest is the denied. The default is the starting point, and the grants are the explicit.

The inheritance problem. The roles inherit. The app_write role is granted to the app_user role, and the account with the app_user inherits the write. The chain is the GRANT on the role, and the REVOKE breaks it.

a. The GRANT

The GRANT adds a privilege to a user or a role. The syntax is GRANT privilege ON object TO principal.

The basic grant

GRANT SELECT ON customers TO reporting;
GRANT INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT ALL PRIVILEGES ON orders TO app_admin;

The SELECT grants the read, the INSERT, UPDATE, DELETE grants the modification, and the ALL PRIVILEGES grants every applicable operation.

The privilege list

Privilege	Applies to	Meaning
`SELECT`	Tables, views, sequences	Read the rows
`INSERT`	Tables, views	Add the rows
`UPDATE`	Tables, views	Modify the rows
`DELETE`	Tables, views	Remove the rows
`TRUNCATE`	Tables	Empty the table
`REFERENCES`	Tables	Create the foreign key
`TRIGGER`	Tables	Create the trigger
`CREATE`	Schemas, databases	Create the objects
`CONNECT`	Databases	Connect
`TEMPORARY`	Databases	Create the temp tables
`EXECUTE`	Functions, procedures	Call the routine
`USAGE`	Sequences, schemas, types	Use the object
`ALL PRIVILEGES`	Any	Every applicable

The grant to a role

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT app_user TO alice;

The first grant gives the privileges to the role, and the second grants the role to the user. The user inherits the privileges.

The WITH GRANT OPTION

GRANT SELECT ON customers TO alice WITH GRANT OPTION;

The WITH GRANT OPTION allows the alice to grant the SELECT to the other users. The privilege is the transitive, and the revocation cascades.

The grant on the schema

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_user;

The USAGE on the schema is required before the objects are accessible. The ALL TABLES IN SCHEMA grants the SELECT on every current table, and the ALTER DEFAULT PRIVILEGES covers the future tables.

The default privileges

ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO reporting;

The ALTER DEFAULT PRIVILEGES sets the privilege for the objects created in the future.

The grant on the sequence

GRANT USAGE, SELECT ON SEQUENCE order_id_seq TO app_user;

The USAGE allows the nextval, and the SELECT allows the currval and the lastval.

The grant on the function

GRANT EXECUTE ON FUNCTION process_order(INT) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_user;

The EXECUTE allows the call, and the function’s SECURITY DEFINER runs with the owner’s privileges.

The GRANT table

Object	Privileges
Table	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`
View	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
Sequence	`USAGE`, `SELECT`, `UPDATE`
Schema	`USAGE`, `CREATE`
Database	`CONNECT`, `CREATE`, `TEMPORARY`
Function	`EXECUTE`

b. The REVOKE

The REVOKE removes a privilege. The syntax is the mirror of the GRANT.

The basic revoke

REVOKE SELECT ON customers FROM reporting;
REVOKE INSERT, UPDATE, DELETE ON orders FROM app_user;
REVOKE ALL PRIVILEGES ON orders FROM app_user;

The REVOKE removes the privilege, and the account loses the access.

The revoke of the grant option

REVOKE GRANT OPTION FOR SELECT ON customers FROM alice;

The GRANT OPTION FOR removes only the ability to grant the privilege. The alice keeps the SELECT but cannot pass it on.

The cascade revoke

REVOKE SELECT ON customers FROM alice CASCADE;

The CASCADE removes the privilege and every privilege that the alice granted. The RESTRICT is the default, and it errors if the dependent grants exist.

The revoke of the role

REVOKE app_user FROM alice;

The REVOKE removes the role, and the alice loses the inherited privileges.

The revoke on the schema

REVOKE USAGE ON SCHEMA app FROM app_user;
REVOKE SELECT ON ALL TABLES IN SCHEMA app FROM app_user;

The REVOKE on the schema and the tables.

The revoke table

Action	Effect
`REVOKE privilege`	Remove the privilege
`REVOKE GRANT OPTION FOR`	Remove the grant ability
`REVOKE ... CASCADE`	Remove and the dependent
`REVOKE ... RESTRICT`	Error if the dependent

c. The roles

A role is the named collection of the privileges. The role can be granted to the users and to the other roles.

The role creation

CREATE ROLE app_read;
CREATE ROLE app_write;
CREATE ROLE app_admin;
CREATE ROLE alice LOGIN PASSWORD 'secret';

The CREATE ROLE creates the role, and the LOGIN makes it the account.

The role privileges

GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_write;
GRANT app_read TO app_write;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_admin;
GRANT app_write TO app_admin;

The app_write inherits the app_read, and the app_admin inherits the app_write. The chain is the role hierarchy.

The role grant

GRANT app_read TO alice;
GRANT app_write TO bob;
GRANT app_admin TO carol;

The accounts inherit the roles’ privileges.

The role attributes

CREATE ROLE alice
    LOGIN
    PASSWORD 'secret'
    VALID UNTIL '2026-12-31'
    CONNECTION LIMIT 10;

CREATE ROLE app_read
    NOLOGIN
    NOINHERIT;

The LOGIN allows the connection, the PASSWORD sets the password, the VALID UNTIL sets the expiry, and the CONNECTION LIMIT limits the concurrent.

The role membership

GRANT app_read TO alice WITH ADMIN OPTION;

The WITH ADMIN OPTION allows the alice to grant the role to the other users.

The role inspection

\du
\du alice
SELECT * FROM pg_roles;
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'alice';

The \du is the psql command, and the pg_roles is the catalog view.

The current user and the session

SELECT current_user;
SELECT session_user;
SET ROLE app_read;
RESET ROLE;

The current_user is the effective, and the session_user is the original. The SET ROLE switches the effective for the session.

The role table

Aspect	Purpose
`CREATE ROLE`	Create the role
`GRANT`	Grant the role
`REVOKE`	Revoke the role
`DROP ROLE`	Remove the role
`SET ROLE`	Switch the effective
`WITH ADMIN OPTION`	The grant ability

Complete Example Session

-- ============================================
-- PART 1: THE BASIC GRANT
-- ============================================

GRANT SELECT ON customers TO reporting;
GRANT INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT ALL PRIVILEGES ON orders TO app_admin;

-- ============================================
-- PART 2: THE WITH GRANT OPTION
-- ============================================

GRANT SELECT ON customers TO alice WITH GRANT OPTION;

-- ============================================
-- PART 3: THE REVOKE
-- ============================================

REVOKE SELECT ON customers FROM reporting;
REVOKE ALL PRIVILEGES ON orders FROM app_user;
REVOKE GRANT OPTION FOR SELECT ON customers FROM alice;

-- ============================================
-- PART 4: THE ROLE CREATION
-- ============================================

CREATE ROLE app_read NOLOGIN;
CREATE ROLE app_write NOLOGIN;
CREATE ROLE app_admin NOLOGIN;

-- ============================================
-- PART 5: THE ROLE PRIVILEGES
-- ============================================

GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_write;
GRANT app_read TO app_write;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_admin;
GRANT app_write TO app_admin;

-- ============================================
-- PART 6: THE ROLE GRANT
-- ============================================

GRANT app_read TO alice;
GRANT app_write TO bob;
GRANT app_admin TO carol;

-- ============================================
-- PART 7: THE ACCOUNT CREATION
-- ============================================

CREATE ROLE alice
    LOGIN
    PASSWORD 'secret'
    VALID UNTIL '2026-12-31'
    CONNECTION LIMIT 10;

GRANT app_write TO alice;

-- ============================================
-- PART 8: THE SCHEMA PRIVILEGES
-- ============================================

GRANT USAGE ON SCHEMA app TO app_read, app_write, app_admin;
GRANT USAGE, SELECT ON SEQUENCE order_id_seq TO app_write;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_write;

-- ============================================
-- PART 9: THE DEFAULT PRIVILEGES
-- ============================================

ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO app_read;

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- 1. Create the roles for the access levels.
-- 2. Grant the privileges to the roles.
-- 3. Grant the roles to the accounts.
-- 4. Use the WITH GRANT OPTION sparingly.
-- 5. Use the REVOKE for the immediate removal.
-- 6. Use the ALTER DEFAULT PRIVILEGES for the future.
-- 7. Use the SET ROLE for the testing.
-- 8. Audit the grants for the compliance.
-- 9. Use the least privilege for the application.
-- 10. Drop the role when the account leaves.

The ten parts showed the basic grant, the WITH GRANT OPTION, the revoke, the role creation, the role privileges, the role grant, the account creation, the schema privileges, the default privileges, and the complete pattern.

Quick Reference

The Privileges

Privilege	Applies to
`SELECT`	Tables, views, sequences
`INSERT`	Tables, views
`UPDATE`	Tables, views
`DELETE`	Tables, views
`TRUNCATE`	Tables
`REFERENCES`	Tables
`TRIGGER`	Tables
`CREATE`	Schemas, databases
`CONNECT`	Databases
`EXECUTE`	Functions, procedures
`USAGE`	Sequences, schemas

The GRANT Syntax

Form	Purpose
`GRANT p ON o TO u`	The basic grant
`GRANT p ON o TO u WITH GRANT OPTION`	The grant ability
`GRANT r TO u`	The role grant
`GRANT p ON ALL TABLES IN SCHEMA s TO u`	The bulk grant

The REVOKE Syntax

Form	Purpose
`REVOKE p ON o FROM u`	The basic revoke
`REVOKE GRANT OPTION FOR p ON o FROM u`	The grant ability
`REVOKE p ON o FROM u CASCADE`	The cascade
`REVOKE r FROM u`	The role revoke

The Role Attributes

Attribute	Purpose
`LOGIN`	Allow the connection
`PASSWORD`	Set the password
`VALID UNTIL`	The expiry
`CONNECTION LIMIT`	The concurrent limit
`NOLOGIN`	The group role
`NOINHERIT`	The no inheritance

The Inspection

Command	Purpose
`\du`	The psql roles
`\dp`	The psql privileges
`pg_roles`	The role catalog
`information_schema.role_table_grants`	The grants

Best Practices

✅ Do This:

-- Create the roles for the access levels.
CREATE ROLE app_read NOLOGIN;  -- ✅

-- Grant the privileges to the roles.
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;  -- ✅

-- Grant the roles to the accounts.
GRANT app_read TO alice;  -- ✅

-- Use the schema USAGE before the object grants.
GRANT USAGE ON SCHEMA app TO app_read;  -- ✅

-- Use the ALTER DEFAULT PRIVILEGES for the future.
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_read;  -- ✅

-- Use the REVOKE for the immediate removal.
REVOKE SELECT ON customers FROM reporting;  -- ✅

-- Audit the grants.
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'alice';  -- ✅

-- Use the least privilege.
GRANT SELECT, INSERT ON orders TO app_user;  -- ✅

❌ Don’t Do This:

-- Don't grant the superuser to the application.
ALTER ROLE app_user SUPERUSER;  -- ❌

-- Don't grant ALL PRIVILEGES without the review.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_user;  -- ❌

-- Don't use the WITH GRANT OPTION casually.
GRANT SELECT ON customers TO alice WITH GRANT OPTION;  -- ⚠️ The transitive

-- Don't forget the schema USAGE.
GRANT SELECT ON app.orders TO alice;  -- ❌ The permission denied

-- Don't leave the role without the expiry for the contractor.
CREATE ROLE contractor LOGIN PASSWORD 'secret';  -- ❌ The indefinite

-- Don't grant the public role the write.
GRANT INSERT ON orders TO PUBLIC;  -- ❌ The world write

-- Don't forget the revoke when the employee leaves.
-- The account remains with the privileges.  -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
The permission denied	No schema `USAGE`	Grant the schema
The privilege not inherited	The `NOINHERIT`	Remove the attribute
The revoke errors	The dependent grants	Use the `CASCADE`
The future table ungranted	No default privileges	Use `ALTER DEFAULT PRIVILEGES`
The account too powerful	The `ALL PRIVILEGES`	Grant the specific
The role not inspected	No audit	Query the catalog
The public write	The grant to `PUBLIC`	Revoke it

Real-World Examples

1. The Basic Grant

GRANT SELECT ON customers TO reporting;

2. The Role

CREATE ROLE app_read NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;
GRANT app_read TO alice;

3. The Schema Usage

GRANT USAGE ON SCHEMA app TO app_read;

4. The Default Privileges

ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_read;

5. The Grant Option

GRANT SELECT ON customers TO alice WITH GRANT OPTION;

6. The Revoke

REVOKE SELECT ON customers FROM reporting;

7. The Cascade

REVOKE SELECT ON customers FROM alice CASCADE;

8. The Role Chain

GRANT app_read TO app_write;
GRANT app_write TO app_admin;

9. The Account

CREATE ROLE alice LOGIN PASSWORD 'secret' VALID UNTIL '2026-12-31';

10. The Complete Pattern

CREATE ROLE app_read NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_read;
GRANT app_read TO alice;

Visual

The Grant Flow

┌────────────────────────────────────────────────────────────┐
│  THE GRANT FLOW                                            │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  GRANT SELECT ON customers TO alice;              │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The privilege is recorded in the catalog.        │     │
│  │  The alice can now SELECT from the customers.     │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  REVOKE SELECT ON customers FROM alice;           │     │
│  │  The privilege is removed.                        │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Role Hierarchy

┌────────────────────────────────────────────────────────────┐
│  THE ROLE HIERARCHY                                        │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  app_admin                                        │     │
│  │    ├── the ALL PRIVILEGES                         │     │
│  │    └── inherits app_write                         │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  app_write                                        │     │
│  │    ├── the INSERT, UPDATE, DELETE                 │     │
│  │    └── inherits app_read                          │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  app_read                                         │     │
│  │    └── the SELECT                                 │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The roles inherit, and the accounts inherit the roles.    │
└────────────────────────────────────────────────────────────┘

The Grant Option

┌────────────────────────────────────────────────────────────┐
│  THE GRANT OPTION                                          │
│                                                            │
│  GRANT SELECT ON customers TO alice WITH GRANT OPTION;     │
│    │                                                       │
│    v                                                       │
│  The alice can grant the SELECT to the other users.        │
│    │                                                       │
│    v                                                       │
│  GRANT SELECT ON customers TO bob;  -- by the alice       │
│    │                                                       │
│    v                                                       │
│  REVOKE SELECT ON customers FROM alice CASCADE;            │
│    │                                                       │
│    v                                                       │
│  The bob's privilege is also removed.                      │
└────────────────────────────────────────────────────────────┘

The Default Privileges

┌────────────────────────────────────────────────────────────┐
│  THE DEFAULT PRIVILEGES                                    │
│                                                            │
│  ALTER DEFAULT PRIVILEGES IN SCHEMA app                    │
│    GRANT SELECT ON TABLES TO app_read;                     │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The existing tables: the privilege is not changed.│    │
│  │  The future tables: the SELECT is granted.         │     │
│  │                                                   │     │
│  │  CREATE TABLE app.new_table (...);                 │     │
│  │    → app_read has the SELECT automatically.        │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
`GRANT`	Add the privilege
`REVOKE`	Remove the privilege
The privilege	The operation on the object
The role	The named privilege bundle
`WITH GRANT OPTION`	The transitive grant
`CASCADE`	The dependent revoke
`ALTER DEFAULT PRIVILEGES`	The future objects
The schema `USAGE`	The prerequisite
`SET ROLE`	The effective switch
`pg_roles`	The role catalog

Key takeaways:

The GRANT adds a privilege, and the REVOKE removes it. The privilege is the operation, the object is the target, and the principal is the user or the role. The three form the permission statement.
A role is the named collection of the privileges. The role is granted to the users and to the other roles. The hierarchy is the chain, and the account inherits the privileges through the chain.
The schema USAGE is the prerequisite. The object grant without the schema USAGE produces the permission denied. The GRANT USAGE ON SCHEMA is the first step.
The WITH GRANT OPTION allows the transitive grant. The recipient can grant the privilege to the others. The revocation cascades.
The ALTER DEFAULT PRIVILEGES covers the future objects. The existing tables are not changed. The future tables receive the privilege automatically.
The least privilege is the rule. The application does not need the DROP TABLE. The account holds only the needed, and the review is the audit.
The immediate revoke is the security response. The employee leaves, and the REVOKE removes the access. The DROP ROLE removes the account entirely.
The audit is the query. The information_schema.role_table_grants and the pg_roles answer the “who can do what” question. The review is the periodic.

Remember: The user access control is the GRANT and the REVOKE, the privilege and the role. The privilege is the operation on the object, the role is the named bundle, and the chain is the inheritance. The schema USAGE, the WITH GRANT OPTION, and the ALTER DEFAULT PRIVILEGES are the details. The result is the database where the account has exactly the access it needs, and the audit shows the model.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!