ALTER USER

The ALTER USER statement can be used to modify an existing defined database user.

Statement syntax

ALTER USER [ IF EXISTS ] @name
  ON [ ROOT | NAMESPACE | DATABASE ]
  [ PASSWORD @pass | PASSHASH @hash ]
  [ ROLES @role, ... ]
  [ DURATION FOR TOKEN [ @duration | NONE ] ]
  [ DURATION FOR SESSION [ @duration | NONE ] ]
  [ COMMENT @string | DROP COMMENT ]

Example usage

-- Define a user with viewer role
DEFINE USER billy ON DATABASE PASSWORD "example" ROLES VIEWER;

-- Congrats on your promotion billy,
-- be sure to use this power for good
ALTER USER billy ON DATABASE ROLES EDITOR;

Changing a user's password with ALTER USER … PASSWORD regenerates the SCRAM-SHA-256 verifier material used by the Postgres wire protocol, in addition to updating the Argon2 hash. ALTER USER … PASSHASH updates the hash and clears any stored SCRAM verifier.