On 11/15/12 12:53 PM, Peter Eisentraut wrote:
> We already have the ability to store in pg_db_role_setting configuration
> settings for
>
> specific user, specific database
> specific user, any database
> any user, specific database
>
> The existing infrastructure would also support
>
> any user, any database (= all the time)
>
> All you'd need is to add
>
> ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING);
>
> in postinit.c, and have some SQL command to modify this setting.
Here is a patch for that.
The internals are straightforward. Actually, we might want to refactor
this a bit later, unifying the AlterRoleSet and AlterDatabaseSet parse
nodes and the functions that do the work, because it's really all the same.
The SQL level interface is a bit odd. The existing facilities are
ALTER ROLE / SET
ALTER DATABASE / SET
ALTER ROLE / IN DATABASE / SET
Since the original design somehow considered roles to be superior to
databases in this regard, I added the global setting as ALTER ROLE ALL
SET ..., but that's obviously arbitrary. Most other variants would
probably be much more invasive, though.
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 2fbba53..6fa51ee 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> 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 @@ <title>Description</title>
<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 @@ <title>Description</title>
<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 @@ <title>See Also</title>
<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 3ba877d..5edb59a 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -814,41 +814,46 @@ static void DelRoleMems(const char *rolename, Oid roleid,
{
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 @@ static void DelRoleMems(const char *rolename, Oid roleid,
{
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 76ef11e..fab330d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1018,6 +1018,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 7e21cea..8427006 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1010,6 +1010,7 @@
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 ac5e4f3..0c6ab9b 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -500,6 +500,7 @@ static char *config_enum_get_options(struct config_enum *
record,
/* 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 @@ struct config_generic **
*/
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 0023c00..d497b1f 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 */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
