GRANT and REVOKE permissions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the GRANT command do in PostgreSQL?

The GRANT command gives specific permissions on database objects to users or roles, allowing them to perform certain actions like SELECT, INSERT, UPDATE, or DELETE.

beginner

What is the purpose of the REVOKE command?

The REVOKE command removes previously granted permissions from users or roles, preventing them from performing certain actions on database objects.

beginner

How do you grant SELECT permission on a table named 'employees' to a user named 'alice'?

Use the command: <br> GRANT SELECT ON employees TO alice;

beginner

How do you revoke INSERT permission on a table named 'sales' from a role named 'sales_team'?

Use the command: <br> REVOKE INSERT ON sales FROM sales_team;

intermediate

Can you grant multiple permissions at once using GRANT? How?

Yes, you can grant multiple permissions by listing them separated by commas. For example: <br> GRANT SELECT, INSERT ON orders TO bob;

Which command gives a user permission to read data from a table?

A GRANT SELECT ON table TO user;

B REVOKE SELECT ON table FROM user;

C GRANT DELETE ON table TO user;

D REVOKE INSERT ON table FROM user;

GRANT SELECT allows the user to read data from the table.

What does the REVOKE command do?

A Gives permissions to users

B Creates a new user

C Removes permissions from users

D Deletes a table

REVOKE removes permissions that were previously granted.

How would you remove UPDATE permission from user 'john' on table 'products'?

A GRANT UPDATE ON products TO john;

B REVOKE UPDATE ON products FROM john;

C DELETE UPDATE ON products FROM john;

D REMOVE UPDATE ON products FROM john;

REVOKE UPDATE ON products FROM john; removes the UPDATE permission.

Which of the following is a valid way to grant multiple permissions?

A GRANT SELECT, INSERT ON table TO user;

B GRANT SELECT INSERT ON table TO user;

C GRANT (SELECT, INSERT) ON table TO user;

D GRANT SELECT; INSERT ON table TO user;

Permissions are listed separated by commas without parentheses.

If you want to allow a user to add new rows to a table, which permission should you grant?

A SELECT

B DELETE

C UPDATE

D INSERT

INSERT permission allows adding new rows to a table.

Explain how to use GRANT and REVOKE commands to control user permissions on a PostgreSQL table.

Think about how you let or stop someone from doing things with your data.

Describe a real-life scenario where you would use GRANT and REVOKE in a database.

Imagine sharing a document with friends and then changing who can edit it.