aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/alter_role.sgml27
-rw-r--r--src/backend/commands/user.c88
-rw-r--r--src/backend/parser/gram.y8
-rw-r--r--src/backend/utils/init/postinit.c1
-rw-r--r--src/backend/utils/misc/guc.c3
-rw-r--r--src/include/utils/guc.h1
6 files changed, 88 insertions, 40 deletions
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 2fbba53d25f..b0981fdd5d5 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -39,9 +39,9 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
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 }
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
-ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
+ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
+ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
+ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
</synopsis>
</refsynopsisdiv>
@@ -83,8 +83,15 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
<para>
The remaining variants change a role's session default for a configuration
variable, either for all databases or, when the <literal>IN
- DATABASE</literal> clause is specified, only for sessions in
- the named database. Whenever the role subsequently
+ DATABASE</literal> clause is specified, only for sessions in the named
+ database. If <literal>ALL</literal> is specified instead of a role name,
+ this changes the setting for all roles. Using <literal>ALL</literal>
+ with <literal>IN DATABASE</literal> is effectively the same as using the
+ command <literal>ALTER DATABASE ... SET ...</literal>.
+ </para>
+
+ <para>
+ Whenever the role subsequently
starts a new session, the specified value becomes the session
default, overriding whatever setting is present in
<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
<xref linkend="sql-set-session-authorization"> does not cause new
configuration values to be set.
Settings set for all databases are overridden by database-specific settings
- attached to a role.
+ attached to a role. Settings for specific databases or specific roles override
+ settings for all roles.
+ </para>
+
+ <para>
Superusers can change anyone's session defaults. Roles having
<literal>CREATEROLE</> privilege can change defaults for non-superuser
roles. Ordinary roles can only set defaults for themselves.
Certain configuration variables cannot be set this way, or can only be
- set if a superuser issues the command.
+ set if a superuser issues the command. Only superusers can change a setting
+ for all roles in all databases.
</para>
</refsect1>
@@ -307,6 +319,7 @@ ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
<simplelist type="inline">
<member><xref linkend="sql-createrole"></member>
<member><xref linkend="sql-droprole"></member>
+ <member><xref linkend="sql-alterdatabase"></member>
<member><xref linkend="sql-set"></member>
</simplelist>
</refsect1>
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 3ba877d253a..5edb59af36b 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -814,41 +814,46 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
{
HeapTuple roletuple;
Oid databaseid = InvalidOid;
- Oid roleid;
+ Oid roleid = InvalidOid;
- roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role));
+ if (stmt->role)
+ {
+ roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role));
- if (!HeapTupleIsValid(roletuple))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("role \"%s\" does not exist", stmt->role)));
+ if (!HeapTupleIsValid(roletuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("role \"%s\" does not exist", stmt->role)));
- roleid = HeapTupleGetOid(roletuple);
+ roleid = HeapTupleGetOid(roletuple);
- /*
- * Obtain a lock on the role and make sure it didn't go away in the
- * meantime.
- */
- shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
+ /*
+ * Obtain a lock on the role and make sure it didn't go away in the
+ * meantime.
+ */
+ shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
- /*
- * To mess with a superuser you gotta be superuser; else you need
- * createrole, or just want to change your own settings
- */
- if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
- {
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to alter superusers")));
- }
- else
- {
- if (!have_createrole_privilege() &&
- HeapTupleGetOid(roletuple) != GetUserId())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied")));
+ /*
+ * To mess with a superuser you gotta be superuser; else you need
+ * createrole, or just want to change your own settings
+ */
+ if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter superusers")));
+ }
+ else
+ {
+ if (!have_createrole_privilege() &&
+ HeapTupleGetOid(roletuple) != GetUserId())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied")));
+ }
+
+ ReleaseSysCache(roletuple);
}
/* look up and lock the database, if specified */
@@ -856,10 +861,29 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
{
databaseid = get_database_oid(stmt->database, false);
shdepLockAndCheckObject(DatabaseRelationId, databaseid);
+
+ if (!stmt->role)
+ {
+ /*
+ * If no role is specified, then this is effectively the same as
+ * ALTER DATABASE ... SET, so use the same permission check.
+ */
+ if (!pg_database_ownercheck(databaseid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
+ stmt->database);
+ }
+ }
+
+ if (!stmt->role && !stmt->database)
+ {
+ /* Must be superuser to alter settings globally. */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter settings globally")));
}
- AlterSetting(databaseid, HeapTupleGetOid(roletuple), stmt->setstmt);
- ReleaseSysCache(roletuple);
+ AlterSetting(databaseid, roleid, stmt->setstmt);
return roleid;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index fee05311c5c..b998431f5f3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1020,6 +1020,14 @@ AlterRoleSetStmt:
n->setstmt = $5;
$$ = (Node *)n;
}
+ | ALTER ROLE ALL opt_in_database SetResetClause
+ {
+ AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
+ n->role = NULL;
+ n->database = $4;
+ n->setstmt = $5;
+ $$ = (Node *)n;
+ }
;
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 7e21ceae880..84270061d8a 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1010,6 +1010,7 @@ process_settings(Oid databaseid, Oid roleid)
ApplySetting(databaseid, roleid, relsetting, PGC_S_DATABASE_USER);
ApplySetting(InvalidOid, roleid, relsetting, PGC_S_USER);
ApplySetting(databaseid, InvalidOid, relsetting, PGC_S_DATABASE);
+ ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_GLOBAL);
heap_close(relsetting, AccessShareLock);
}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 6128694200f..5437e0744f3 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -500,6 +500,7 @@ const char *const GucSource_Names[] =
/* PGC_S_ENV_VAR */ "environment variable",
/* PGC_S_FILE */ "configuration file",
/* PGC_S_ARGV */ "command line",
+ /* PGC_S_GLOBAL */ "global",
/* PGC_S_DATABASE */ "database",
/* PGC_S_USER */ "user",
/* PGC_S_DATABASE_USER */ "database user",
@@ -5149,7 +5150,7 @@ set_config_option(const char *name, const char *value,
*/
elevel = IsUnderPostmaster ? DEBUG3 : LOG;
}
- else if (source == PGC_S_DATABASE || source == PGC_S_USER ||
+ else if (source == PGC_S_GLOBAL || source == PGC_S_DATABASE || source == PGC_S_USER ||
source == PGC_S_DATABASE_USER)
elevel = WARNING;
else
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 0023c007e0e..d497b1f6546 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -87,6 +87,7 @@ typedef enum
PGC_S_ENV_VAR, /* postmaster environment variable */
PGC_S_FILE, /* postgresql.conf */
PGC_S_ARGV, /* postmaster command line */
+ PGC_S_GLOBAL, /* global in-database setting */
PGC_S_DATABASE, /* per-database setting */
PGC_S_USER, /* per-user setting */
PGC_S_DATABASE_USER, /* per-user-and-database setting */
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-02 12:13:36 +0000