Index for Section USER

Alphabetical listing for A

Bottom of page

ALTER
NAME
  ALTER USER - change a database user account

SYNOPSIS
  ALTER USER username [ [ WITH ] option [ ... ] ]

  where option can be:

	[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
      | CREATEDB | NOCREATEDB
      | CREATEUSER | NOCREATEUSER
      | VALID UNTIL 'abstime'

  ALTER USER username SET variable { TO | = } { value | DEFAULT }
  ALTER USER username RESET variable

DESCRIPTION
  ALTER USER is used to change the attributes of a PostgreSQL user account.
  Attributes not mentioned in the command retain their previous settings.

  The first variant of this command in the synopsis changes certain global
  user privileges and authentication settings. (See below for details.) Only
  a database superuser can change privileges and password expiration with
  this command. Ordinary users can only change their own password.

  The second and the third variant change a user's session default for a
  specified configuration variable. Whenever the user subsequently starts a
  new session, the specified value becomes the session default, overriding
  whatever setting is present in postgresql.conf or has been received from
  the postmaster.  Ordinary users can change their own session defaults.
  Superusers can change anyone's session defaults.

  PARAMETERS

  username
       The name of the user whose attributes are to be altered.

  password
       The new password to be used for this account.

  ENCRYPTED

  UNENCRYPTED
       These key words control whether the password is stored encrypted in
       pg_shadow. (See CREATE USER [create_user(5)] for more information
       about this choice.)

  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.

  CREATEUSER

  NOCREATEUSER
       These clauses determine whether a user will be permitted to create new
       users himself. This option will also make the user a superuser who can
       override all access restrictions.

  abstime
       The date (and, optionally, the time) at which this user's password is
       to expire.

  variable

  value
       Set this user's session default for the specified configuration
       variable to the given value. If value is DEFAULT or, equivalently,
       RESET is used, the user-specific variable setting is removed and the
       user will inherit the system-wide default setting in new sessions. Use
       RESET ALL to clear all settings.

       See SET [set(5)] and the Administrator's Guide for more information
       about allowed variable names and values.

DIAGNOSTICS
  ALTER USER
       Message returned if the alteration was successful.

  ERROR: ALTER USER: user "username" does not exist
       Error message returned if the specified user is not known to the
       database.

NOTES
  Use CREATE USER [create_user(5)] to add new users, and DROP USER
  [drop_user(5)] to remove a user.

  ALTER USER cannot change a user's group memberships.	Use ALTER GROUP
  [alter_group(5)] to do that.

  Using ALTER DATABASE [alter_database(5)], it is also possible to tie a
  session default to a specific database rather than a user.

EXAMPLES
  Change a user password:

  ALTER USER davide WITH PASSWORD 'hu8jmn3';

  Change a user's valid until date:

  ALTER USER manuel VALID UNTIL 'Jan 31 2030';

  Change a user's valid until date, specifying that his authorization should
  expire at midday on 4th May 1998 using the time zone which is one hour
  ahead of UTC:

  ALTER USER chris VALID UNTIL 'May 4 12:00:00 1998 +1';

  Give a user the ability to create other users and new databases:

  ALTER USER miriam CREATEUSER CREATEDB;

COMPATIBILITY
  The ALTER USER statement is a PostgreSQL extension. The SQL standard leaves
  the definition of users to the implementation.

SEE ALSO
  CREATE USER [create_user(5)], DROP USER [drop_user(l)], SET [set(l)]

Index for Section USER

Alphabetical listing for A

Top of page