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:
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.
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 18. 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 varnameLOGIN privilege are fairly useless, since they will never
be invoked.