Roles

Roles belong to the PostgreSQL server cluster and not to a certain database. A role can either be a database user or a database group. The role concept subsumes the concepts of users and groups in the old PostgreSQL versions. For compatibility reasons, with PostgreSQL version 8.1 and later, the CREATE USER and CREATE GROUP SQL commands are still supported.

The roles have several attributes, which are as follows:

• SUPERUSER: A superuser role can bypass all permission checks except the LOGIN attribute.
• LOGIN: A role with the LOGIN attribute can be used by a client to connect to a database.
• CREATEDB: A role with the create database attribute can create databases.
• CREATEROLE: A role with this feature enabled can create, delete, and alter other roles.
• REPLICATION: A role with this attribute can be used to stream replication.
• PASSWORD: The PASSWORD role can be used with the md5 and scram-sha-256 authentication method. The password expiration can be controlled by specifying the validity period. Note that this password differs from the OS password. In newer versions of PostgreSQL server—mainly 10 and 11—it's recommended to use scram-sha-256, instead of md5, because it's more secure.
• CONNECTION LIMIT: This specifies the number of concurrent connections that the user can initiate. Connection creation consumes hardware resources; thus, it's recommended to use connection pooling tools such as Pgpool-II, Yandex Odyssey, PgBouncer, or some APIs, such as Apache DBCP or c3p0.
• INHERIT: If specified, the role will inherit the privileges assigned to the roles that it's a member of. If not specified, INHERIT is the default.
• BYPASSRLS: If specified, this role can bypass row-level security (RLS).

A role can be a member of another role to simplify accessing and managing database permissions; for example, you can create a role with no login, also known as a group, and grant it permission to access the database objects. If a new role needs to access the same database objects with the same permissions as the group, the new role could be assigned a membership to this group. This is achieved by the GRANT and REVOKE SQL commands, which are discussed in detail in Chapter 11, PostgreSQL Security.