GRANT and REVOKE permissions in PostgreSQL - Deep Dive

Overview - GRANT and REVOKE permissions

What is it?

GRANT and REVOKE are commands used in databases to control who can do what. GRANT gives specific permissions to users or roles, like reading or changing data. REVOKE takes those permissions away. This helps keep data safe and organized by deciding who can access or change it.

Why it matters

Without controlling permissions, anyone could see or change important data, causing mistakes or security problems. GRANT and REVOKE let database owners protect data and share it safely. This control is essential for teamwork, privacy, and preventing accidents or attacks.

Where it fits

Before learning GRANT and REVOKE, you should understand basic database concepts like tables, users, and roles. After this, you can learn about advanced security features like roles inheritance, row-level security, and auditing access.

Mental Model

Core Idea

GRANT and REVOKE are like giving and taking keys to rooms in a building, controlling who can enter and what they can do inside.

Think of it like...

Imagine a library where the librarian gives out keys to different rooms: some keys allow you to read books, others to add or remove books. GRANT is handing out these keys, and REVOKE is taking them back.

┌───────────────┐       ┌───────────────┐
│   Database    │       │     User      │
│   Objects     │◄──────│  Permissions  │
│ (tables, etc) │       │ (keys to rooms)│
└───────────────┘       └───────────────┘
       ▲                        ▲
       │                        │
    GRANT                   REVOKE
       │                        │
       ▼                        ▼
  Permissions given       Permissions removed

Build-Up - 7 Steps

1

Foundation

Understanding Database Permissions

Concept: Permissions control what users can do with database objects.

In a database, permissions are rules that say who can read, write, or change data. Common permissions include SELECT (read), INSERT (add), UPDATE (change), and DELETE (remove). These permissions help keep data safe and organized.

Result

You know that permissions are like rules for users to access or change data.

Understanding permissions is the base for controlling access and protecting data in any database.

2

Foundation

Roles and Users in PostgreSQL

Concept: Users and roles represent people or groups who get permissions.

PostgreSQL uses roles to represent users or groups. A role can own objects and have permissions. You can create roles and assign them to users to manage permissions easily.

Result

You understand that roles are containers for permissions and users can be assigned roles.

Knowing roles helps manage permissions efficiently, especially for many users.

3

Intermediate

Using GRANT to Give Permissions

🤔Before reading on: do you think GRANT can give multiple permissions at once or only one at a time? Commit to your answer.

Concept: GRANT assigns one or more permissions to roles or users on database objects.

The GRANT command lets you give permissions. For example, to let a user read a table, you write: GRANT SELECT ON table_name TO user_name; You can grant multiple permissions by listing them separated by commas.

Result

Users or roles receive the permissions you specify and can perform those actions.

Understanding that GRANT can assign multiple permissions at once makes permission management faster and clearer.

4

Intermediate

Using REVOKE to Remove Permissions

🤔Before reading on: does REVOKE remove all permissions from a user or only the ones specified? Commit to your answer.

Concept: REVOKE removes specific permissions previously granted to roles or users.

The REVOKE command takes away permissions. For example, to stop a user from reading a table: REVOKE SELECT ON table_name FROM user_name; It only removes the permissions you list, not all permissions the user might have.

Result

Users lose the specified permissions and can no longer perform those actions.

Knowing that REVOKE targets specific permissions prevents accidental removal of unrelated access.

5

Intermediate

Granting Permissions with Grant Option

🤔Before reading on: do you think a user with GRANT OPTION can give permissions to others? Commit to your answer.

Concept: GRANT OPTION lets a user pass on permissions to others.

When you grant permissions, you can add WITH GRANT OPTION. This means the user can also grant those permissions to others. For example: GRANT SELECT ON table_name TO user_name WITH GRANT OPTION; This helps delegate permission management.

Result

Users with GRANT OPTION can share their permissions, creating a chain of trust.

Understanding GRANT OPTION reveals how permission control can be delegated safely.

6

Advanced

Revoking Permissions and Cascading Effects

🤔Before reading on: if you revoke a permission from a user who granted it to others, do those others keep their permissions? Commit to your answer.

Concept: Revoking permissions can cascade and remove permissions granted by the revoked user.

If a user granted permissions to others using GRANT OPTION, revoking from that user can also revoke from those others. This cascading ensures no orphaned permissions remain. PostgreSQL handles this automatically to keep permissions consistent.

Result

Permissions are cleaned up properly, preventing unauthorized access after revocation.

Knowing about cascading revokes helps avoid security holes from leftover permissions.

7

Expert

Managing Permissions in Complex Role Hierarchies

🤔Before reading on: do you think permissions granted to a role automatically apply to users assigned to that role? Commit to your answer.

Concept: Permissions granted to roles propagate to users assigned those roles, enabling scalable permission management.

In PostgreSQL, roles can be members of other roles. Permissions granted to a parent role apply to all member roles and users. This hierarchy allows managing permissions for many users by assigning roles instead of individual permissions. However, understanding inheritance and conflicts is key to avoid unexpected access.

Result

You can manage large user groups efficiently with role hierarchies and inherited permissions.

Understanding role inheritance is crucial for designing secure and maintainable permission systems in real-world databases.

Under the Hood

PostgreSQL stores permissions as access control lists (ACLs) attached to each database object. When a user tries to perform an action, the system checks the ACLs for that object and the user's roles. GRANT adds entries to these ACLs, and REVOKE removes them. The system also tracks who granted permissions to manage cascading revokes.

Why designed this way?

This design allows fine-grained control over access, supporting complex scenarios like role inheritance and delegation. It balances security with flexibility, enabling both strict control and easy sharing of permissions. Alternatives like fixed permission sets would be too rigid for diverse needs.

┌───────────────┐
│ Database User │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Role Membership  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Access Control│
│  Lists (ACLs) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database Object│
│ (table, etc)  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does REVOKE remove all permissions a user has on an object or only the ones specified? Commit to your answer.

Common Belief: REVOKE removes all permissions a user has on an object, no matter what.

Reality: REVOKE only removes the specific permissions listed; other permissions remain intact.

Why it matters: Assuming REVOKE removes all can lead to unexpected access if some permissions are not revoked, causing security risks.

Quick: Can a user without GRANT OPTION give permissions to others? Commit to your answer.

Common Belief: Any user with a permission can grant it to others.

Reality: Only users granted permissions WITH GRANT OPTION can pass them on to others.

Why it matters: Misunderstanding this can cause permission leaks or confusion about who controls access.

Quick: If a role has a permission, do users assigned to that role always have it? Commit to your answer.

Common Belief: Users automatically have all permissions of roles they belong to, no exceptions.

Reality: Users inherit permissions from roles, but explicit revokes or role membership changes can affect this.

Why it matters: Ignoring this can cause unexpected access or denial, complicating security management.

Quick: Does revoking a permission from a user who granted it to others affect those others? Commit to your answer.

Common Belief: Revoking a permission from one user does not affect permissions granted by that user to others.

Reality: Revoking can cascade and remove permissions granted by that user to others to maintain security.

Why it matters: Not knowing this can leave unauthorized users with access after revocation.

Expert Zone

1

Permissions granted with GRANT OPTION create a chain of trust that can be complex to track and audit.

2

Role membership and permission inheritance can interact in subtle ways, especially with explicit revokes overriding inherited permissions.

3

Cascading revokes ensure security but can unintentionally remove permissions if role hierarchies are not carefully designed.

When NOT to use

GRANT and REVOKE are not suitable for row-level or column-level security; use PostgreSQL's Row-Level Security (RLS) policies instead. For very dynamic or attribute-based access control, consider external systems or extensions.

Production Patterns

In production, teams use roles to group permissions by job function, granting users roles instead of individual permissions. They use WITH GRANT OPTION sparingly to limit delegation. Auditing tools monitor permission changes, and cascading revokes are tested carefully to avoid accidental access loss.

Connections

Access Control Lists (ACLs)

GRANT and REVOKE manipulate ACLs to control access.

Understanding ACLs helps grasp how permissions are stored and checked internally.

Role-Based Access Control (RBAC)

GRANT and REVOKE implement RBAC by assigning permissions to roles.

Knowing RBAC principles clarifies why roles simplify permission management.

Physical Security Systems

Similar to how keys control physical access, GRANT and REVOKE control digital access.

Recognizing this parallel helps appreciate the importance of careful permission management to prevent unauthorized entry.

Common Pitfalls

#1 Granting permissions without specifying WITH GRANT OPTION when delegation is needed.

Wrong approach:

GRANT SELECT ON employees TO analyst;

Correct approach:

GRANT SELECT ON employees TO analyst WITH GRANT OPTION;

Root cause:

Not understanding that without WITH GRANT OPTION, users cannot pass permissions to others.

#2 Revoking permissions without specifying which ones, expecting all to be removed.

Wrong approach:

REVOKE ON employees FROM analyst;

Correct approach:

REVOKE SELECT ON employees FROM analyst;

Root cause:

Misunderstanding REVOKE syntax; it requires explicit permission names.

#3 Assuming users automatically lose permissions when removed from a role without checking inherited permissions.

Wrong approach:

DROP ROLE analyst; -- expecting all permissions gone

Correct approach:

REVOKE role membership and verify permissions explicitly.

Root cause:

Not realizing permissions can come from multiple roles or direct grants.

Key Takeaways

GRANT and REVOKE control who can do what in a database by giving or taking permissions.

Permissions can be granted to users or roles, and roles help manage groups of users efficiently.

WITH GRANT OPTION allows users to delegate permissions, creating chains of trust that must be managed carefully.

Revoking permissions can cascade to remove permissions granted by others, ensuring security but requiring careful planning.

Understanding role inheritance and permission propagation is essential for secure and maintainable database access control.

Practice

1. What does the GRANT command do in PostgreSQL?

easy

A. It gives specific permissions to a user or role.

B. It deletes a user from the database.

C. It creates a new database.

D. It backs up the database.

Solution

1. Step 1: Understand the purpose of GRANT

   The GRANT command is used to give permissions like SELECT, INSERT, or UPDATE to users or roles.

2. Step 2: Compare with other options

   Deleting users, creating databases, or backing up are done by other commands, not GRANT.

3. Final Answer:

   It gives specific permissions to a user or role. -> Option A

4. Quick Check:

   GRANT = give permissions [OK]

Hint: GRANT means give rights; REVOKE means take away [OK]

Common Mistakes:

• Confusing GRANT with user creation
• Thinking GRANT deletes data
• Mixing GRANT with backup commands

2. Which of the following is the correct syntax to grant SELECT permission on table employees to user john?

easy

A. GRANT ON employees SELECT TO john;

B. GRANT john ON employees SELECT;

C. GRANT SELECT TO john ON employees;

D. GRANT SELECT ON employees TO john;

Solution

1. Step 1: Recall correct GRANT syntax

   The correct syntax is: GRANT permission ON object TO user;

2. Step 2: Match syntax with options

   GRANT SELECT ON employees TO john; matches the correct order: GRANT SELECT ON employees TO john;

3. Final Answer:

   GRANT SELECT ON employees TO john; -> Option D

4. Quick Check:

   GRANT + permission + ON + object + TO + user [OK]

Hint: GRANT permission ON object TO user; is the pattern [OK]

Common Mistakes:

• Mixing order of keywords
• Placing user before permission
• Omitting ON keyword

3. Given the commands:
GRANT SELECT ON customers TO alice;
REVOKE SELECT ON customers FROM alice;
What permissions does user alice have on table customers after these commands?

medium

A. Alice can SELECT from customers.

B. Alice can INSERT into customers.

C. Alice cannot SELECT from customers.

D. Alice has all permissions on customers.

Solution

1. Step 1: Analyze the GRANT command

   Alice is given SELECT permission on customers table.

2. Step 2: Analyze the REVOKE command

   The SELECT permission is then revoked from Alice, removing her ability to SELECT.

3. Final Answer:

   Alice cannot SELECT from customers. -> Option C

4. Quick Check:

   REVOKE removes permission given by GRANT [OK]

Hint: REVOKE removes permissions previously granted [OK]

Common Mistakes:

• Assuming REVOKE adds permissions
• Confusing SELECT with INSERT
• Ignoring the order of commands

4. Identify the error in the following command:
REVOKE ALL PRIVILEGES employees FROM bob;

medium

A. The order of keywords is incorrect.

B. The user name should be before the table name.

C. REVOKE cannot be used with ALL PRIVILEGES.

D. The command is correct.

Solution

1. Step 1: Check correct REVOKE syntax

   The correct syntax is: REVOKE privileges ON object FROM user;

2. Step 2: Compare with given command

   The command uses: REVOKE ALL PRIVILEGES employees FROM bob; missing ON keyword and wrong order.

3. Final Answer:

   The order of keywords is incorrect. -> Option A

4. Quick Check:

   REVOKE + privileges + ON + object + FROM + user [OK]

Hint: REVOKE syntax requires ON before object [OK]

Common Mistakes:

• Omitting ON keyword
• Swapping user and object positions
• Using ALL PRIVILEGES incorrectly

5. You want to allow user carol to insert and update data on table orders, but not delete. Which commands correctly grant these permissions?

hard

A. GRANT DELETE ON orders TO carol;

B. GRANT INSERT, UPDATE ON orders TO carol;

C. GRANT ALL PRIVILEGES ON orders TO carol;

D. GRANT SELECT, DELETE ON orders TO carol;

Solution

1. Step 1: Identify required permissions

   Carol needs INSERT and UPDATE permissions only, no DELETE.

2. Step 2: Match commands with required permissions

   GRANT INSERT, UPDATE ON orders TO carol; grants INSERT and UPDATE correctly. Options B and D grant DELETE, which is not wanted. GRANT ALL PRIVILEGES ON orders TO carol; grants all permissions, including DELETE.

3. Final Answer:

   GRANT INSERT, UPDATE ON orders TO carol; -> Option B

4. Quick Check:

   Grant only needed permissions, avoid ALL PRIVILEGES if not required [OK]

Hint: Grant only needed permissions, avoid ALL PRIVILEGES if unsure [OK]

Common Mistakes:

• Granting DELETE when not needed
• Using ALL PRIVILEGES carelessly
• Confusing SELECT with UPDATE