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 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; CREATEROLE
is insufficient for that.
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 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
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.
It is good practice to create a role that has the CREATEDB
and 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 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.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.