22.2. Role Attributes

22.2. Role Attributes
Prev	Up	Chapter 22. Database Roles	Home	Next

+ 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 LOGIN attribute can be used + as the initial role name for a database connection. A role with + the LOGIN attribute can be considered the same + as a “database user”. To create a role with login privilege, + use either: +

+CREATE ROLE name LOGIN;
+CREATE USER name;
+

+ (CREATE USER is equivalent to CREATE ROLE + except that CREATE USER includes LOGIN by + default, while CREATE ROLE does 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 name SUPERUSER. 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 CREATE ROLE + name CREATEDB. +

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 + name CREATEROLE. + A role with CREATEROLE privilege can alter and drop + other roles, too, as well as grant or revoke membership in them. + Altering a role includes most changes that can be made using + ALTER ROLE, including, for example, changing + passwords. It also includes modifications to a role that can + be made using the COMMENT and + SECURITY LABEL commands. +

+ However, CREATEROLE does not convey the ability to + create SUPERUSER roles, nor does it convey any + power over SUPERUSER roles that already exist. + Furthermore, CREATEROLE does not convey the power + to create REPLICATION users, nor the ability to + grant or revoke the REPLICATION privilege, nor the + ability to modify the role properties of such users. However, it does + allow ALTER ROLE ... SET and + ALTER ROLE ... RENAME to be used on + REPLICATION roles, as well as the use of + COMMENT ON ROLE, + SECURITY LABEL ON ROLE, + and DROP ROLE. + Finally, CREATEROLE does not + confer the ability to grant or revoke the BYPASSRLS + privilege. +

+ Because the CREATEROLE privilege allows a user + to grant or revoke membership even in roles to which it does not (yet) + have any access, a CREATEROLE user can obtain access + to the capabilities of every predefined role in the system, including + highly privileged roles such as + pg_execute_server_program and + pg_write_server_files. +

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 LOGIN permission as well. To create such a role, use + CREATE ROLE name REPLICATION + 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 password and + md5 authentication methods + make use of passwords. Database passwords are separate from + operating system passwords. Specify a password upon role + creation with CREATE ROLE + name PASSWORD 'string'. +

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 CREATE ROLE name NOINHERIT. +

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 ROLE name BYPASSRLS as 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 + CREATE ROLE name CONNECTION LIMIT 'integer'. +

+ + A role's attributes can be modified after creation with + ALTER ROLE. + See the reference pages for the CREATE ROLE + and ALTER ROLE commands for details. +

+ A role can also have role-specific defaults for many of the run-time + configuration settings described in Chapter 20. 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 rolename RESET varname. + Note that role-specific defaults attached to roles without + LOGIN privilege are fairly useless, since they will never + be invoked. +