Column-level permissions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What columns can user 'report_viewer' select from 'sales'?

Given the following permissions:

GRANT SELECT (date, amount) ON sales TO report_viewer;

Which columns will be visible to report_viewer when running SELECT * FROM sales;?

PostgreSQL

SELECT * FROM sales;

A All columns from 'sales' are returned regardless of column-level permissions.

B Only columns 'date' and 'amount' are returned; other columns are hidden.

C No columns are returned; query fails with permission error.

D Only columns 'date' and 'amount' are returned, but other columns show NULL values.

💡 Hint

Think about how PostgreSQL enforces column-level SELECT permissions.

PostgreSQL restricts SELECT to only the columns granted. The user sees only those columns; other columns show NULL values when using SELECT *.

🧠 Conceptual

intermediate

What happens if a user lacks SELECT permission on a column?

If a user has SELECT permission on a table but is denied SELECT on one column, what happens when they query that column?

A The query returns zero rows.

B The query returns the actual data for that column anyway.

C The query fails with a permission denied error.

D The query returns NULL for that column without error.

💡 Hint

Consider how PostgreSQL enforces column-level security strictly.

If a user tries to select a column without permission, PostgreSQL raises a permission denied error.

📝 Syntax

advanced

Which statement correctly grants SELECT on specific columns?

Choose the correct SQL statement to grant SELECT permission on columns 'name' and 'email' of table 'customers' to user 'client_user'.

A GRANT SELECT ON customers(name, email) TO client_user;

B GRANT SELECT (name, email) ON customers TO client_user;

C GRANT SELECT ON customers TO client_user (name, email);

D GRANT SELECT TO client_user ON customers(name, email);

💡 Hint

Remember the syntax order for column-level grants in PostgreSQL.

The correct syntax is GRANT SELECT (columns) ON table TO user; Option B matches this.

❓ optimization

advanced

How to optimize queries with column-level permissions for performance?

You have a table with many columns but users have SELECT permission on only a few columns. What is the best way to optimize query performance for these users?

A Create a view with only the allowed columns and grant SELECT on the view instead of the base table.

B Grant SELECT on all columns but filter results in application code.

C Use SELECT * queries and rely on PostgreSQL to filter columns internally.

D Create a trigger to hide unauthorized columns at query time.

💡 Hint

Think about how views can simplify permissions and improve query speed.

Creating a view with only allowed columns reduces data scanned and simplifies permission management, improving performance.

🔧 Debug

expert

Why does this query fail despite column-level SELECT grant?

User 'analyst' has been granted SELECT on columns 'id' and 'score' of table 'results'. The following query fails with permission denied error:

SELECT id, score, comments FROM results;

Why does this happen?

A User lacks SELECT permission on column 'comments', causing the entire query to fail.

B User must have SELECT on all columns in the table to run any SELECT query.

C The query syntax is invalid because 'comments' is not included in the grant.

D PostgreSQL does not support column-level permissions on SELECT statements.

💡 Hint

Check which columns the user is allowed to select and which are requested.

If a query requests a column without permission, PostgreSQL denies the entire query, even if other columns are allowed.

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