Privileges in PostgreSQL

What are privileges in Postgresql?

Privileges refer to the permissions and rights granted to database objects, such as tables, views, schemas, and functions, allowing users and roles to perform various actions on these objects
In PostgreSQL, multiple privileges are present for every object that is created. By default, the owner (or a superuser) of an object has all the privileges on it. In PostgreSQL, the following types of privileges are present:

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• REFERENCES
• TRIGGER
• CREATE
• CONNECT
• TEMPORARY
• EXECUTE
• USAGE

There are different privileges associated with different objects. For instance, the EXECUTE privilege is associated with procedure. The GRANT command is used to grant any privilege to any user. It allows you to control what actions users can perform on the object. The syntax for granting privileges is as follows:

GRANT privilege_name(s) ON object_name TO user_name_or_role;

To grant the EXECUTE privilege on a procedure named "my_function" to a user "my_user," you would use:

GRANT EXECUTE ON FUNCTION my_function TO my_user;

Similarly, to take back privileges, the REVOKE command is used. The REVOKE command is used to revoke previously granted privileges from a user or role. It allows you to take back specific privileges that were previously assigned. The syntax for revoking privileges is shown below:

REVOKE privilege_name(s) ON object_name FROM user_name_or_role;

To revoke the previously granted EXECUTE privilege on the "my_function" procedure from the "my_user," you would use:

REVOKE EXECUTE ON FUNCTION my_function FROM my_user;

It is important to note that managing privileges in PostgreSQL is crucial for access control in your data and ensuring the security and integrity of your database is intact.