21.2. Role Attributes
A database role can have a number of attributes that define its privileges and interact with the client authentication system.
- login privilege
Only roles that have the
LOGINattribute can be used as the initial role name for a database connection. A role with the
LOGINattribute can be considered the same as a “database user”. To create a role with login privilege, use either:
nameLOGIN; CREATE USER
CREATE USERis equivalent to
CREATE ROLEexcept that
LOGINby default, while
CREATE ROLEdoes not.)
- superuser status
A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser. To create a new database superuser, use
CREATE ROLE. You must do this as a role that is already a superuser.
- database creation
A role must be explicitly given permission to create databases (except for superusers, since those bypass all permission checks). To create such a role, use
- role creation
A role must be explicitly given permission to create more roles (except for superusers, since those bypass all permission checks). To create such a role, use
CREATE ROLE. A role with
CREATEROLEprivilege can alter and drop other roles, too, as well as grant or revoke membership in them. However, to create, alter, drop, or change membership of a superuser role, superuser status is required;
CREATEROLEis insufficient for that.
- initiating replication
A role must explicitly be given permission to initiate streaming replication (except for superusers, since those bypass all permission checks). A role used for streaming replication must have
LOGINpermission as well. To create such a role, use
A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The
md5authentication methods make use of passwords. Database passwords are separate from operating system passwords. Specify a password upon role creation with
- inheritance of privileges
A role is given permission to inherit the privileges of roles it is a member of, by default. However, to create a role without the permission, use
- bypassing row-level security
A role must be explicitly given permission to bypass every row-level security (RLS) policy (except for superusers, since those bypass all permission checks). To create such a role, use
CREATE ROLEas a superuser.
- connection limit
Connection limit can specify how many concurrent connections a role can make. -1 (the default) means no limit. Specify connection limit upon role creation with
nameCONNECTION LIMIT '
It is good practice to create a role that has the
CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it.
A role can also have role-specific defaults for many of the run-time configuration settings described in Chapter 19. For example, if for some reason you want to disable index scans (hint: not a good idea) anytime you connect, you can use:
ALTER ROLE myname SET enable_indexscan TO off;
This will save the setting (but not set it immediately). In subsequent connections by this role it will appear as though
SET enable_indexscan TO off had been executed just before the session started. You can still alter this setting during the session; it will only be the default. To remove a role-specific default setting, use
ALTER ROLE . Note that role-specific defaults attached to roles without
LOGIN privilege are fairly useless, since they will never be invoked.