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.