Biblioteca de cunoștințe
Manage Database Roles
Solution Database roles are conceptually completely separate from operating system users. Database roles are global across a database cluster installation. To create a role use the CREATE ROLE SQL command:
CREATE ROLE name;
To remove an existing role, use the analogous DROP ROLE command:
DROP ROLE name;
For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:
To determine the set of existing roles, examine the pg_roles system catalog, for example
SELECT rolname FROM pg_roles;
The psql program’s \du meta-command is also useful for listing the existing roles.
In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.
Every connection to the database server is made in the name of some particular role, and this role determines the initial access privileges for commands issued on that connection. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. For example, the psql program uses the -U command line option to indicate the role to connect as. Many applications assume the name of the current operating system user by default (including createuser and psql). Therefore it is often convenient to maintain a naming correspondence between roles and operating system users.
The set of database roles a given client connection may connect as is determined by the client authentication setup. Since the role identity determines the set of privileges available to a connected client, it is important to carefully configure this when setting up a multiuser environment.