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 assumes 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.
However, to create, alter, drop, or change membership of a
superuser role, superuser status is required;
CREATEROLE is insufficient for that.
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'.
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.
Tip: 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 rolename RESET varname.
Note that role-specific defaults attached to roles without
LOGIN privilege are fairly useless, since they will never
be invoked.