Man Pages

alter_role(7) - phpMan alter_role(7) - phpMan

Command: man perldoc info search(apropos)  


ALTER ROLE(7)                    SQL Commands                    ALTER ROLE(7)



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 SET configuration_parameter FROM CURRENT
       ALTER ROLE name RESET configuration_parameter
       ALTER ROLE name RESET ALL


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(7)].  (All the possible attributes are covered, except that there are  no
       options  for adding or removing memberships; use GRANT [grant(7)] and REVOKE [revoke(7)] for that.)  Attributes
       not mentioned in the command retain their previous settings.  Database superusers can change any of these  set-
       tings for any role.  Roles having CREATEROLE privilege can change any of these settings, but only for non-supe-
       ruser 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  CRE-
       ATEROLE  privilege  can rename non-superuser roles.  The current session 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  remaining  variants  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 present in postgresql.conf or has been received from the postgres command line. This only happens at
       login time, so configuration settings associated with a role to which you've SET  ROLE  [set_role(7)]  will  be
       ignored.   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(7)]. For more information, see
              the CREATE ROLE reference page.

       newname
              The new name of the role.

       configuration_parameter

       value  Set this role's session default for the specified configuration 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 sessions. Use RESET ALL to clear  all  role-specific
              settings.   SET  FROM  CURRENT  saves  the session's current value of the parameter as the role-specific
              value.

              Role-specific variable setting take effect only at login; SET ROLE [set_role(7)] does not process  role-
              specific variable settings.

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

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

       ALTER ROLE cannot change a role's memberships.  Use GRANT [grant(7)] and REVOKE [revoke(7)] 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(7)].  Role-specific settings override database-specific ones if there is a conflict.

EXAMPLES
       Change a role's password:

       ALTER ROLE davide WITH PASSWORD 'hu8jmn3';


       Remove a role's password:

       ALTER ROLE davide WITH PASSWORD NULL;


       Change  a  password expiration date, specifying that the password 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';


       Give a role the ability to create other roles and new databases:

       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(7)], DROP ROLE [drop_role(7)], SET [set(7)]



SQL - Language Statements         2014-02-17                     ALTER ROLE(7)