CREATE USER SQL - Language Statements CREATE USER Creates account information for a new user 1999-07-20 CREATE USER username [ WITH PASSWORD password ] [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] [ IN GROUP groupname [, ...] ] [ VALID UNTIL 'abstime' ] 1998-09-21 username The name of the user. password The WITH PASSWORD clause sets the user's password within the "pg_shadow" table. For this reason, "pg_shadow" is no longer accessible to the instance of Postgres that the Postgres user's password is initially set to NULL. When a user's password in the "pg_shadow" table is NULL, user authentication proceeds as it historically has (HBA, PG_PASSWORD, etc). However, if a password is set for a user, a new authentication system supplants any other configured for the Postgres instance, and the password stored in the "pg_shadow" table is used for authentication. For more details on how this authentication system functions see pg_crypt(3). If the WITH PASSWORD clause is omitted, the user's password is set to the empty string which equates to a NULL value in the authentication system mentioned above. CREATEDB NOCREATEDB These clauses define a user's ability to create databases. If CREATEDB is specified, the user being defined will be allowed to create his own databases. Using NOCREATEDB will deny a user the ability to create databases. If this clause is omitted, NOCREATEDB is used by default. CREATEUSER NOCREATEUSER These clauses determine whether a user will be permitted to create new users in an instance of Postgres. Omitting this clause will set the user's value of this attribute to be NOCREATEUSER. groupname A name of a group into which to insert the user as a new member. abstime The VALID UNTIL clause sets an absolute time after which the user's Postgres login is no longer valid. Please note that if a user does not have a password defined in the "pg_shadow" table, the valid until date will not be checked during user authentication. If this clause is omitted, a NULL value is stored in "pg_shadow" for this attribute, and the login will be valid for all time. 1998-09-21 CREATE USER Message returned if the command completes successfully. 1998-09-21 CREATE USER will add a new user to an instance of Postgres. The new user will be given a usesysid of: SELECT MAX(usesysid) + 1 FROM pg_shadow; This means that Postgres users' usesysids will not correspond to their operating system(OS) user ids. The exception to this rule is the postgres superuser, whose OS user id is used as the usesysid during the initdb process. If you still want the OS user id and the usesysid to match for any given user, use the createuser script provided with the Postgres distribution. 1998-09-21 CREATE USER statement is a Postgres language extension. Use DROP USER or ALTER USER statements to remove or modify a user account. Refer to the pg_shadow table for further information. Table = pg_shadow +--------------------------+--------------------------+-------+ | Field | Type | Length| +--------------------------+--------------------------+-------+ | usename | name | 32 | | usesysid | int4 | 4 | | usecreatedb | bool | 1 | | usetrace | bool | 1 | | usesuper | bool | 1 | | usecatupd | bool | 1 | | passwd | text | var | | valuntil | abstime | 4 | +--------------------------+--------------------------+-------+ Create a user with no password: CREATE USER jonathan Create a user with a password: CREATE USER davide WITH PASSWORD "jw8s0F4" Create a user with a password, whose account is valid until the end of 2001. Note that after one second has ticked in 2002, the account is not valid: CREATE USER miriam WITH PASSWORD "jw8s0F4" VALID UNTIL 'Jan 1 2002' Create an account where the user can create databases: CREATE USER manuel WITH PASSWORD "jw8s0F4" CREATEDB 1998-09-21 There is no CREATE USER statement in SQL92.