alter_role(7)

1ALTER ROLE(7)                    SQL Commands                    ALTER ROLE(7)
2
3
4

NAME

6       ALTER ROLE - change a database role
7
8

SYNOPSIS

10       ALTER ROLE name [ [ WITH ] option [ ... ] ]
11
12       where option can be:
13
14             SUPERUSER | NOSUPERUSER
15           | CREATEDB | NOCREATEDB
16           | CREATEROLE | NOCREATEROLE
17           | CREATEUSER | NOCREATEUSER
18           | INHERIT | NOINHERIT
19           | LOGIN | NOLOGIN
20           | CONNECTION LIMIT connlimit
21           | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
22           | VALID UNTIL 'timestamp'
23
24       ALTER ROLE name RENAME TO newname
25
26       ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }
27       ALTER ROLE name SET configuration_parameter FROM CURRENT
28       ALTER ROLE name RESET configuration_parameter
29       ALTER ROLE name RESET ALL
30
31

DESCRIPTION

33       ALTER ROLE changes the attributes of a PostgreSQL role.
34
35       The  first  variant  of  this command listed in the synopsis can change
36       many of the role attributes that can be specified in CREATE ROLE  [cre‐
37       ate_role(7)].   (All  the  possible attributes are covered, except that
38       there are no options for adding  or  removing  memberships;  use  GRANT
39       [grant(7)]  and REVOKE [revoke(7)] for that.)  Attributes not mentioned
40       in the command retain their previous settings.  Database superusers can
41       change  any  of  these  settings for any role.  Roles having CREATEROLE
42       privilege can change any of these settings, but only for  non-superuser
43       roles.  Ordinary roles can only change their own password.
44
45       The  second  variant changes the name of the role.  Database superusers
46       can rename any role.  Roles having CREATEROLE privilege can rename non-
47       superuser roles.  The current session user cannot be renamed.  (Connect
48       as a different user if you need to  do  that.)   Because  MD5-encrypted
49       passwords  use  the  role  name  as cryptographic salt, renaming a role
50       clears its password if the password is MD5-encrypted.
51
52       The remaining variants change a role's session default for a  specified
53       configuration  variable.  Whenever  the  role subsequently starts a new
54       session, the specified value becomes the  session  default,  overriding
55       whatever  setting  is  present  in postgresql.conf or has been received
56       from the postgres command line. This only happens  at  login  time,  so
57       configuration  settings associated with a role to which you've SET ROLE
58       [set_role(7)] will be ignored.  Superusers can change anyone's  session
59       defaults.  Roles  having  CREATEROLE  privilege can change defaults for
60       non-superuser roles. Certain variables cannot be set this way,  or  can
61       only be set if a superuser issues the command.
62

PARAMETERS

64       name   The name of the role whose attributes are to be altered.
65
66       SUPERUSER
67
68       NOSUPERUSER
69
70       CREATEDB
71
72       NOCREATEDB
73
74       CREATEROLE
75
76       NOCREATEROLE
77
78       CREATEUSER
79
80       NOCREATEUSER
81
82       INHERIT
83
84       NOINHERIT
85
86       LOGIN
87
88       NOLOGIN
89
90       CONNECTION LIMIT connlimit
91
92       PASSWORD password
93
94       ENCRYPTED
95
96       UNENCRYPTED
97
98       VALID UNTIL 'timestamp'
99              These  clauses  alter  attributes  originally set by CREATE ROLE
100              [create_role(7)]. For more information, see the CREATE ROLE ref‐
101              erence page.
102
103       newname
104              The new name of the role.
105
106       configuration_parameter
107
108       value  Set  this role's session default for the specified configuration
109              parameter to the given value. If value is  DEFAULT  or,  equiva‐
110              lently,  RESET  is  used,  the role-specific variable setting is
111              removed, so the role will inherit the system-wide  default  set‐
112              ting  in  new sessions. Use RESET ALL to clear all role-specific
113              settings.  SET FROM CURRENT saves the session's current value of
114              the parameter as the role-specific value.
115
116              Role-specific  variable  setting  take effect only at login; SET
117              ROLE [set_role(7)] does not process role-specific variable  set‐
118              tings.
119
120              See  SET  [set(7)] and in the documentation for more information
121              about allowed parameter names and values.
122

NOTES

124       Use CREATE ROLE [create_role(7)]  to  add  new  roles,  and  DROP  ROLE
125       [drop_role(7)] to remove a role.
126
127       ALTER  ROLE  cannot  change a role's memberships.  Use GRANT [grant(7)]
128       and REVOKE [revoke(7)] to do that.
129
130       Caution must be exercised when specifying an unencrypted password  with
131       this command. The password will be transmitted to the server in cleart‐
132       ext, and it might also be logged in the client's command history or the
133       server  log.  psql  [psql(1)]  contains a command \password that can be
134       used to safely change a role's password.
135
136       It is also possible to tie a session default  to  a  specific  database
137       rather  than  to a role; see ALTER DATABASE [alter_database(7)].  Role-
138       specific settings override database-specific ones if there  is  a  con‐
139       flict.
140

EXAMPLES

142       Change a role's password:
143
144       ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
145
146
147       Remove a role's password:
148
149       ALTER ROLE davide WITH PASSWORD NULL;
150
151
152       Change  a password expiration date, specifying that the password should
153       expire at midday on 4th May 2015 using the time zone which is one  hour
154       ahead of UTC:
155
156       ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
157
158
159       Make a password valid forever:
160
161       ALTER ROLE fred VALID UNTIL 'infinity';
162
163
164       Give a role the ability to create other roles and new databases:
165
166       ALTER ROLE miriam CREATEROLE CREATEDB;
167
168
169       Give  a  role a non-default setting of the maintenance_work_mem parame‐
170       ter:
171
172       ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
173
174

COMPATIBILITY

176       The ALTER ROLE statement is a PostgreSQL extension.
177