| PostgreSQL 8.2.6 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 18. Database Roles and Privileges | Fast Forward | Next |
A database role may 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 thing 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.)
A database superuser bypasses all permission checks. 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.
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 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 not sufficient for that.
A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The password , md5 , and crypt 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 17 . 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.