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 . (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. superuser 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. database creation 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. password 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. As in Unix, groups are a way of logically grouping users to ease management of permissions: permissions can be granted to, or revoked from, a group as a whole. To create a group, use CREATE GROUP name To add users to or remove users from a group, use ALTER GROUP name ADD USER uname1, ... ALTER GROUP name DROP USER uname1, ... When a database object is created, it is assigned an owner. The owner is the user that executed the creation statement. There is currently no polished interface for changing the owner of a database object. By default, only an owner (or a superuser) can do anything with the object. In order to allow other users to use it, privileges must be granted. There are several different privileges: SELECT (read), INSERT (append), UPDATE (write), DELETE, RULE, REFERENCES (foreign key), and TRIGGER. (See the GRANT manual page for more detailed information.) The right to modify or destroy an object is always the privilege of the owner only. To assign privileges, the GRANT command is used. So, if joe is an existing user, and accounts is an existing table, write access can be granted with GRANT UPDATE ON accounts TO joe; The user executing this command must be the owner of the table. To grant a privilege to a group, use GRANT SELECT ON accounts TO GROUP staff; The special user name PUBLIC can be used to grant a privilege to every user on the system. Writing ALL in place of a specific privilege specifies that all privileges will be granted. To revoke a privilege, use the fittingly named REVOKE command: REVOKE ALL ON accounts FROM PUBLIC; The special privileges of the table owner (i.e., the right to do DROP, GRANT, REVOKE, etc) are always implicit in being the owner, and cannot be granted or revoked. But the table owner can choose to revoke his own ordinary privileges, for example to make a table read-only for himself as well as others. Functions and triggers allow users to insert code into the backend server that other users may execute without knowing it. Hence, both mechanisms permit users to Trojan horse others with relative impunity. The only real protection is tight control over who can define functions (e.g., write to relations with SQL fields) and triggers. Audit trails and alerters on the system catalogs pg_class, pg_shadow and pg_group are also possible. Functions written in any language except SQL run inside the backend server process with the operating systems permissions of the database server daemon process. It is possible to change the server's internal data structures from inside of trusted functions. Hence, among many other things, such functions can circumvent any system access controls. This is an inherent problem with user-defined C functions.