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 always 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.