Index for Section USER

Alphabetical listing for A

Bottom of page

ALTER
NAME
  ALTER USER - change a database user account

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

  where option can be:

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

  ALTER USER name RENAME TO newname

  ALTER USER name SET parameter { TO | = } { value | DEFAULT }
  ALTER USER name RESET parameter

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

  The first variant of this command listed in the synopsis changes certain
  per-user privileges and authentication settings. (See below for details.)
  Database superusers can change any of these settings for any user. Ordinary
  users can only change their own password.

  The second variant changes the name of the user. Only a database superuser
  can rename user accounts. The current session user cannot be renamed.
  (Connect as a different user if you need to do that.) Because MD5-encrypted
  passwords use the user name as cryptographic salt, renaming a user clears
  their MD5 password.

  The third and the fourth 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 command line.	Ordinary users can change their own session
  defaults.  Superusers can change anyone's session defaults.  Certain
  variables cannot be set this way, or can only be set by a superuser.

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

  CREATEDB

  NOCREATEDB
       These clauses define a user's ability to create databases. If CREATEDB
       is specified, the user will be allowed to create his own databases.
       Using NOCREATEDB will deny a user the ability to create databases. (If
       the user is also a superuser, then this setting has no real effect.)

  CREATEUSER

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

  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.)

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

  newname
       The new name of the user.

  parameter

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

       See SET [set(5)] and the documentation for more information about
       allowed parameter names and values.

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.

  The VALID UNTIL clause defines an expiration time for a password only, not
  for the user account per se. In particular, the expiration time is not
  enforced when logging in using a non-password-based authentication method.

  It is also possible to tie a session default to a specific database rather
  than to a user; see ALTER DATABASE [alter_database(5)].  User-specific
  settings override database-specific ones if there is a conflict.

EXAMPLES
  Change a user's password:

  ALTER USER davide WITH PASSWORD 'hu8jmn3';

  Change the expiration date of the user's password:

  ALTER USER manuel VALID UNTIL 'Jan 31 2030';

  Change a password expiration date, specifying that the password should
  expire at midday on 4th May 2005 using the time zone which is one hour
  ahead of UTC:

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

  Make a password valid forever:

  ALTER USER fred VALID UNTIL 'infinity';

  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