Roles[edit | edit source]
PostgreSQL supports the concept of
roles  to handle security issues within the database. Roles are independent from operating system user accounts (with the exception of the special case peer authentication which is defined in the pg_hba.conf file).
The concept of roles subsumes the concepts of individual users and groups of users with similar rights. A role can be thought of as either an individual database user, or a group of database users, depending on how the role is set up. Thus the outdated SQL command
CREATE USER ... is only an alias for
CREATE ROLE ... . Roles have certain privileges on database objects like tables or functions and can assign those privileges to other roles. Roles are global across a cluster - not per individual database.
Often individual users, which shall have identical privileges, are grouped together to a user group and the privileges are granted to that group.
-- ROLE, in the sense of a group of individual users or other roles CREATE ROLE group_1 ENCRYPTED PASSWORD 'xyz'; -- assign some rights to the role GRANT SELECT ON table_1 TO group_1; -- ROLE, in the sense of some individual users CREATE ROLE adam LOGIN ENCRYPTED PASSWORD 'xyz'; -- Default is NOLOGIN CREATE ROLE anne LOGIN ENCRYPTED PASSWORD 'xyz'; -- the link between user group and individual users GRANT group_1 TO adam, anne;
CREATE ROLE command you can assign the privileges SUPERUSER, CREATEDB, CREATEROLE, REPLICATION and LOGIN to that role. With the
GRANT command you can assign access privileges to database objects like tables. The second purpose of the
GRANT command is the definition of the group membership.
In addition to the roles created by the database administrator there is always the special role PUBLIC, which can be thought of as a role which is a member of all other roles. Thus, privileges assigned to PUBLIC are implicitly given to all roles, even if those roles are created at a later stage.
List existing roles[edit | edit source]
Roles can be listed with the following commands.
With SQL, this will display an additional set of postgreSQL default roles that group together sets of common access levels:
SELECT rolname FROM pg_roles;
or the psql command:
References[edit | edit source]
- Concept of roles