Table-level permissions in PostgreSQL - Step-by-Step Execution

Concept Flow - Table-level permissions

Start: User wants to access table

↓

Check user role and privileges

↓

Does user have table-level permission?

No→Access Denied

Yes↓

Allow operation (SELECT, INSERT, UPDATE, DELETE)

↓

End

When a user tries to access a table, the database checks if the user has the right permissions on that table. If yes, the operation proceeds; if not, access is denied.

Execution Sample

PostgreSQL

GRANT SELECT ON employees TO alice;
-- Alice tries to SELECT from employees
SELECT * FROM employees WHERE id = 1;

Grant SELECT permission on the employees table to user Alice, then Alice runs a SELECT query on that table.

Execution Table

Step	Action	User	Permission Checked	Result	Output
1	Grant SELECT on employees	admin	N/A	Permission granted to alice	N/A
2	Alice runs SELECT query	alice	SELECT on employees	Permission found	Row with id=1 returned
3	Alice tries INSERT query	alice	INSERT on employees	Permission not found	ERROR: permission denied for table employees
4	Revoke SELECT on employees from alice	admin	N/A	Permission revoked	N/A
5	Alice runs SELECT query again	alice	SELECT on employees	Permission not found	ERROR: permission denied for table employees

💡 Execution stops when permission is denied or query completes successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 4	After Step 5
alice_permissions	{}	{SELECT: employees}	{SELECT: employees}	{}	{}

Key Moments - 2 Insights

Why does Alice get an error when trying to INSERT into employees?

Because in execution_table row 3, Alice does not have INSERT permission on employees, so the database denies the operation.

What happens when SELECT permission is revoked from Alice?

As shown in rows 4 and 5, after revoking SELECT permission, Alice's SELECT queries fail with permission denied errors.

Visual Quiz - 3 Questions

Look at the execution_table, what permission does Alice have after step 1?

A No permissions

B SELECT on employees

C INSERT on employees

D All permissions

💡 Hint

Check the 'alice_permissions' variable after Step 1 in variable_tracker.

At which step does Alice first get a permission denied error?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Result' and 'Output' columns in execution_table for permission denied messages.

If Alice was granted INSERT permission at step 1, what would change in the execution_table?

A Step 3 would succeed instead of error

B Step 2 would fail

C Step 5 would succeed

D No change

💡 Hint

Consider what permission is checked in Step 3 and the current result.

Concept Snapshot

Table-level permissions control what actions a user can perform on a table.
Use GRANT to give permissions like SELECT, INSERT, UPDATE, DELETE.
Database checks permissions on each operation.
If permission missing, access is denied with an error.
REVOKE removes permissions.
Always verify user permissions before querying.

Full Transcript

Table-level permissions in PostgreSQL control access to tables. When a user tries to perform an action like SELECT or INSERT, the database checks if the user has the required permission on that table. If the permission exists, the operation proceeds and returns data or modifies the table. If not, the database returns a permission denied error. Permissions are granted using the GRANT command and removed using REVOKE. For example, granting SELECT on the employees table to user Alice allows her to run SELECT queries successfully. If she tries to INSERT without permission, she gets an error. Revoking SELECT permission later causes her SELECT queries to fail. This step-by-step check ensures data security and controlled access.

Practice

1. What does the GRANT SELECT ON table_name TO user_name; command do in PostgreSQL?

easy

A. Removes all permissions from the user on the specified table.

B. Allows the user to delete data from the specified table.

C. Creates a new table with the given name.

D. Allows the user to read data from the specified table.

Solution

1. Step 1: Understand the GRANT command

   The GRANT command is used to give specific permissions to users on database objects like tables.

2. Step 2: Identify the permission type SELECT

   SELECT permission allows reading data from the table but not modifying it.

3. Final Answer:

   Allows the user to read data from the specified table. -> Option D

4. Quick Check:

   GRANT SELECT = read permission [OK]

Hint: GRANT SELECT means read access only [OK]

Common Mistakes:

• Confusing SELECT with DELETE permission
• Thinking GRANT creates tables
• Mixing GRANT with REVOKE commands

2. Which of the following is the correct syntax to revoke INSERT permission on a table named employees from user john?

easy

A. REVOKE INSERT TO john ON employees;

B. REVOKE ON employees INSERT FROM john;

C. REVOKE INSERT ON employees FROM john;

D. REVOKE INSERT FROM john ON employees;

Solution

1. Step 1: Recall REVOKE syntax

   The correct syntax is REVOKE permission ON table FROM user;

2. Step 2: Match syntax with options

   REVOKE INSERT ON employees FROM john; matches the correct order: REVOKE INSERT ON employees FROM john;

3. Final Answer:

   REVOKE INSERT ON employees FROM john; -> Option C

4. Quick Check:

   REVOKE permission ON table FROM user [OK]

Hint: REVOKE syntax: REVOKE permission ON table FROM user [OK]

Common Mistakes:

• Swapping ON and FROM keywords
• Using TO instead of FROM
• Incorrect order of clauses

3. Given the commands:

GRANT SELECT ON orders TO alice;
GRANT INSERT ON orders TO bob;
REVOKE SELECT ON orders FROM alice;

Which of the following is true about user permissions on the orders table?

medium

A. Alice cannot read data; Bob can insert data.

B. Alice can read and insert data; Bob can only insert data.

C. Alice can read data; Bob cannot insert data.

D. Both Alice and Bob have no permissions on the table.

Solution

1. Step 1: Analyze granted permissions

   Alice was granted SELECT (read) permission, Bob was granted INSERT permission.

2. Step 2: Analyze revoked permissions

   Alice's SELECT permission was revoked, so she no longer can read data.

3. Final Answer:

   Alice cannot read data; Bob can insert data. -> Option A

4. Quick Check:

   Revoked SELECT removes read access [OK]

Hint: Revoking removes permission even if previously granted [OK]

Common Mistakes:

• Assuming revoked permission still applies
• Confusing INSERT with SELECT
• Thinking REVOKE affects other users

4. Consider this command:

GRANT UPDATE ON customers TO ;

What is the error in this command?

medium

A. Missing user name after TO keyword.

B. UPDATE is not a valid permission.

C. Table name is missing after ON keyword.

D. GRANT cannot be used for UPDATE permission.

Solution

1. Step 1: Check syntax completeness

   The command ends with TO but does not specify a user or role name.

2. Step 2: Validate permission and table name

   UPDATE is a valid permission and customers is the table name, so those parts are correct.

3. Final Answer:

   Missing user name after TO keyword. -> Option A

4. Quick Check:

   GRANT requires user after TO [OK]

Hint: Always specify user after TO in GRANT [OK]

Common Mistakes:

• Leaving user name blank after TO
• Confusing permission names
• Omitting table name

5. You want to allow user carol to read and insert data into the products table but prevent her from deleting or updating any data. Which commands should you use?

hard

A. GRANT ALL ON products TO carol;
REVOKE DELETE, UPDATE ON products FROM carol;

B. GRANT SELECT, INSERT ON products TO carol;
REVOKE DELETE, UPDATE ON products FROM carol;

C. GRANT SELECT, INSERT, DELETE ON products TO carol;

D. GRANT SELECT ON products TO carol;
GRANT INSERT ON products TO carol;

Solution

1. Step 1: Grant only SELECT and INSERT permissions

   To allow reading and inserting, grant SELECT and INSERT on products to carol.

2. Step 2: Revoke DELETE and UPDATE permissions

   To prevent deleting or updating, explicitly revoke DELETE and UPDATE permissions if previously granted.

3. Final Answer:

   GRANT SELECT, INSERT ON products TO carol; REVOKE DELETE, UPDATE ON products FROM carol; -> Option B

4. Quick Check:

   Grant needed permissions, revoke unwanted ones [OK]

Hint: Grant needed permissions, revoke unwanted explicitly [OK]

Common Mistakes:

• Granting ALL permissions instead of specific ones
• Not revoking unwanted permissions
• Granting DELETE or UPDATE by mistake