A database role can have a number of attributes that define its privileges and interact with the client authentication system.
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 ROLEname
LOGIN; CREATE USERname
;
(CREATE USER
is equivalent to CREATE ROLE
except that CREATE USER
includes LOGIN
by
default, while CREATE ROLE
does not.)
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.
name
SUPERUSER
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
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 name
CREATEROLECREATEROLE
privilege can alter and drop
roles which have been granted to the CREATEROLE
user with the ADMIN
option. Such a grant occurs
automatically when a CREATEROLE
user that is not
a superuser creates a new role, so that by default, a
CREATEROLE
user can alter and drop the roles
which they have created.
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.
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
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
'
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 using name
NOINHERITWITH INHERIT TRUE
or WITH INHERIT FALSE
.
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
as a superuser.
name
BYPASSRLS
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
.
Note that role-specific defaults attached to roles without
rolename
RESET varname
LOGIN
privilege are fairly useless, since they will never
be invoked.
When a non-superuser creates a role using the CREATEROLE
privilege, the created role is automatically granted back to the creating
user, just as if the bootstrap superuser had executed the command
GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
INHERIT FALSE
. Since a CREATEROLE
user can
only exercise special privileges with regard to an existing role if they
have ADMIN OPTION
on it, this grant is just sufficient
to allow a CREATEROLE
user to administer the roles they
created. However, because it is created with INHERIT FALSE, SET
FALSE
, the CREATEROLE
user doesn't inherit the
privileges of the created role, nor can it access the privileges of that
role using SET ROLE
. However, since any user who has
ADMIN OPTION
on a role can grant membership in that
role to any other user, the CREATEROLE
user can gain
access to the created role by simply granting that role back to
themselves with the INHERIT
and/or SET
options. Thus, the fact that privileges are not inherited by default nor
is SET 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 user, it cannot be removed or changed by
the CREATEROLE
user; however, any superuser could
revoke it, modify it, and/or issue additional such grants to other
CREATEROLE
users. Whichever CREATEROLE
users have ADMIN OPTION
on a role at any given time
can administer it.