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.