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.