How to Revoke Privileges from a PostgreSQL User

Revoke a User’s Privileges

Write a PostgreSQL query to revoke INSERT privileges on the orders table from the user sales_rep.

Solution:

-- Revoke INSERT privilege from a user
REVOKE INSERT ON orders FROM sales_rep;

Explanation:

• Purpose of the Query:
• Removes the ability of sales_rep to insert data into the orders table.
• Key Components:
• REVOKE INSERT ON orders: Removes the privilege from the specified table.
• FROM sales_rep: Specifies the affected user.
• Real-World Application:
• Useful when restricting database modifications for security purposes.

Notes:

• If a privilege is revoked, the user will receive a permission error on insert attempts.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to revoke SELECT privileges on the payroll table from the user temp_employee.
• Write a PostgreSQL query to revoke all privileges on the suppliers table from the role data_entry_team.
• Write a PostgreSQL query to revoke UPDATE privileges on the invoices table from the user accountant.
• Write a PostgreSQL query to revoke INSERT and UPDATE privileges on the inventory table from all users.

Go to:

• PostgreSQL User and Role Management Queries Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Grant SELECT Privileges to a User.
NEXT : Change a User’s Password.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.