1ALTER ROLE(7) SQL Commands ALTER ROLE(7)
2
3
4
6 ALTER ROLE - change a database role
7
8
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
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
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
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
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
176 The ALTER ROLE statement is a PostgreSQL extension.
177
179 CREATE ROLE [create_role(7)], DROP ROLE [drop_role(7)], SET [set(7)]
180
181
182
183SQL - Language Statements 2014-02-17 ALTER ROLE(7)