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 later other roles.
• Replication: A role with this attribute can be used for streaming replication.
• Password: The role password can be used with the md5 authentication method. Also, it can be encrypted. The password expiration can be controlled by specifying the validity period. Note that this password differs from the OS password.
• Connection limit: Connection limit specifies the number of concurrent connections that the user can initiate. Connection creation consumes hardware resources; thus, it is recommended to use connection pooling tools such as pgpool-II or 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 is a member of. If not specified, Inherit is the default.
• Bypassrls: if specified, the role can bypass row level security (RLS).

During the installation of PostgreSQL, the postgres superuser role is created. CREATE USER is equivalent to CREATE ROLE with the LOGIN option, and CREATE GROUP is equivalent to CREATE ROLE with the NOLOGIN option.

A role can be a member of another role to simplify accessing and managing the database permissions; for example, one can create a role with no login, also known as group, and grant its permissions 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.

The roles of a cluster do not necessarily have the privilege to access every database in the cluster.