Skip to content

Commit 9475db3

Browse files
peterepetere
committed
Add ALTER ROLE ALL SET command
This generalizes the existing ALTER ROLE ... SET and ALTER DATABASE ... SET functionality to allow creating settings that apply to all users in all databases. reviewed by Pavel Stehule
1 parent 17f1523 commit 9475db3

File tree

6 files changed

+88
-40
lines changed

6 files changed

+88
-40
lines changed

‎doc/src/sgml/ref/alter_role.sgml

+20-7
Original file line numberDiff line numberDiff line change
@@ -39,9 +39,9 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
3939
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
4040

4141
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
42-
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
43-
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
44-
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
42+
ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
43+
ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
44+
ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
4545
</synopsis>
4646
</refsynopsisdiv>
4747

@@ -83,8 +83,15 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
8383
<para>
8484
The remaining variants change a role's session default for a configuration
8585
variable, either for all databases or, when the <literal>IN
86-
DATABASE</literal> clause is specified, only for sessions in
87-
the named database. Whenever the role subsequently
86+
DATABASE</literal> clause is specified, only for sessions in the named
87+
database. If <literal>ALL</literal> is specified instead of a role name,
88+
this changes the setting for all roles. Using <literal>ALL</literal>
89+
with <literal>IN DATABASE</literal> is effectively the same as using the
90+
command <literal>ALTER DATABASE ... SET ...</literal>.
91+
</para>
92+
93+
<para>
94+
Whenever the role subsequently
8895
starts a new session, the specified value becomes the session
8996
default, overriding whatever setting is present in
9097
<filename>postgresql.conf</> or has been received from the <command>postgres</command>
@@ -93,12 +100,17 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
93100
<xref linkend="sql-set-session-authorization"> does not cause new
94101
configuration values to be set.
95102
Settings set for all databases are overridden by database-specific settings
96-
attached to a role.
103+
attached to a role. Settings for specific databases or specific roles override
104+
settings for all roles.
105+
</para>
106+
107+
<para>
97108
Superusers can change anyone's session defaults. Roles having
98109
<literal>CREATEROLE</> privilege can change defaults for non-superuser
99110
roles. Ordinary roles can only set defaults for themselves.
100111
Certain configuration variables cannot be set this way, or can only be
101-
set if a superuser issues the command.
112+
set if a superuser issues the command. Only superusers can change a setting
113+
for all roles in all databases.
102114
</para>
103115
</refsect1>
104116

@@ -307,6 +319,7 @@ ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
307319
<simplelist type="inline">
308320
<member><xref linkend="sql-createrole"></member>
309321
<member><xref linkend="sql-droprole"></member>
322+
<member><xref linkend="sql-alterdatabase"></member>
310323
<member><xref linkend="sql-set"></member>
311324
</simplelist>
312325
</refsect1>

‎src/backend/commands/user.c

+56-32
Original file line numberDiff line numberDiff line change
@@ -814,52 +814,76 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
814814
{
815815
HeapTuple roletuple;
816816
Oid databaseid = InvalidOid;
817-
Oid roleid;
817+
Oid roleid = InvalidOid;
818818

819-
roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role));
819+
if (stmt->role)
820+
{
821+
roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role));
820822

821-
if (!HeapTupleIsValid(roletuple))
822-
ereport(ERROR,
823-
(errcode(ERRCODE_UNDEFINED_OBJECT),
824-
errmsg("role \"%s\" does not exist", stmt->role)));
823+
if (!HeapTupleIsValid(roletuple))
824+
ereport(ERROR,
825+
(errcode(ERRCODE_UNDEFINED_OBJECT),
826+
errmsg("role \"%s\" does not exist", stmt->role)));
825827

826-
roleid = HeapTupleGetOid(roletuple);
828+
roleid = HeapTupleGetOid(roletuple);
827829

828-
/*
829-
* Obtain a lock on the role and make sure it didn't go away in the
830-
* meantime.
831-
*/
832-
shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
830+
/*
831+
* Obtain a lock on the role and make sure it didn't go away in the
832+
* meantime.
833+
*/
834+
shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
833835

834-
/*
835-
* To mess with a superuser you gotta be superuser; else you need
836-
* createrole, or just want to change your own settings
837-
*/
838-
if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
839-
{
840-
if (!superuser())
841-
ereport(ERROR,
842-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
843-
errmsg("must be superuser to alter superusers")));
844-
}
845-
else
846-
{
847-
if (!have_createrole_privilege() &&
848-
HeapTupleGetOid(roletuple) != GetUserId())
849-
ereport(ERROR,
850-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
851-
errmsg("permission denied")));
836+
/*
837+
* To mess with a superuser you gotta be superuser; else you need
838+
* createrole, or just want to change your own settings
839+
*/
840+
if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
841+
{
842+
if (!superuser())
843+
ereport(ERROR,
844+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
845+
errmsg("must be superuser to alter superusers")));
846+
}
847+
else
848+
{
849+
if (!have_createrole_privilege() &&
850+
HeapTupleGetOid(roletuple) != GetUserId())
851+
ereport(ERROR,
852+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
853+
errmsg("permission denied")));
854+
}
855+
856+
ReleaseSysCache(roletuple);
852857
}
853858

854859
/* look up and lock the database, if specified */
855860
if (stmt->database != NULL)
856861
{
857862
databaseid = get_database_oid(stmt->database, false);
858863
shdepLockAndCheckObject(DatabaseRelationId, databaseid);
864+
865+
if (!stmt->role)
866+
{
867+
/*
868+
* If no role is specified, then this is effectively the same as
869+
* ALTER DATABASE ... SET, so use the same permission check.
870+
*/
871+
if (!pg_database_ownercheck(databaseid, GetUserId()))
872+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
873+
stmt->database);
874+
}
875+
}
876+
877+
if (!stmt->role && !stmt->database)
878+
{
879+
/* Must be superuser to alter settings globally. */
880+
if (!superuser())
881+
ereport(ERROR,
882+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
883+
errmsg("must be superuser to alter settings globally")));
859884
}
860885

861-
AlterSetting(databaseid, HeapTupleGetOid(roletuple), stmt->setstmt);
862-
ReleaseSysCache(roletuple);
886+
AlterSetting(databaseid, roleid, stmt->setstmt);
863887

864888
return roleid;
865889
}

‎src/backend/parser/gram.y

+8
Original file line numberDiff line numberDiff line change
@@ -1020,6 +1020,14 @@ AlterRoleSetStmt:
10201020
n->setstmt = $5;
10211021
$$ = (Node *)n;
10221022
}
1023+
| ALTER ROLE ALL opt_in_database SetResetClause
1024+
{
1025+
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
1026+
n->role = NULL;
1027+
n->database = $4;
1028+
n->setstmt = $5;
1029+
$$ = (Node *)n;
1030+
}
10231031
;
10241032

10251033

‎src/backend/utils/init/postinit.c

+1
Original file line numberDiff line numberDiff line change
@@ -1010,6 +1010,7 @@ process_settings(Oid databaseid, Oid roleid)
10101010
ApplySetting(databaseid, roleid, relsetting, PGC_S_DATABASE_USER);
10111011
ApplySetting(InvalidOid, roleid, relsetting, PGC_S_USER);
10121012
ApplySetting(databaseid, InvalidOid, relsetting, PGC_S_DATABASE);
1013+
ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_GLOBAL);
10131014

10141015
heap_close(relsetting, AccessShareLock);
10151016
}

‎src/backend/utils/misc/guc.c

+2-1
Original file line numberDiff line numberDiff line change
@@ -500,6 +500,7 @@ const char *const GucSource_Names[] =
500500
/* PGC_S_ENV_VAR */ "environment variable",
501501
/* PGC_S_FILE */ "configuration file",
502502
/* PGC_S_ARGV */ "command line",
503+
/* PGC_S_GLOBAL */ "global",
503504
/* PGC_S_DATABASE */ "database",
504505
/* PGC_S_USER */ "user",
505506
/* PGC_S_DATABASE_USER */ "database user",
@@ -5149,7 +5150,7 @@ set_config_option(const char *name, const char *value,
51495150
*/
51505151
elevel = IsUnderPostmaster ? DEBUG3 : LOG;
51515152
}
5152-
else if (source == PGC_S_DATABASE || source == PGC_S_USER ||
5153+
else if (source == PGC_S_GLOBAL || source == PGC_S_DATABASE || source == PGC_S_USER ||
51535154
source == PGC_S_DATABASE_USER)
51545155
elevel = WARNING;
51555156
else

‎src/include/utils/guc.h

+1
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,7 @@ typedef enum
8787
PGC_S_ENV_VAR, /* postmaster environment variable */
8888
PGC_S_FILE, /* postgresql.conf */
8989
PGC_S_ARGV, /* postmaster command line */
90+
PGC_S_GLOBAL, /* global in-database setting */
9091
PGC_S_DATABASE, /* per-database setting */
9192
PGC_S_USER, /* per-user setting */
9293
PGC_S_DATABASE_USER, /* per-user-and-database setting */

0 commit comments

Comments
 (0)