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.

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

Previous PostgreSQL Exercise: Grant SELECT Privileges to a User.