Every database cluster contains a set of database users. Those users are separate from the users managed by the operating system on which the server runs. Users own database objects (for example, tables) and can assign privileges on those objects to other users to control who has access to which object. This chapter describes how to create and manage users and introduces the privilege system. More information about the various types of database objects and the effects of privileges can be found in . user CREATE USER DROP USER 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 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 command: DROP USER name; createuser dropuser For convenience, the programs and are provided as wrappers around these SQL commands that can be called from the shell command line: createuser name dropuser name To determine the set of existing users, examine the pg_user system catalog, for example SELECT usename FROM pg_user; The program's \du meta-command is also useful for listing the existing users. 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 database cluster. Customarily, this user will be named postgres. In order to create more users you first have to connect as this initial user. Exactly one user identity is active for a connection to the database server. 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. Many applications assume the name of the current operating system user by default (including createuser and psql). Therefore it is convenient to maintain a naming correspondence between the two user sets. 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, just as a person's login name need not match her real name.) Since the user identity determines the set of privileges available to a connected client, it is important to carefully configure this when setting up a multiuser environment. A database user may have a number of attributes that define its privileges and interact with the client authentication system. superusersuperuser 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 creationdatabaseprivilege to create 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. passwordpassword 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 user creation with CREATE USER name PASSWORD 'string'. A user's attributes can be modified after creation with ALTER USER.ALTER USER See the reference pages for the and commands for details. A user can also set personal defaults for many of the run-time configuration settings described in . For example, if for some reason you want to disable index scans (hint: not a good idea) anytime you connect, you can use ALTER USER myname SET enable_indexscan TO off; This will save the setting (but not set it immediately). In subsequent connections by this user 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 undo any such setting, use ALTER USER username RESET varname;. group As in Unix, groups are a way of logically grouping users to ease management of privileges: privileges can be granted to, or revoked from, a group as a whole. To create a group, use the SQL command: CREATE GROUP name; To add users to or remove users from an existing group, use : ALTER GROUP name ADD USER uname1, ... ; ALTER GROUP name DROP USER uname1, ... ; To destroy a group, use : DROP GROUP name; This only drops the group, not its member users. To determine the set of existing groups, examine the pg_group system catalog, for example SELECT groname FROM pg_group; The program's \dg meta-command is also useful for listing the existing groups. privilege owner GRANT REVOKE Being moved to the DDL chapter. Will eventually disappear here. When an object is created, it is assigned an owner. The owner is normally the user that executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. To allow other users to use it, privileges must be granted. There are several different kinds of privilege: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, and USAGE. For more information on the different types of privileges supported by PostgreSQL, see the reference page. To assign privileges, the GRANT command is used. So, if joe is an existing user, and accounts is an existing table, the privilege to update the table can be granted with GRANT UPDATE ON accounts TO joe; To grant a privilege to a group, use GRANT SELECT ON accounts TO GROUP staff; The special 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 that apply to the object will be granted. To revoke a privilege, use the fittingly named REVOKE command: REVOKE ALL ON accounts FROM PUBLIC; The special privileges of an object's owner (i.e., the right to modify or destroy the object) are always implicit in being the owner, and cannot be granted or revoked. But the owner can choose to revoke his own ordinary privileges, for example to make a table read-only for himself as well as others. An object can be assigned to a new owner with an ALTER command of the appropriate kind for the object. Only superusers can do this. 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 ease. The only real protection is tight control over who can define functions. Functions run inside the backend server process with the operating system permissions of the database server daemon. If the programmming language used for the function allows unchecked memory accesses, it is possible to change the server's internal data structures. Hence, among many other things, such functions can circumvent any system access controls. Function languages that allow such access are considered untrusted, and PostgreSQL allows only superusers to create functions written in those languages.