PostgreSQL – Privileges
Privileges control the actions that users and roles are allowed to perform on database objects such as tables, views, functions, and schemas. Privileges determine who can read, write, modify, or delete data within the database. PostgreSQL provides a fine-grained privilege system that allows you to control access at different levels of granularity.
Here are some common privileges and their usage:
SELECT: Allows users to retrieve data from a table or view.
GRANT SELECT ON table_name TO username;INSERT: Allows users to add new rows to a table.
GRANT INSERT ON table_name TO username;UPDATE: Allows users to modify existing rows in a table.
GRANT UPDATE ON table_name TO username;DELETE: Allows users to remove rows from a table.
GRANT DELETE ON table_name TO username;USAGE: Allows users to use sequences, domains, or non-table objects.
GRANT USAGE ON sequence_name TO username;EXECUTE: Allows users to execute a function or procedure.
GRANT EXECUTE ON FUNCTION function_name(arg_type, ...) TO username;ALL PRIVILEGES: Grants all available privileges for a specific object.
GRANT ALL PRIVILEGES ON table_name TO username;GRANT OPTION: Allows a user to grant the same privilege to other users.
GRANT SELECT ON table_name TO username WITH GRANT OPTION;Privileges can be granted to individual users or roles. Roles can also be members of other roles, allowing you to manage privileges hierarchically.
To remove privileges, you can use the REVOKE statement:
REVOKE privilege_name ON object_name FROM username;For example, to remove the SELECT privilege from a user on a table:
REVOKE SELECT ON table_name FROM username;Pivileges should be managed carefully to ensure the security and integrity of your PostgreSQL database. Grant only the necessary privileges to users and roles to minimize the risk of unauthorized access or accidental data modification.