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 theLOGINattribute can be considered the same as a“database user”. To create a role with login privilege, use either:CREATE ROLE
nameLOGIN;CREATE USERname;(
CREATE USERis equivalent toCREATE ROLEexcept thatCREATE USERincludesLOGINby default, whileCREATE 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.nameSUPERUSER- 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
CREATE ROLE.nameCREATEDB- 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 withnameCREATEROLECREATEROLEprivilege can alter and drop roles which have been granted to theCREATEROLEuser with theADMINoption. Such a grant occurs automatically when aCREATEROLEuser that is not a superuser creates a new role, so that by default, aCREATEROLEuser can alter and drop the roles which they have created. Altering a role includes most changes that can be made usingALTER ROLE, including, for example, changing passwords. It also includes modifications to a role that can be made using theCOMMENTandSECURITY LABELcommands.However,
CREATEROLEdoes not convey the ability to createSUPERUSERroles, nor does it convey any power overSUPERUSERroles that already exist. Furthermore,CREATEROLEdoes not convey the power to createREPLICATIONusers, nor the ability to grant or revoke theREPLICATIONprivilege, nor the ability to modify the role properties of such users. However, it does allowALTER ROLE ... SETandALTER ROLE ... RENAMEto be used onREPLICATIONroles, as well as the use ofCOMMENT ON ROLE,SECURITY LABEL ON ROLE, andDROP ROLE. Finally,CREATEROLEdoes not confer the ability to grant or revoke theBYPASSRLSprivilege.- 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, useCREATE ROLE.nameREPLICATION LOGIN- password
A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The
passwordandmd5authentication methods make use of passwords. Database passwords are separate from operating system passwords. Specify a password upon role creation withCREATE ROLE.namePASSWORD 'string'- inheritance of privileges
A role inherits the privileges of roles it is a member of, by default. However, to create a role which does not inherit privileges by default, use
CREATE ROLE. Alternatively, inheritance can be overridden for individual grants by usingnameNOINHERITWITH INHERIT TRUEorWITH INHERIT FALSE.- 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.nameBYPASSRLS- 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
CREATE ROLE.nameCONNECTION LIMIT 'integer'
A role's attributes can be modified after creation withALTER ROLE. See the reference pages for theCREATE ROLE andALTER ROLE commands for details.
A role can also have role-specific defaults for many of the run-time configuration settings described inChapter 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 thoughSET 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, useALTER ROLE. Note that role-specific defaults attached to roles withoutrolename RESETvarnameLOGIN privilege are fairly useless, since they will never be invoked.
When a non-superuser creates a role using theCREATEROLE privilege, the created role is automatically granted back to the creating user, just as if the bootstrap superuser had executed the commandGRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE. Since aCREATEROLE user can only exercise special privileges with regard to an existing role if they haveADMIN OPTION on it, this grant is just sufficient to allow aCREATEROLE user to administer the roles they created. However, because it is created withINHERIT FALSE, SET FALSE, theCREATEROLE user doesn't inherit the privileges of the created role, nor can it access the privileges of that role usingSET ROLE. However, since any user who hasADMIN OPTION on a role can grant membership in that role to any other user, theCREATEROLE user can gain access to the created role by simply granting that role back to themselves with theINHERIT and/orSET options. Thus, the fact that privileges are not inherited by default nor isSET ROLE granted by default is a safeguard against accidents, not a security feature. Also note that, because this automatic grant is granted by the bootstrap superuser, it cannot be removed or changed by theCREATEROLE user; however, any superuser could revoke it, modify it, and/or issue additional such grants to otherCREATEROLE users. WhicheverCREATEROLE users haveADMIN OPTION on a role at any given time can administer it.