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

Challenge - 5 Problems

❓ query_result

intermediate

Check SELECT permission effect on query output

Given a table employees with columns id, name, and salary, user alice has only SELECT permission on this table. What will be the result of the query SELECT * FROM employees; when run by alice?

PostgreSQL

SELECT * FROM employees;

A Returns all rows and columns from the employees table.

B Returns no rows but shows the column names.

C Raises a permission denied error.

D Returns only the id and name columns, excluding salary.

💡 Hint

SELECT permission allows reading all rows and columns unless column-level restrictions exist.

With SELECT permission on the table, the user can read all rows and columns. No error occurs, and no columns are hidden by default.

❓ query_result

intermediate

Effect of missing INSERT permission

User bob tries to insert a new row into the departments table but does not have INSERT permission on it. What happens when bob runs INSERT INTO departments (id, name) VALUES (5, 'Marketing');?

PostgreSQL

INSERT INTO departments (id, name) VALUES (5, 'Marketing');

A The row is inserted successfully.

B The query inserts the row but with default values.

C The query raises a permission denied error.

D The query runs but does not insert any row.

💡 Hint

INSERT permission is required to add rows to a table.

Without INSERT permission, the database denies the operation and raises a permission error.

📝 Syntax

advanced

Granting UPDATE permission on a table

Which of the following SQL commands correctly grants UPDATE permission on the projects table to user carol?

A GRANT UPDATE ON projects TO carol;

B GRANT UPDATE projects TO carol;

C GRANT UPDATE ON TABLE projects TO carol;

D GRANT UPDATE ON DATABASE projects TO carol;

💡 Hint

The correct syntax includes ON TABLE for tables, but ON TABLE is optional in PostgreSQL.

The correct syntax to grant UPDATE permission on a table is GRANT UPDATE ON TABLE table_name TO user;. Option A omits the keyword TABLE, which is optional in PostgreSQL.

🔧 Debug

advanced

Diagnose permission error on DELETE

User dave tries to delete rows from the tasks table but gets a permission denied error. The DBA confirms that dave has DELETE permission on the table. What could be the reason for the error?

A Dave has DELETE permission but the table is locked by another transaction.

B Dave has DELETE permission but lacks TRIGGER permission required by a delete trigger.

C Dave has DELETE permission but the table is a view, not a table.

D Dave lacks SELECT permission on the table, which is required for DELETE.

💡 Hint

Some DELETE operations require additional permissions if triggers exist.

If the table has DELETE triggers, the user needs permission to execute those triggers. Lacking TRIGGER permission can cause a permission denied error despite having DELETE permission.

🧠 Conceptual

expert

Understanding role inheritance and table permissions

Role analyst has SELECT permission on the sales table. Role intern is a member of analyst but has no direct permissions on sales. What permissions does intern have on the sales table?

A Intern has no permissions on sales because it has no direct grants.

B Intern has all permissions analyst has plus additional default permissions.

C Intern has only USAGE permission on sales by default.

D Intern inherits SELECT permission on sales through analyst role membership.

💡 Hint

Role membership allows permission inheritance unless explicitly revoked.

In PostgreSQL, roles inherit permissions from roles they are members of. Since intern is a member of analyst, it inherits SELECT permission on sales.

Practice

1. What does the GRANT SELECT ON table_name TO user_name; command do in PostgreSQL?

easy

A. Removes all permissions from the user on the specified table.

B. Allows the user to delete data from the specified table.

C. Creates a new table with the given name.

D. Allows the user to read data from the specified table.

Solution

1. Step 1: Understand the GRANT command

   The GRANT command is used to give specific permissions to users on database objects like tables.

2. Step 2: Identify the permission type SELECT

   SELECT permission allows reading data from the table but not modifying it.

3. Final Answer:

   Allows the user to read data from the specified table. -> Option D

4. Quick Check:

   GRANT SELECT = read permission [OK]

Hint: GRANT SELECT means read access only [OK]

Common Mistakes:

• Confusing SELECT with DELETE permission
• Thinking GRANT creates tables
• Mixing GRANT with REVOKE commands

2. Which of the following is the correct syntax to revoke INSERT permission on a table named employees from user john?

easy

A. REVOKE INSERT TO john ON employees;

B. REVOKE ON employees INSERT FROM john;

C. REVOKE INSERT ON employees FROM john;

D. REVOKE INSERT FROM john ON employees;

Solution

1. Step 1: Recall REVOKE syntax

   The correct syntax is REVOKE permission ON table FROM user;

2. Step 2: Match syntax with options

   REVOKE INSERT ON employees FROM john; matches the correct order: REVOKE INSERT ON employees FROM john;

3. Final Answer:

   REVOKE INSERT ON employees FROM john; -> Option C

4. Quick Check:

   REVOKE permission ON table FROM user [OK]

Hint: REVOKE syntax: REVOKE permission ON table FROM user [OK]

Common Mistakes:

• Swapping ON and FROM keywords
• Using TO instead of FROM
• Incorrect order of clauses

3. Given the commands:

GRANT SELECT ON orders TO alice;
GRANT INSERT ON orders TO bob;
REVOKE SELECT ON orders FROM alice;

Which of the following is true about user permissions on the orders table?

medium

A. Alice cannot read data; Bob can insert data.

B. Alice can read and insert data; Bob can only insert data.

C. Alice can read data; Bob cannot insert data.

D. Both Alice and Bob have no permissions on the table.

Solution

1. Step 1: Analyze granted permissions

   Alice was granted SELECT (read) permission, Bob was granted INSERT permission.

2. Step 2: Analyze revoked permissions

   Alice's SELECT permission was revoked, so she no longer can read data.

3. Final Answer:

   Alice cannot read data; Bob can insert data. -> Option A

4. Quick Check:

   Revoked SELECT removes read access [OK]

Hint: Revoking removes permission even if previously granted [OK]

Common Mistakes:

• Assuming revoked permission still applies
• Confusing INSERT with SELECT
• Thinking REVOKE affects other users

4. Consider this command:

GRANT UPDATE ON customers TO ;

What is the error in this command?

medium

A. Missing user name after TO keyword.

B. UPDATE is not a valid permission.

C. Table name is missing after ON keyword.

D. GRANT cannot be used for UPDATE permission.

Solution

1. Step 1: Check syntax completeness

   The command ends with TO but does not specify a user or role name.

2. Step 2: Validate permission and table name

   UPDATE is a valid permission and customers is the table name, so those parts are correct.

3. Final Answer:

   Missing user name after TO keyword. -> Option A

4. Quick Check:

   GRANT requires user after TO [OK]

Hint: Always specify user after TO in GRANT [OK]

Common Mistakes:

• Leaving user name blank after TO
• Confusing permission names
• Omitting table name

5. You want to allow user carol to read and insert data into the products table but prevent her from deleting or updating any data. Which commands should you use?

hard

A. GRANT ALL ON products TO carol;
REVOKE DELETE, UPDATE ON products FROM carol;

B. GRANT SELECT, INSERT ON products TO carol;
REVOKE DELETE, UPDATE ON products FROM carol;

C. GRANT SELECT, INSERT, DELETE ON products TO carol;

D. GRANT SELECT ON products TO carol;
GRANT INSERT ON products TO carol;

Solution

1. Step 1: Grant only SELECT and INSERT permissions

   To allow reading and inserting, grant SELECT and INSERT on products to carol.

2. Step 2: Revoke DELETE and UPDATE permissions

   To prevent deleting or updating, explicitly revoke DELETE and UPDATE permissions if previously granted.

3. Final Answer:

   GRANT SELECT, INSERT ON products TO carol; REVOKE DELETE, UPDATE ON products FROM carol; -> Option B

4. Quick Check:

   Grant needed permissions, revoke unwanted ones [OK]

Hint: Grant needed permissions, revoke unwanted explicitly [OK]

Common Mistakes:

• Granting ALL permissions instead of specific ones
• Not revoking unwanted permissions
• Granting DELETE or UPDATE by mistake