Managing database users and their privileges is in concept similar to managing users of a Unix operating system, but the details are not identical.
Database users are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database user names are global across a database cluster installation (and not per individual database). To create a user use the CREATE USER SQL command:
CREATE USER name
name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. To remove an existing user, use the analogous DROP USER command.
For convenience, the shell scripts createuser and dropuser are provided as wrappers around these SQL commands.
In order to bootstrap the database system, a freshly initialized system always contains one predefined user. This user will have the fixed id 1, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the area (and is presumably being used as the user that runs the server). Customarily, this user will be named postgres. In order to create more users you first have to connect as this initial user.
The user 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 user to
connect as. The set of database users a given client connection
may connect as is determined by the client authentication
setup, as explained in Chapter 4. (Thus, a client is
not necessarily limited to connect as the user with the same
name as its operating system user, in the same way a person is
not constrained in its login name by her real name.)
A database user may have a number of attributes that define its privileges and interact with the client authentication system.
A database superuser bypasses all permission checks. Also, only a superuser can create new users. To create a database superuser, use CREATE USER name CREATEUSER.
A user must be explicitly given permission to create databases (except for superusers, since those bypass all permission checks). To create such a user, use CREATE USER name CREATEDB.
A password is only significant if password authentication is used for client authentication. Database passwords are separate from operating system passwords. Specify a password upon user creation with CREATE USER name PASSWORD 'string'.
A user's attributes can be modified after creation with ALTER USER. See the reference pages for CREATE USER and ALTER USER for details.