PostgreSQL – Roles
Roles are used to manage authentication and authorization. Roles can represent users or groups of users, and they control who can access the database and what actions they are allowed to perform. PostgreSQL provides two main types of roles: user roles and group roles (also known as role hierarchies).
User Roles: User roles represent individual users and allow them to connect to a PostgreSQL database. Users can have privileges assigned to them that determine what they can do within the database.
Creating a User Role:
CREATE ROLE username LOGIN PASSWORD 'password'; Granting Privileges to a User Role:
GRANT privilege_name ON table_name TO username;Group Roles (Role Hierarchies): Group roles are roles that don’t log in directly but are used to manage sets of users. A group role can have other roles as members. Privileges granted to a group role are automatically available to its member roles.
Creating a Group Role:
CREATE ROLE groupname; Adding Members to a Group Role:
GRANT groupname TO username; Granting Privileges to a Group Role:
GRANT privilege_name ON table_name TO groupname; Adding a Group Role as a Member of Another Group Role:
GRANT member_groupname TO groupname;Roles can be granted various privileges, such as SELECT, INSERT, UPDATE, DELETE, and more, which determine what actions a user or group can perform on tables, views, and other database objects.
Additionally, PostgreSQL has some predefined roles:
postgres: The superuser role that has all privileges.public: A special schema where all users haveCREATEprivilege by default.pg_read_all_settings,pg_write_all_settings: Roles for configuring server settings.pg_signal_backend: Role for signaling backend processes.
Remember that proper role and privilege management is essential for securing your PostgreSQL database. Assign privileges carefully to ensure that users and groups only have the necessary access to perform their required tasks.