alter user -- alter user account information within a PostgreSQL instance

.nf

\fBalteruser\fR username

[\fBwithpassword\fR password]

[\fBcreatedb\fR |\fBnocreatedb\fR]

[\fBcreateuser\fR |\fBnocreateuser\fR]

[\fBingroup\fR group-1, ..., group-n]

[\fBvaliduntil'\fRabstime\fB'\fR]

.fi

.BR"alter user"

is used to change the attributes of a user's PostgreSQL account. For a

detailed description of each of the clause in the alter user statement,

please see the create_user(l) manual page. Please note that it is not

possible to alter a user's usesysid via the alter user statement. Also,

it is only possible for the postgres user or any user with read and modify

permissions on pg_user to alter user passwords.

If any of the clauses of the alter user statement are omitted, the

corresponding value in the pg_user relation is left unchanged.

This statement can be used to modify users created with createuser(1).

.nf

---

--- Change a user password

---

alter user tab with password hu8jmn3;

.fi

.nf

---

--- Change a user's valid until date

---

alter user tab valid until 'Jan 31 2030';

.fi

.nf

---

--- Give a user the ability to create other users.

---

alter user tab createuser;

.fi

create_user(l), drop_user(l).

create user -- create a new user within a PostgreSQL instance

.nf

[\fBwithpassword\fR password]

[\fBcreatedb\fR |\fBnocreatedb\fR]

[\fBcreateuser\fR |\fBnocreateuser\fR]

[\fBingroup\fR group-1, ..., group-n]

[\fBvaliduntil'\fRabstime\fB'\fR]

.fi

.BR"create user"

will add a new user to an instance of PostgreSQL. The new user will be

given a usesysid of 'SELECT max(usesysid) + 1 FROM pg_user'. This means

that a PostgreSQL user's usesysid will not correspond to their operating

system(OS) user id. The exception to this rule is the 'postgres' user,

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, then use the createuser(1) script provided with the PostgreSQL

distribution.

The 'with password' clause sets the user's password within the pg_user

relation. For this reason, pg_user is no longer accessible to the

'public'group.Pleasenotethatwheninitdb(1)isexecutedforan

instance of PostgreSQL that the postgres user's password is initially set

to NULL. When a user's password in the pg_user relation is NULL, then

user authentication proceeds as it historically has (HBA, PG_PASSWORD,

etc). However, if a password is set for a user, then a new authentication

system supplants any other configured for the PostgreSQL instance, and the

password stored in the pg_user relation is used for authentication. For

more details on how this authentication system functions see pg_crypt(3).

If the 'with password' clause is omitted, then the user's password is set

to the empty string with equates to a NULL value in the authentication

system mentioned above.

The createdb/nocreatedb clause defines a user's ability to create

databases. If createdb is specified, then the user being defined will be

allowed to create his/her own databases. Using nocreatedb will deny a

user the ability to create databases. If this clause is omitted, then

nocreatedb is used by default.

The createuser/nocreateuser clause allows/prevents a user from creating

new users in an instance of PostgreSQL. Omitting this clause will set the

user's value of this attribute to be nocreateuser.

At the current time the 'in group' clause is non-functional. The intent

is to use this clause to affect the groups a user is a member of (as

defined in the pg_group relation).

Finally, the 'valid until' clause sets an absolute time after which the

user's PostgreSQL login is no longer valid. Please note that if a user

does not have a password defined in the pg_user relation, then the valid

until date will not be checked during user authentication. If this clause

is omitted, then a NULL value is stored in pg_user for this attribute, and

the login will be valid for all time.

.nf

---

--- Create a user with no password

---

create user tab;

.fi

.nf

---

--- Create a user with a password

---

create user tab with password jw8s0F4;

.fi

.nf

---

--- Create a user with a password, whose account is valid thru 2001

--- Note that after one second has ticked in 2002, the account is not

--- valid

---

create user tab with password jw8s0F4 valid until 'Jan 1 2002';

.fi

.nf

---

--- Create an account where the user can create databases.

---

create user tab with password jw8s0F4 createdb;

.fi

pg_crypt(3), alter_user(l), drop_user(l).

drop user -- drop user from within a PostgreSQL instance

.nf

\fBdropuser\fR username

.fi

.BR"drop user"

statement removes the named user from a PostgreSQL instance, along with

any databases owned by the user. It does not remove tables, views, or

triggers owned by the named user in database not owned by the user. This

statement can be used in the place of destroyuser(1), regardless of how

the user was created.

.nf

---

--- Drop a user

---

drop user tab;

.fi

alter_user(l), create_user(l).