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.

Practice

1. What does column-level permission in PostgreSQL control?

easy

A. Access to server configuration files

B. Access to entire tables only

C. Access to database schemas

D. Access to specific columns in a table

Solution

1. Step 1: Understand the concept of column-level permissions

   Column-level permissions allow control over which columns a user can see or modify in a table.

2. Step 2: Compare with other access types

   Other options refer to broader or unrelated access controls, not specific columns.

3. Final Answer:

   Access to specific columns in a table -> Option D

4. Quick Check:

   Column-level permission = Access to specific columns [OK]

Hint: Column-level means controlling access per column, not whole table [OK]

Common Mistakes:

• Confusing column-level with table-level permissions
• Thinking it controls schema or server access
• Assuming it controls row-level access

2. Which of the following is the correct syntax to grant SELECT permission on column email of table users to user alice?

easy

A. GRANT SELECT(email) ON users TO alice;

B. GRANT SELECT ON users TO alice(email);

C. GRANT SELECT ON users(email) TO alice;

D. GRANT SELECT TO alice ON users(email);

Solution

1. Step 1: Recall PostgreSQL syntax for column-level GRANT

   The correct syntax is GRANT SELECT (email) ON users TO alice; (parentheses after privilege).

2. Step 2: Match options with syntax

   GRANT SELECT(email) ON users TO alice; matches the correct syntax exactly, others misplace keywords or parentheses.

3. Final Answer:

   GRANT SELECT(email) ON users TO alice; -> Option A

4. Quick Check:

   GRANT SELECT(column) ON table TO user [OK]

Hint: GRANT SELECT(column) ON table TO user is correct syntax [OK]

Common Mistakes:

• Placing column name after TO user
• Putting column inside ON table()
• Misordering keywords in the statement

3. Given the table employees(id, name, salary), if user bob has SELECT permission only on id and name, what will be the result of SELECT * FROM employees; executed by bob?

medium

A. Only id and name columns with data, salary as NULL

B. All columns with actual data

C. Error: permission denied

D. Only salary column with data

Solution

1. Step 1: Understand column-level permission effect on SELECT *

   User can see columns they have permission for; restricted columns appear as NULL.

2. Step 2: Apply to given columns

   Bob has permission on id and name, so salary shows as NULL.

3. Final Answer:

   Only id and name columns with data, salary as NULL -> Option A

4. Quick Check:

   Restricted columns show NULL, not error [OK]

Hint: Restricted columns appear as NULL, not error [OK]

Common Mistakes:

• Expecting a permission denied error
• Assuming all columns show data
• Thinking restricted columns are hidden completely

4. You run the command GRANT SELECT ON employees(name, salary) TO carol; but get a syntax error. What is the likely cause?

medium

A. Column names must be in double quotes

B. Cannot grant permissions on multiple columns at once

C. Column list in parentheses must precede the table name

D. User carol does not exist

Solution

1. Step 1: Check syntax for GRANT with multiple columns

   Correct syntax is GRANT SELECT (name, salary) ON employees TO carol;

2. Step 2: Identify error in command

   Command incorrectly places column list after table name; column list must follow SELECT before ON.

3. Final Answer:

   Column list in parentheses must precede the table name -> Option C

4. Quick Check:

   GRANT SELECT (col1, col2) ON table TO user [OK]

Hint: Columns inside parentheses after SELECT before ON table [OK]

Common Mistakes:

• Placing column list after table name
• Forgetting parentheses around columns
• Assuming multiple columns need separate GRANTs

5. You want to allow user dave to update only the phone column in the contacts table but not others. Which sequence of commands correctly achieves this?

hard

A. GRANT UPDATE(phone) ON contacts TO dave; REVOKE UPDATE ON contacts FROM dave;

B. REVOKE UPDATE ON contacts FROM dave; GRANT UPDATE(phone) ON contacts TO dave;

C. GRANT UPDATE ON contacts TO dave; REVOKE UPDATE(phone) ON contacts FROM dave;

D. GRANT UPDATE(phone) ON contacts TO dave;

Solution

1. Step 1: Remove any existing full UPDATE permission

   First revoke any broad UPDATE permission to avoid conflicts.

2. Step 2: Grant UPDATE permission only on the phone column

   Then grant UPDATE on the specific column to limit access.

3. Final Answer:

   REVOKE UPDATE ON contacts FROM dave; GRANT UPDATE(phone) ON contacts TO dave; -> Option B

4. Quick Check:

   Revoke broad then grant column-level UPDATE [OK]

Hint: Revoke broad permission before granting column-level update [OK]

Common Mistakes:

• Granting column-level without revoking broad permission
• Revoking column-level instead of broad permission
• Assuming single GRANT is enough if broad permission exists