alter_role(5)

ALTER ROLE()              SQL Commands               ALTER ROLE()

NAME

     ALTER ROLE - change a database role

SYNOPSIS

     ALTER ROLE name [ [ WITH ] option [ ... ] ]

     where option can be:

           SUPERUSER | NOSUPERUSER
         | CREATEDB | NOCREATEDB
         | CREATEROLE | NOCREATEROLE
         | CREATEUSER | NOCREATEUSER
         | INHERIT | NOINHERIT
         | LOGIN | NOLOGIN
         | CONNECTION LIMIT connlimit
         | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
         | VALID UNTIL 'timestamp'

     ALTER ROLE name RENAME TO newname

     ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }
     ALTER ROLE name RESET configuration_parameter

DESCRIPTION

     ALTER ROLE changes the attributes of a PostgreSQL role.

     The first variant of this command listed in the synopsis can
     change  many of the role attributes that can be specified in
     CREATE ROLE [create_role(5)].  (All the possible  attributes
     are  covered, except that there are no options for adding or
     removing  memberships;  use  GRANT  [grant(5)]  and   REVOKE
     [revoke(5)] for that.)  Attributes not mentioned in the com-
     mand retain their previous  settings.   Database  superusers
     can change any of these settings for any role.  Roles having
     CREATEROLE privilege can change any of these  settings,  but
     only  for  non-superuser  roles.   Ordinary  roles  can only
     change their own password.

     The second variant changes the name of the  role.   Database
     superusers  can  rename  any  role.  Roles having CREATEROLE
     privilege can rename non-superuser roles.  The current  ses-
     sion  user  cannot be renamed.  (Connect as a different user
     if you need to do that.)   Because  MD5-encrypted  passwords
     use  the  role  name  as cryptographic salt, renaming a role
     clears its password if the password is MD5-encrypted.

     The third and the fourth variant  change  a  role's  session
     default for a specified configuration variable. Whenever the
     role subsequently starts a new session, the specified  value
     becomes  the session default, overriding whatever setting is

SQL - Language StatementLast change: 2008-01-03 1

ALTER ROLE()              SQL Commands               ALTER ROLE()

     present in postgresql.conf or has  been  received  from  the
     postgres command line.  (For a role without LOGIN privilege,
     session defaults have no effect.)  Ordinary roles can change
     their  own session defaults.  Superusers can change anyone's
     session defaults.  Roles  having  CREATEROLE  privilege  can
     change  defaults for non-superuser roles.  Certain variables
     cannot be set this way, or can only be set  if  a  superuser
     issues the command.

PARAMETERS

     name The name  of  the  role  whose  attributes  are  to  be
          altered.

     SUPERUSER

     NOSUPERUSER

     CREATEDB

     NOCREATEDB

     CREATEROLE

     NOCREATEROLE

     CREATEUSER

     NOCREATEUSER

     INHERIT

     NOINHERIT

     LOGIN

     NOLOGIN

     CONNECTION LIMIT connlimit

     PASSWORD password

     ENCRYPTED

     UNENCRYPTED

     VALID UNTIL 'timestamp'
          These clauses alter attributes originally set by CREATE
          ROLE  [create_role(5)].  For  more information, see the
          CREATE ROLE reference page.

     newname
          The new name of the role.

SQL - Language StatementLast change: 2008-01-03 2

ALTER ROLE()              SQL Commands               ALTER ROLE()

     configuration_parameter

     value
          Set this role's session default for the specified  con-
          figuration  parameter  to  the given value. If value is
          DEFAULT or, equivalently,  RESET  is  used,  the  role-
          specific  variable setting is removed, so the role will
          inherit the system-wide default  setting  in  new  ses-
          sions.  Use  RESET  ALL to clear all role-specific set-
          tings.

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

NOTES

     Use CREATE ROLE [create_role(5)] to add new roles, and  DROP
     ROLE [drop_role(5)] to remove a role.

     ALTER ROLE cannot change a role's  memberships.   Use  GRANT
     [grant(5)] and REVOKE [revoke(5)] to do that.

     Caution must be exercised  when  specifying  an  unencrypted
     password with this command. The password will be transmitted
     to the server in cleartext, and it might also be  logged  in
     the  client's  command  history  or  the  server  log.  psql
     [psql(1)] contains a command \password that can be  used  to
     safely change a role's password.

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

EXAMPLES

     Change a role's password:

     ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

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

     ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

     Make a password valid forever:

     ALTER ROLE fred VALID UNTIL 'infinity';

SQL - Language StatementLast change: 2008-01-03 3

ALTER ROLE()              SQL Commands               ALTER ROLE()

     Give a role the ability to create other roles and new  data-
     bases:

     ALTER ROLE miriam CREATEROLE CREATEDB;

     Give   a    role    a    non-default    setting    of    the
     maintenance_work_mem parameter:

     ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

COMPATIBILITY

     The ALTER ROLE statement is a PostgreSQL extension.

SEE ALSO

     CREATE ROLE [create_role(5)], DROP ROLE [drop_role(l)],  SET
     [set(l)]

SQL - Language StatementLast change: 2008-01-03 4