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

Concept Flow - Column-level permissions

Start

↓

Define Table with Columns

↓

Grant SELECT on specific columns to user

↓

User queries table

↓

Database checks column permissions

↓

Return only allowed columns

↓

End

This flow shows how column-level permissions control which columns a user can see when querying a table.

Execution Sample

PostgreSQL

CREATE TABLE employees(id INT, name TEXT, salary INT);
GRANT SELECT (id, name) ON employees TO user1;
-- user1 runs: SELECT * FROM employees;

Create a table, grant select permission on specific columns, then user queries the table.

Execution Table

Step	Action	Permission Check	Result	Output Columns
1	Create table employees with columns id, name, salary	N/A	Table created	id, name, salary
2	Grant SELECT on columns id, name to user1	N/A	Permissions granted	id, name
3	user1 runs SELECT * FROM employees	Check user1 permissions on employees	Allowed columns: id, name	id, name
4	Return query result to user1	N/A	Only allowed columns returned	id, name
5	End	N/A	Query complete	id, name

💡 Query returns only columns user1 has SELECT permission on; salary column excluded.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
Table columns	id, name, salary	id, name, salary	id, name, salary	id, name, salary
User1 permissions	None	SELECT on id, name	SELECT on id, name	SELECT on id, name
Query output columns	None	None	id, name	id, name

Key Moments - 2 Insights

Why does user1 not see the salary column in the query result?

Because in step 2, SELECT permission was granted only on columns id and name. The database enforces this in step 3 by filtering out salary.

What happens if user1 tries to SELECT salary explicitly?

The query will fail with a permission error because user1 lacks SELECT permission on salary, as shown in the permission check in step 3.

Visual Quiz - 3 Questions

Look at the execution table, which columns does user1 have SELECT permission on after step 2?

A id, name, salary

B salary only

C id, name

D No columns

💡 Hint

Check the 'User1 permissions' variable in variable_tracker after step 2.

At which step does the database filter out columns user1 cannot access?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Look at the 'Permission Check' and 'Result' columns in execution_table at step 3.

If user1 is granted SELECT on salary as well, how would the output columns change at step 4?

A id, name, salary

B salary only

C id, name

D No columns

💡 Hint

Consider the 'User1 permissions' variable and how it affects 'Query output columns' in variable_tracker.

Concept Snapshot

Column-level permissions in SQL allow granting SELECT rights on specific columns.
Use GRANT SELECT(column1, column2) ON table TO user;
When querying, users see only allowed columns.
Unauthorized columns are hidden or cause errors.
This controls sensitive data access at column granularity.

Full Transcript

This visual execution trace shows how column-level permissions work in PostgreSQL. First, a table named employees is created with three columns: id, name, and salary. Then, SELECT permission is granted only on the id and name columns to user1. When user1 runs a SELECT * query on employees, the database checks permissions and returns only the columns user1 is allowed to see, which are id and name. The salary column is excluded because user1 lacks permission. This example highlights how column-level permissions restrict data visibility to protect sensitive information.