1ALTER ROLE(7) PostgreSQL 15.4 Documentation ALTER ROLE(7)
2
3
4
6 ALTER_ROLE - change a database role
7
9 ALTER ROLE role_specification [ WITH ] option [ ... ]
10
11 where option can be:
12
13 SUPERUSER | NOSUPERUSER
14 | CREATEDB | NOCREATEDB
15 | CREATEROLE | NOCREATEROLE
16 | INHERIT | NOINHERIT
17 | LOGIN | NOLOGIN
18 | REPLICATION | NOREPLICATION
19 | BYPASSRLS | NOBYPASSRLS
20 | CONNECTION LIMIT connlimit
21 | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
22 | VALID UNTIL 'timestamp'
23
24 ALTER ROLE name RENAME TO new_name
25
26 ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
27 ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
28 ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
29 ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
30
31 where role_specification can be:
32
33 role_name
34 | CURRENT_ROLE
35 | CURRENT_USER
36 | SESSION_USER
37
39 ALTER ROLE changes the attributes of a PostgreSQL role.
40
41 The first variant of this command listed in the synopsis can change
42 many of the role attributes that can be specified in CREATE ROLE. (All
43 the possible attributes are covered, except that there are no options
44 for adding or removing memberships; use GRANT and REVOKE for that.)
45 Attributes not mentioned in the command retain their previous settings.
46 Database superusers can change any of these settings for any role.
47 Roles having CREATEROLE privilege can change any of these settings
48 except SUPERUSER, REPLICATION, and BYPASSRLS; but only for
49 non-superuser and non-replication roles. Ordinary roles can only change
50 their own password.
51
52 The second variant changes the name of the role. Database superusers
53 can rename any role. Roles having CREATEROLE privilege can rename
54 non-superuser roles. The current session user cannot be renamed.
55 (Connect as a different user if you need to do that.) Because
56 MD5-encrypted passwords use the role name as cryptographic salt,
57 renaming a role clears its password if the password is MD5-encrypted.
58
59 The remaining variants change a role's session default for a
60 configuration variable, either for all databases or, when the IN
61 DATABASE clause is specified, only for sessions in the named database.
62 If ALL is specified instead of a role name, this changes the setting
63 for all roles. Using ALL with IN DATABASE is effectively the same as
64 using the command ALTER DATABASE ... SET ....
65
66 Whenever the role subsequently starts a new session, the specified
67 value becomes the session default, overriding whatever setting is
68 present in postgresql.conf or has been received from the postgres
69 command line. This only happens at login time; executing SET ROLE or
70 SET SESSION AUTHORIZATION does not cause new configuration values to be
71 set. Settings set for all databases are overridden by database-specific
72 settings attached to a role. Settings for specific databases or
73 specific roles override settings for all roles.
74
75 Superusers can change anyone's session defaults. Roles having
76 CREATEROLE privilege can change defaults for non-superuser roles.
77 Ordinary roles can only set defaults for themselves. Certain
78 configuration variables cannot be set this way, or can only be set if a
79 superuser issues the command. Only superusers can change a setting for
80 all roles in all databases.
81
83 name
84 The name of the role whose attributes are to be altered.
85
86 CURRENT_ROLE
87 CURRENT_USER
88 Alter the current user instead of an explicitly identified role.
89
90 SESSION_USER
91 Alter the current session user instead of an explicitly identified
92 role.
93
94 SUPERUSER
95 NOSUPERUSER
96 CREATEDB
97 NOCREATEDB
98 CREATEROLE
99 NOCREATEROLE
100 INHERIT
101 NOINHERIT
102 LOGIN
103 NOLOGIN
104 REPLICATION
105 NOREPLICATION
106 BYPASSRLS
107 NOBYPASSRLS
108 CONNECTION LIMIT connlimit
109 [ ENCRYPTED ] PASSWORD 'password'
110 PASSWORD NULL
111 VALID UNTIL 'timestamp'
112 These clauses alter attributes originally set by CREATE ROLE. For
113 more information, see the CREATE ROLE reference page.
114
115 new_name
116 The new name of the role.
117
118 database_name
119 The name of the database the configuration variable should be set
120 in.
121
122 configuration_parameter
123 value
124 Set this role's session default for the specified configuration
125 parameter to the given value. If value is DEFAULT or, equivalently,
126 RESET is used, the role-specific variable setting is removed, so
127 the role will inherit the system-wide default setting in new
128 sessions. Use RESET ALL to clear all role-specific settings. SET
129 FROM CURRENT saves the session's current value of the parameter as
130 the role-specific value. If IN DATABASE is specified, the
131 configuration parameter is set or removed for the given role and
132 database only.
133
134 Role-specific variable settings take effect only at login; SET ROLE
135 and SET SESSION AUTHORIZATION do not process role-specific variable
136 settings.
137
138 See SET(7) and Chapter 20 for more information about allowed
139 parameter names and values.
140
142 Use CREATE ROLE to add new roles, and DROP ROLE to remove a role.
143
144 ALTER ROLE cannot change a role's memberships. Use GRANT and REVOKE to
145 do that.
146
147 Caution must be exercised when specifying an unencrypted password with
148 this command. The password will be transmitted to the server in
149 cleartext, and it might also be logged in the client's command history
150 or the server log. psql(1) contains a command \password that can be
151 used to change a role's password without exposing the cleartext
152 password.
153
154 It is also possible to tie a session default to a specific database
155 rather than to a role; see ALTER DATABASE (ALTER_DATABASE(7)). If there
156 is a conflict, database-role-specific settings override role-specific
157 ones, which in turn override database-specific ones.
158
160 Change a role's password:
161
162 ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
163
164 Remove a role's password:
165
166 ALTER ROLE davide WITH PASSWORD NULL;
167
168 Change a password expiration date, specifying that the password should
169 expire at midday on 4th May 2015 using the time zone which is one hour
170 ahead of UTC:
171
172 ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
173
174 Make a password valid forever:
175
176 ALTER ROLE fred VALID UNTIL 'infinity';
177
178 Give a role the ability to manage other roles and create new databases:
179
180 ALTER ROLE miriam CREATEROLE CREATEDB;
181
182 Give a role a non-default setting of the maintenance_work_mem
183 parameter:
184
185 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
186
187 Give a role a non-default, database-specific setting of the
188 client_min_messages parameter:
189
190 ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
191
193 The ALTER ROLE statement is a PostgreSQL extension.
194
196 CREATE ROLE (CREATE_ROLE(7)), DROP ROLE (DROP_ROLE(7)), ALTER DATABASE
197 (ALTER_DATABASE(7)), SET(7)
198
199
200
201PostgreSQL 15.4 2023 ALTER ROLE(7)