PostgreSQL – GRANT Statement
The GRANT statement is used to assign privileges to users or roles on database objects like tables, views, functions, and schemas. The GRANT statement allows you to control who can perform specific actions on these objects.
Here is the basic syntax of the GRANT statement:
GRANT privilege_name [, ...] ON object_name TO user_or_role [, ...] [WITH GRANT OPTION];
privilege_name: The name of the privilege you want to grant (e.g., SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.).object_name: The name of the database object on which you want to grant the privilege (e.g., table name, view name, function name, etc.).user_or_role: The user or role to whom you want to grant the privilege.WITH GRANT OPTION: This optional clause allows the grantee to further grant the same privilege to others.
Here are a few examples of how the GRANT statement can be used:
Granting SELECT privilege on a table to a user:
GRANT SELECT ON table_name TO username;Granting multiple privileges on a table to a role:
GRANT SELECT, INSERT, UPDATE ON table_name TO rolename;Granting EXECUTE privilege on a function to a role:
GRANT EXECUTE ON FUNCTION function_name(arg_type, ...) TO rolename;Granting all privileges on a schema to a user:
GRANT ALL PRIVILEGES ON SCHEMA schema_name TO username;Granting a privilege with the ability to grant it to others:
GRANT SELECT ON table_name TO username WITH GRANT OPTION;Privileges can also be revoked using the REVOKE statement. Proper privilege management is crucial for maintaining the security and integrity of your PostgreSQL database. Only grant the necessary privileges to users and roles to ensure that they can perform their intended tasks without compromising data security.