Column-level permissions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What are column-level permissions in a database?

Column-level permissions control access to specific columns in a table, allowing users to read or modify only certain columns instead of the entire table.

beginner

How do you grant SELECT permission on specific columns in PostgreSQL?

Use the GRANT command with the column names listed after the table name, for example: GRANT SELECT (column1, column2) ON table_name TO user_name;

intermediate

Why might you use column-level permissions instead of table-level permissions?

To protect sensitive data by restricting access to certain columns while still allowing access to other parts of the table.

intermediate

Can you update specific columns using column-level permissions in PostgreSQL?

Yes, you can grant UPDATE permission on specific columns by specifying them in the GRANT statement, like: GRANT UPDATE (column1) ON table_name TO user_name;

intermediate

What happens if a user has SELECT permission on a table but not on a specific column?

The user can query the table but will not see data from the restricted column; attempts to access it will result in an error.

Which SQL command is used to give column-level SELECT permission in PostgreSQL?

A CREATE PERMISSION ON column_name FOR user_name;

B GRANT SELECT (column_name) ON table_name TO user_name;

C ALTER TABLE table_name ADD PERMISSION column_name;

D SET PERMISSION SELECT ON column_name TO user_name;

The GRANT command with column names specifies column-level permissions in PostgreSQL.

If a user has UPDATE permission on only one column, what can they do?

A Delete rows from the table.

B Update any column in the table.

C Update only that specific column in the table.

D Insert new rows into the table.

Column-level UPDATE permission restricts updates to only the specified columns.

What is a main benefit of using column-level permissions?

A Allows fine control over sensitive data access.

B Improves query speed.

C Automatically backs up data.

D Simplifies table structure.

Column-level permissions help protect sensitive data by limiting access to specific columns.

Which of the following is NOT true about column-level permissions?

A They can restrict INSERT access on columns.

B They can restrict UPDATE access on columns.

C They can restrict SELECT access on columns.

D They can restrict DELETE access on columns.

DELETE permission applies to rows, not individual columns.

How do you revoke column-level SELECT permission in PostgreSQL?

A REVOKE SELECT (column_name) ON table_name FROM user_name;

B DROP PERMISSION ON column_name FROM user_name;

C REMOVE SELECT ON column_name FROM user_name;

D ALTER USER user_name REVOKE SELECT ON column_name;

REVOKE with column names removes column-level permissions.

Explain what column-level permissions are and why they are useful in databases.

Think about how you might hide some information but show other parts of a table.

Describe how to grant and revoke SELECT permission on specific columns in PostgreSQL.

Focus on the GRANT and REVOKE commands with column lists.

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