TODO item: Allow more complex user/database default GUC settings
Started by Alvaro Herreraover 16 years ago43 messages
Hi,
There's a longstanding TODO item, in subject. Previous discussion was
here:
http://archives.postgresql.org/pgsql-hackers/2006-09/msg02341.php
In looking what it would take to implement it, I find that it is
trivial. The only part that looks complex is the UI for it. Is anyone
interested in giving this patch a shot?
Implementation-side, it requires a new catalog (pg_settings), with the
following columns:
setdatabase oid
setrole oid
setconfig text[]
datconfig and rolconfig are removed.
ALTER DATABASE / SET sets setdatabase to the OID of the database in
command, and setrole to 0 (InvalidOid); ALTER ROLE / SET sets setrole
and leaves setdatabase 0.
A new command allows one to set a config that applies to both database
and role.
At startup, the settings are applied in the following order:
database=value role=0
database=0 role=value
database=value role=value
This way, current behavior is maintained (ALTER ROLE trumps ALTER
DATABASE).
The only real work in this is figuring out what the grammar for the new
command looks like. Maybe we could have some like
ALTER ROLE foo ALTER DATABASE bar SET config
There are of course many possible variations but this looks the most
reasonable one. Any better ideas?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera wrote:
Implementation-side, it requires a new catalog (pg_settings), with the
following columns:
So, I've come up with the attached patch. It does not have the new
command yet, so you can do ALTER USER and ALTER DATABASE and it works,
but there's no way to set user-and-database-specific settings, short of
INSERT into the catalog. Apart from that it works nicely.
I'm just posting in case somebody has thoughts on the UI part of it.
Other things that need fixed:
- need to figure out locking for roles; this stuff must be synchronized
with role drop
- pg_shadow and pg_roles need a join to obtain settings
- two regression tests need their expected file updated
- catalog version bump
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachments:
setting.patchtext/x-diff; charset=us-asciiDownload
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.206
diff -c -p -r2.206 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 10 Aug 2009 22:13:50 -0000 2.206
--- doc/src/sgml/catalogs.sgml 26 Aug 2009 02:01:11 -0000
***************
*** 199,204 ****
--- 199,209 ----
</row>
<row>
+ <entry><link linkend="catalog-pg-setting"><structname>pg_setting</structname></link></entry>
+ <entry>per-user and per-database settings</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
<entry>dependencies on shared objects</entry>
</row>
***************
*** 2132,2144 ****
</row>
<row>
- <entry><structfield>datconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>datacl</structfield></entry>
<entry><type>aclitem[]</type></entry>
<entry></entry>
--- 2137,2142 ----
***************
*** 3996,4001 ****
--- 3994,4058 ----
</sect1>
+ <sect1 id="catalog-pg-setting">
+ <title><structname>pg_setting</structname></title>
+
+ <indexterm zone="catalog-pg-setting">
+ <primary>pg_setting</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_setting</structname> records the default
+ values that have been set for run-time configuration variables,
+ for each role and database combination.
+ </para>
+
+ <para>
+ Unlike most system catalogs, <structname>pg_setting</structname>
+ is shared across all databases of a cluster: there is only one
+ copy of <structname>pg_setting</structname> per cluster, not
+ one per database.
+ </para>
+
+ <table>
+ <title><structname>pg_setting</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>setdatabase</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
+ <entry>The OID of the database the setting is applicable to, or zero if not database-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setrole</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
+ <entry>The OID of the role the setting is applicable to, or zero if not role-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setconfig</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry></entry>
+ <entry>Defaults for run-time configuration variables</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="catalog-pg-shdepend">
<title><structname>pg_shdepend</structname></title>
***************
*** 6448,6460 ****
</row>
<row>
- <entry><structfield>rolconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>oid</structfield></entry>
<entry><type>oid</type></entry>
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
--- 6505,6510 ----
Index: src/backend/catalog/Makefile
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/catalog/Makefile,v
retrieving revision 1.70
diff -c -p -r1.70 Makefile
*** src/backend/catalog/Makefile 12 May 2009 00:56:05 -0000 1.70
--- src/backend/catalog/Makefile 25 Aug 2009 19:15:03 -0000
*************** include $(top_builddir)/src/Makefile.glo
*** 13,19 ****
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
! pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
--- 13,19 ----
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
! pg_setting.o pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
*************** POSTGRES_BKI_SRCS = $(addprefix $(top_sr
*** 32,38 ****
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
! pg_database.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
--- 32,38 ----
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
! pg_database.h pg_setting.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
Index: src/backend/catalog/catalog.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/catalog/catalog.c,v
retrieving revision 1.83
diff -c -p -r1.83 catalog.c
*** src/backend/catalog/catalog.c 11 Jun 2009 14:48:54 -0000 1.83
--- src/backend/catalog/catalog.c 24 Aug 2009 22:41:36 -0000
***************
*** 31,36 ****
--- 31,37 ----
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_pltemplate.h"
+ #include "catalog/pg_setting.h"
#include "catalog/pg_shdepend.h"
#include "catalog/pg_shdescription.h"
#include "catalog/pg_tablespace.h"
*************** IsSharedRelation(Oid relationId)
*** 306,312 ****
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
! relationId == TableSpaceRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
--- 307,314 ----
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
! relationId == TableSpaceRelationId ||
! relationId == SettingRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
*************** IsSharedRelation(Oid relationId)
*** 320,326 ****
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
! relationId == TablespaceNameIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
--- 322,329 ----
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
! relationId == TablespaceNameIndexId ||
! relationId == SettingDatidRolidIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
*************** IsSharedRelation(Oid relationId)
*** 328,334 ****
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
! relationId == PgShdescriptionToastIndex)
return true;
return false;
}
--- 331,339 ----
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
! relationId == PgShdescriptionToastIndex ||
! relationId == PgSettingToastTable ||
! relationId == PgSettingToastIndex)
return true;
return false;
}
Index: src/backend/catalog/pg_setting.c
===================================================================
RCS file: src/backend/catalog/pg_setting.c
diff -N src/backend/catalog/pg_setting.c
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/backend/catalog/pg_setting.c 25 Aug 2009 21:15:57 -0000
***************
*** 0 ****
--- 1,231 ----
+ /*
+ * pg_setting.c
+ * Routines to support manipulation of the pg_setting relation
+ *
+ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * $PostgreSQL$
+ */
+ #include "postgres.h"
+
+ #include "access/genam.h"
+ #include "access/heapam.h"
+ #include "access/htup.h"
+ #include "access/skey.h"
+ #include "catalog/indexing.h"
+ #include "catalog/pg_setting.h"
+ #include "utils/fmgroids.h"
+ #include "utils/rel.h"
+ #include "utils/tqual.h"
+
+ void
+ AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
+ {
+ char *valuestr;
+ HeapTuple tuple;
+ Relation rel;
+ ScanKeyData scankey[2];
+ SysScanDesc scan;
+
+ valuestr = ExtractSetVariableArgs(setstmt);
+
+ /* Get the old tuple, if any. */
+
+ rel = heap_open(SettingRelationId, RowExclusiveLock);
+ ScanKeyInit(&scankey[0],
+ Anum_pg_setting_setdatabase,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(databaseid));
+ ScanKeyInit(&scankey[1],
+ Anum_pg_setting_setrole,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+ scan = systable_beginscan(rel, SettingDatidRolidIndexId, true,
+ SnapshotNow, 2, scankey);
+ tuple = systable_getnext(scan);
+
+ /*
+ * There are three cases:
+ *
+ * - in RESET ALL, simply delete the pg_setting tuple (if any)
+ *
+ * - in other commands, if there's a tuple in pg_setting, update it;
+ * if it ends up empty, delete it
+ *
+ * - otherwise, insert a new pg_setting tuple, but only if the command is
+ * not RESET
+ */
+ if (setstmt->kind == VAR_RESET_ALL)
+ {
+ if (HeapTupleIsValid(tuple))
+ simple_heap_delete(rel, &tuple->t_self);
+ }
+ else if (HeapTupleIsValid(tuple))
+ {
+ Datum repl_val[Natts_pg_setting];
+ bool repl_null[Natts_pg_setting];
+ bool repl_repl[Natts_pg_setting];
+ HeapTuple newtuple;
+ Datum datum;
+ bool isnull;
+ ArrayType *a;
+
+ memset(repl_repl, false, sizeof(repl_repl));
+ repl_repl[Anum_pg_setting_setconfig - 1] = true;
+ repl_null[Anum_pg_setting_setconfig - 1] = false;
+
+ /* Extract old value of setconfig */
+ datum = heap_getattr(tuple, Anum_pg_setting_setconfig,
+ RelationGetDescr(rel), &isnull);
+ a = isnull ? NULL : DatumGetArrayTypeP(datum);
+
+ /* Update (valuestr is NULL in RESET cases) */
+ if (valuestr)
+ a = GUCArrayAdd(a, setstmt->name, valuestr);
+ else
+ a = GUCArrayDelete(a, setstmt->name);
+
+ if (a)
+ {
+ repl_val[Anum_pg_setting_setconfig - 1] = PointerGetDatum(a);
+
+ newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
+ repl_val, repl_null, repl_repl);
+ simple_heap_update(rel, &tuple->t_self, newtuple);
+
+ /* Update indexes */
+ CatalogUpdateIndexes(rel, newtuple);
+ }
+ else
+ {
+ simple_heap_delete(rel, &tuple->t_self);
+ }
+ }
+ else if (valuestr)
+ {
+ /* non-null valuestr means it's not RESET, so insert a new tuple */
+ HeapTuple newtuple;
+ Datum values[Natts_pg_setting];
+ bool nulls[Natts_pg_setting];
+ ArrayType *a;
+
+ memset(nulls, false, sizeof(nulls));
+
+ a = GUCArrayAdd(NULL, setstmt->name, valuestr);
+
+ values[Anum_pg_setting_setdatabase - 1] = ObjectIdGetDatum(databaseid);
+ values[Anum_pg_setting_setrole - 1] = ObjectIdGetDatum(roleid);
+ values[Anum_pg_setting_setconfig - 1] = PointerGetDatum(a);
+ newtuple = heap_form_tuple(RelationGetDescr(rel), values, nulls);
+
+ simple_heap_insert(rel, newtuple);
+
+ /* Update indexes */
+ CatalogUpdateIndexes(rel, newtuple);
+ }
+
+ systable_endscan(scan);
+
+ /* Close pg_setting, but keep lock till commit */
+ heap_close(rel, NoLock);
+ }
+
+ /*
+ * Drop some settings from the catalog. These can be for a particular
+ * database, or for a particular role. (It is of course possible to do both
+ * too, but it doesn't make sense for current uses.)
+ */
+ void
+ DropSetting(Oid databaseid, Oid roleid)
+ {
+ Relation relsetting;
+ HeapScanDesc scan;
+ ScanKeyData keys[2];
+ HeapTuple tup;
+ int numkeys = 0;
+
+ relsetting = heap_open(SettingRelationId, RowExclusiveLock);
+
+ if (OidIsValid(databaseid))
+ {
+ ScanKeyInit(&keys[numkeys],
+ Anum_pg_setting_setdatabase,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(databaseid));
+ numkeys++;
+ }
+ if (OidIsValid(roleid))
+ {
+ ScanKeyInit(&keys[numkeys],
+ Anum_pg_setting_setrole,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+ numkeys++;
+ }
+
+ scan = heap_beginscan(relsetting, SnapshotNow, numkeys, keys);
+ while (HeapTupleIsValid(tup = heap_getnext(scan, ForwardScanDirection)))
+ {
+ simple_heap_delete(relsetting, &tup->t_self);
+ }
+ heap_endscan(scan);
+
+ heap_close(relsetting, RowExclusiveLock);
+ }
+
+ /*
+ * Scan pg_setting looking for applicable settings, and load them on the
+ * current process.
+ *
+ * relsetting is pg_setting, already opened and locked.
+ *
+ * Note: we only consider setting for the exact databaseid/roleid combination.
+ * This probably needs to be called more than once, with InvalidOid passed as
+ * databaseid/roleid.
+ */
+ void
+ ApplySetting(Oid databaseid, Oid roleid, Relation relsetting, GucSource source)
+ {
+ SysScanDesc scan;
+ ScanKeyData keys[2];
+ HeapTuple tup;
+
+ ScanKeyInit(&keys[0],
+ Anum_pg_setting_setdatabase,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(databaseid));
+ ScanKeyInit(&keys[1],
+ Anum_pg_setting_setrole,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ scan = systable_beginscan(relsetting, SettingDatidRolidIndexId, true,
+ SnapshotNow, 2, keys);
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ bool isnull;
+ Datum datum;
+
+ datum = heap_getattr(tup, Anum_pg_setting_setconfig,
+ RelationGetDescr(relsetting), &isnull);
+ if (!isnull)
+ {
+ ArrayType *a = DatumGetArrayTypeP(datum);
+
+ /*
+ * We process all the options at SUSET level. We assume that the
+ * right to insert an option into pg_settings was checked when it
+ * was inserted.
+ */
+ ProcessGUCArray(a, PGC_SUSET, source, GUC_ACTION_SET);
+ }
+ }
+
+ systable_endscan(scan);
+ }
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.60
diff -c -p -r1.60 system_views.sql
*** src/backend/catalog/system_views.sql 7 Apr 2009 00:31:26 -0000 1.60
--- src/backend/catalog/system_views.sql 24 Aug 2009 22:32:31 -0000
*************** CREATE VIEW pg_roles AS
*** 18,24 ****
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
! rolconfig,
oid
FROM pg_authid;
--- 18,24 ----
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
! NULL as rolconfig,
oid
FROM pg_authid;
*************** CREATE VIEW pg_shadow AS
*** 31,37 ****
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
! rolconfig AS useconfig
FROM pg_authid
WHERE rolcanlogin;
--- 31,37 ----
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
! NULL AS useconfig
FROM pg_authid
WHERE rolcanlogin;
Index: src/backend/commands/dbcommands.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.225
diff -c -p -r1.225 dbcommands.c
*** src/backend/commands/dbcommands.c 11 Jun 2009 14:48:55 -0000 1.225
--- src/backend/commands/dbcommands.c 25 Aug 2009 19:43:40 -0000
***************
*** 33,38 ****
--- 33,39 ----
#include "catalog/indexing.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+ #include "catalog/pg_setting.h"
#include "catalog/pg_tablespace.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
***************
*** 51,57 ****
#include "utils/builtins.h"
#include "utils/flatfiles.h"
#include "utils/fmgroids.h"
- #include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/pg_locale.h"
#include "utils/snapmgr.h"
--- 52,57 ----
*************** createdb(const CreatedbStmt *stmt)
*** 545,556 ****
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
! * We deliberately set datconfig and datacl to defaults (NULL), rather
! * than copying them from the template database. Copying datacl would be
! * a bad idea when the owner is not the same as the template's owner. It's
! * more debatable whether datconfig should be copied.
*/
- new_record_nulls[Anum_pg_database_datconfig - 1] = true;
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
--- 545,554 ----
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
! * We deliberately set datacl to default (NULL), rather
! * than copying it from the template database. Copying it would be
! * a bad idea when the owner is not the same as the template's owner.
*/
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
*************** dropdb(const char *dbname, bool missing_
*** 824,829 ****
--- 822,832 ----
DeleteSharedComments(db_id, DatabaseRelationId);
/*
+ * Remove settings associated with this database
+ */
+ DropSetting(db_id, InvalidOid);
+
+ /*
* Remove shared dependency references for the database.
*/
dropDatabaseDependencies(db_id);
*************** AlterDatabase(AlterDatabaseStmt *stmt, b
*** 1415,1499 ****
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
! char *valuestr;
! HeapTuple tuple,
! newtuple;
! Relation rel;
! ScanKeyData scankey;
! SysScanDesc scan;
! Datum repl_val[Natts_pg_database];
! bool repl_null[Natts_pg_database];
! bool repl_repl[Natts_pg_database];
!
! valuestr = ExtractSetVariableArgs(stmt->setstmt);
! /*
! * Get the old tuple. We don't need a lock on the database per se,
! * because we're not going to do anything that would mess up incoming
! * connections.
! */
! rel = heap_open(DatabaseRelationId, RowExclusiveLock);
! ScanKeyInit(&scankey,
! Anum_pg_database_datname,
! BTEqualStrategyNumber, F_NAMEEQ,
! NameGetDatum(stmt->dbname));
! scan = systable_beginscan(rel, DatabaseNameIndexId, true,
! SnapshotNow, 1, &scankey);
! tuple = systable_getnext(scan);
! if (!HeapTupleIsValid(tuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" does not exist", stmt->dbname)));
! if (!pg_database_ownercheck(HeapTupleGetOid(tuple), GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
stmt->dbname);
! memset(repl_repl, false, sizeof(repl_repl));
! repl_repl[Anum_pg_database_datconfig - 1] = true;
! if (stmt->setstmt->kind == VAR_RESET_ALL)
! {
! /* RESET ALL, so just set datconfig to null */
! repl_null[Anum_pg_database_datconfig - 1] = true;
! repl_val[Anum_pg_database_datconfig - 1] = (Datum) 0;
! }
! else
! {
! Datum datum;
! bool isnull;
! ArrayType *a;
!
! repl_null[Anum_pg_database_datconfig - 1] = false;
!
! /* Extract old value of datconfig */
! datum = heap_getattr(tuple, Anum_pg_database_datconfig,
! RelationGetDescr(rel), &isnull);
! a = isnull ? NULL : DatumGetArrayTypeP(datum);
!
! /* Update (valuestr is NULL in RESET cases) */
! if (valuestr)
! a = GUCArrayAdd(a, stmt->setstmt->name, valuestr);
! else
! a = GUCArrayDelete(a, stmt->setstmt->name);
!
! if (a)
! repl_val[Anum_pg_database_datconfig - 1] = PointerGetDatum(a);
! else
! repl_null[Anum_pg_database_datconfig - 1] = true;
! }
!
! newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
! repl_val, repl_null, repl_repl);
! simple_heap_update(rel, &tuple->t_self, newtuple);
!
! /* Update indexes */
! CatalogUpdateIndexes(rel, newtuple);
!
! systable_endscan(scan);
!
! /* Close pg_database, but keep lock till commit */
! heap_close(rel, NoLock);
/*
* We don't bother updating the flat file since ALTER DATABASE SET doesn't
--- 1418,1439 ----
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
! Oid datid = get_database_oid(stmt->dbname);
! if (!OidIsValid(datid))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" does not exist", stmt->dbname)));
! LockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
!
! if (!pg_database_ownercheck(datid, GetUserId()))
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
stmt->dbname);
! AlterSetting(datid, InvalidOid, stmt->setstmt);
! UnlockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
/*
* We don't bother updating the flat file since ALTER DATABASE SET doesn't
Index: src/backend/commands/user.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/user.c,v
retrieving revision 1.187
diff -c -p -r1.187 user.c
*** src/backend/commands/user.c 11 Jun 2009 14:48:56 -0000 1.187
--- src/backend/commands/user.c 25 Aug 2009 21:16:09 -0000
***************
*** 19,24 ****
--- 19,25 ----
#include "catalog/indexing.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+ #include "catalog/pg_setting.h"
#include "commands/comment.h"
#include "commands/user.h"
#include "libpq/md5.h"
***************
*** 28,34 ****
#include "utils/builtins.h"
#include "utils/flatfiles.h"
#include "utils/fmgroids.h"
- #include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
--- 29,34 ----
*************** CreateRole(CreateRoleStmt *stmt)
*** 342,349 ****
else
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = true;
- new_record_nulls[Anum_pg_authid_rolconfig - 1] = true;
-
tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
/*
--- 342,347 ----
*************** AlterRole(AlterRoleStmt *stmt)
*** 728,757 ****
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
! char *valuestr;
! HeapTuple oldtuple,
! newtuple;
! Relation rel;
! Datum repl_val[Natts_pg_authid];
! bool repl_null[Natts_pg_authid];
! bool repl_repl[Natts_pg_authid];
! valuestr = ExtractSetVariableArgs(stmt->setstmt);
! rel = heap_open(AuthIdRelationId, RowExclusiveLock);
! oldtuple = SearchSysCache(AUTHNAME,
! PointerGetDatum(stmt->role),
! 0, 0, 0);
! if (!HeapTupleIsValid(oldtuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
/*
* 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(oldtuple))->rolsuper)
{
if (!superuser())
ereport(ERROR,
--- 726,749 ----
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
! HeapTuple roletuple;
! roletuple = SearchSysCache(AUTHNAME,
! PointerGetDatum(stmt->role),
! 0, 0, 0);
! if (!HeapTupleIsValid(roletuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
+ /* XXX need some kind of lock here */
+
/*
* 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,
*************** AlterRoleSet(AlterRoleSetStmt *stmt)
*** 761,815 ****
else
{
if (!have_createrole_privilege() &&
! HeapTupleGetOid(oldtuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
! memset(repl_repl, false, sizeof(repl_repl));
! repl_repl[Anum_pg_authid_rolconfig - 1] = true;
! if (stmt->setstmt->kind == VAR_RESET_ALL)
! {
! /* RESET ALL, so just set rolconfig to null */
! repl_null[Anum_pg_authid_rolconfig - 1] = true;
! repl_val[Anum_pg_authid_rolconfig - 1] = (Datum) 0;
! }
! else
! {
! Datum datum;
! bool isnull;
! ArrayType *array;
!
! repl_null[Anum_pg_authid_rolconfig - 1] = false;
!
! /* Extract old value of rolconfig */
! datum = SysCacheGetAttr(AUTHNAME, oldtuple,
! Anum_pg_authid_rolconfig, &isnull);
! array = isnull ? NULL : DatumGetArrayTypeP(datum);
!
! /* Update (valuestr is NULL in RESET cases) */
! if (valuestr)
! array = GUCArrayAdd(array, stmt->setstmt->name, valuestr);
! else
! array = GUCArrayDelete(array, stmt->setstmt->name);
!
! if (array)
! repl_val[Anum_pg_authid_rolconfig - 1] = PointerGetDatum(array);
! else
! repl_null[Anum_pg_authid_rolconfig - 1] = true;
! }
!
! newtuple = heap_modify_tuple(oldtuple, RelationGetDescr(rel),
! repl_val, repl_null, repl_repl);
!
! simple_heap_update(rel, &oldtuple->t_self, newtuple);
! CatalogUpdateIndexes(rel, newtuple);
!
! ReleaseSysCache(oldtuple);
! /* needn't keep lock since we won't be updating the flat file */
! heap_close(rel, RowExclusiveLock);
}
--- 753,767 ----
else
{
if (!have_createrole_privilege() &&
! HeapTupleGetOid(roletuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
! AlterSetting(InvalidOid, HeapTupleGetOid(roletuple), stmt->setstmt);
! ReleaseSysCache(roletuple);
}
*************** DropRole(DropRoleStmt *stmt)
*** 958,963 ****
--- 910,920 ----
DeleteSharedComments(roleid, AuthIdRelationId);
/*
+ * Remove settings for this role.
+ */
+ DropSetting(InvalidOid, roleid);
+
+ /*
* Advance command counter so that later iterations of this loop will
* see the changes already made. This is essential if, for example,
* we are trying to drop both a role and one of its direct members ---
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.176
diff -c -p -r1.176 miscinit.c
*** src/backend/utils/init/miscinit.c 12 Aug 2009 20:53:30 -0000 1.176
--- src/backend/utils/init/miscinit.c 26 Aug 2009 02:15:51 -0000
*************** InitializeSessionUserId(const char *role
*** 392,399 ****
{
HeapTuple roleTup;
Form_pg_authid rform;
- Datum datum;
- bool isnull;
Oid roleid;
/*
--- 392,397 ----
*************** InitializeSessionUserId(const char *role
*** 470,493 ****
AuthenticatedUserIsSuperuser ? "on" : "off",
PGC_INTERNAL, PGC_S_OVERRIDE);
- /*
- * Set up user-specific configuration variables. This is a good place to
- * do it so we don't have to read pg_authid twice during session startup.
- */
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolconfig, &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_authid was checked when it was
- * inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_USER, GUC_ACTION_SET);
- }
-
ReleaseSysCache(roleTup);
}
--- 468,473 ----
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.194
diff -c -p -r1.194 postinit.c
*** src/backend/utils/init/postinit.c 12 Aug 2009 20:53:30 -0000 1.194
--- src/backend/utils/init/postinit.c 25 Aug 2009 19:58:05 -0000
***************
*** 26,31 ****
--- 26,32 ----
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+ #include "catalog/pg_setting.h"
#include "catalog/pg_tablespace.h"
#include "libpq/libpq-be.h"
#include "mb/pg_wchar.h"
*************** static void CheckMyDatabase(const char *
*** 58,64 ****
static void InitCommunication(void);
static void ShutdownPostgres(int code, Datum arg);
static bool ThereIsAtLeastOneRole(void);
!
/*** InitPostgres support ***/
--- 59,65 ----
static void InitCommunication(void);
static void ShutdownPostgres(int code, Datum arg);
static bool ThereIsAtLeastOneRole(void);
! static void process_settings(Oid databaseid, Oid roleid);
/*** InitPostgres support ***/
*************** CheckMyDatabase(const char *name, bool a
*** 277,305 ****
pg_bind_textdomain_codeset(textdomain(NULL));
#endif
- /*
- * Lastly, set up any database-specific configuration variables.
- */
- if (IsUnderPostmaster)
- {
- Datum datum;
- bool isnull;
-
- datum = SysCacheGetAttr(DATABASEOID, tup, Anum_pg_database_datconfig,
- &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_database was checked when it
- * was inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_DATABASE, GUC_ACTION_SET);
- }
- }
-
ReleaseSysCache(tup);
}
--- 278,283 ----
*************** InitPostgres(const char *in_dbname, Oid
*** 659,664 ****
--- 637,645 ----
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
errmsg("connection limit exceeded for non-superusers")));
+ /* Process pg_setting options */
+ process_settings(MyDatabaseId, GetSessionUserId());
+
/*
* Initialize various default states that can't be set up until we've
* selected the active user and gotten the right GUC settings.
*************** InitPostgres(const char *in_dbname, Oid
*** 681,686 ****
--- 662,689 ----
return am_superuser;
}
+ /*
+ * Load GUC settings from pg_setting.
+ *
+ * We try specific settings for the database/role combination, as well as
+ * general for this database and for this user.
+ */
+ static void
+ process_settings(Oid databaseid, Oid roleid)
+ {
+ Relation pg_setting;
+
+ if (!IsUnderPostmaster)
+ return;
+
+ pg_setting = heap_open(SettingRelationId, AccessShareLock);
+
+ ApplySetting(databaseid, roleid, pg_setting, PGC_S_DATABASE_USER);
+ ApplySetting(InvalidOid, roleid, pg_setting, PGC_S_USER);
+ ApplySetting(databaseid, InvalidOid, pg_setting, PGC_S_DATABASE);
+
+ heap_close(pg_setting, AccessShareLock);
+ }
/*
* Backend-shutdown callback. Do cleanup that we want to be sure happens
Index: src/include/catalog/indexing.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/indexing.h,v
retrieving revision 1.108
diff -c -p -r1.108 indexing.h
*** src/include/catalog/indexing.h 11 Jun 2009 14:49:09 -0000 1.108
--- src/include/catalog/indexing.h 24 Aug 2009 22:40:59 -0000
*************** DECLARE_UNIQUE_INDEX(pg_user_mapping_oid
*** 267,272 ****
--- 267,275 ----
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
+ DECLARE_UNIQUE_INDEX(pg_setting_databaseid_rol_index, 2965, on pg_setting using btree(setdatabase oid_ops, setrole oid_ops));
+ #define SettingDatidRolidIndexId 2965
+
/* last step of initialization script: build the indexes declared above */
BUILD_INDICES
Index: src/include/catalog/pg_attribute.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.152
diff -c -p -r1.152 pg_attribute.h
*** src/include/catalog/pg_attribute.h 12 Aug 2009 20:53:30 -0000 1.152
--- src/include/catalog/pg_attribute.h 24 Aug 2009 21:47:31 -0000
*************** DATA(insert ( 1259 tableoid 26 0 0 4
*** 485,492 ****
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
! { 1262, {"datconfig"}, 1009, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \
! { 1262, {"datacl"}, 1034, -1, 0, -1, 13, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
--- 485,491 ----
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
! { 1262, {"datacl"}, 1034, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
Index: src/include/catalog/pg_authid.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/pg_authid.h,v
retrieving revision 1.9
diff -c -p -r1.9 pg_authid.h
*** src/include/catalog/pg_authid.h 1 Jan 2009 17:23:56 -0000 1.9
--- src/include/catalog/pg_authid.h 24 Aug 2009 21:39:19 -0000
*************** CATALOG(pg_authid,1260) BKI_SHARED_RELAT
*** 55,61 ****
/* remaining fields may be null; use heap_getattr to read them! */
text rolpassword; /* password, if any */
timestamptz rolvaliduntil; /* password expiration time, if any */
- text rolconfig[1]; /* GUC settings to apply at login */
} FormData_pg_authid;
#undef timestamptz
--- 55,60 ----
*************** typedef FormData_pg_authid *Form_pg_auth
*** 83,89 ****
#define Anum_pg_authid_rolconnlimit 8
#define Anum_pg_authid_rolpassword 9
#define Anum_pg_authid_rolvaliduntil 10
- #define Anum_pg_authid_rolconfig 11
/* ----------------
* initial contents of pg_authid
--- 82,87 ----
*************** typedef FormData_pg_authid *Form_pg_auth
*** 92,98 ****
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 90,96 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
Index: src/include/catalog/pg_database.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/pg_database.h,v
retrieving revision 1.49
diff -c -p -r1.49 pg_database.h
*** src/include/catalog/pg_database.h 1 Jan 2009 17:23:57 -0000 1.49
--- src/include/catalog/pg_database.h 24 Aug 2009 22:35:04 -0000
*************** CATALOG(pg_database,1262) BKI_SHARED_REL
*** 41,47 ****
Oid datlastsysoid; /* highest OID to consider a system OID */
TransactionId datfrozenxid; /* all Xids < this are frozen in this DB */
Oid dattablespace; /* default table space for this DB */
- text datconfig[1]; /* database-specific GUC (VAR LENGTH) */
aclitem datacl[1]; /* access permissions (VAR LENGTH) */
} FormData_pg_database;
--- 41,46 ----
*************** typedef FormData_pg_database *Form_pg_da
*** 56,62 ****
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 13
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
--- 55,61 ----
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 12
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
*************** typedef FormData_pg_database *Form_pg_da
*** 68,77 ****
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
! #define Anum_pg_database_datconfig 12
! #define Anum_pg_database_datacl 13
! DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_ _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
--- 67,75 ----
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
! #define Anum_pg_database_datacl 12
! DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
Index: src/include/catalog/pg_setting.h
===================================================================
RCS file: src/include/catalog/pg_setting.h
diff -N src/include/catalog/pg_setting.h
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/include/catalog/pg_setting.h 25 Aug 2009 19:59:31 -0000
***************
*** 0 ****
--- 1,67 ----
+ /*-------------------------------------------------------------------------
+ *
+ * pg_setting.h
+ * definition of configuration settings
+ *
+ *
+ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * $PostgreSQL: pgsql/src/include/catalog/pg_ts_config.h,v 1.5 2009-01-01 17:23:58 momjian Exp $
+ *
+ * NOTES
+ * the genbki.sh script reads this file and generates .bki
+ * information from the DATA() statements.
+ *
+ * XXX do NOT break up DATA() statements into multiple lines!
+ * the scripts are not as smart as you might think...
+ *
+ *-------------------------------------------------------------------------
+ */
+ #ifndef PG_SETTING_H
+ #define PG_SETTING_H
+
+ #include "catalog/genbki.h"
+ #include "nodes/parsenodes.h"
+ #include "utils/guc.h"
+ #include "utils/relcache.h"
+
+ /* ----------------
+ * pg_setting definition. cpp turns this into
+ * typedef struct FormData_pg_setting
+ * ----------------
+ */
+ #define SettingRelationId 2964
+
+ CATALOG(pg_setting,2964) BKI_SHARED_RELATION BKI_WITHOUT_OIDS
+ {
+ Oid setdatabase; /* database */
+ Oid setrole; /* role */
+ text setconfig[1]; /* GUC settings to apply at login */
+ } FormData_pg_setting;
+
+ typedef FormData_pg_setting *Form_pg_setting;
+
+ /* ----------------
+ * compiler constants for pg_setting
+ * ----------------
+ */
+ #define Natts_pg_setting 3
+ #define Anum_pg_setting_setdatabase 1
+ #define Anum_pg_setting_setrole 2
+ #define Anum_pg_setting_setconfig 3
+
+ /* ----------------
+ * initial contents of pg_setting are NOTHING
+ * ----------------
+ */
+
+ /*
+ * prototypes for functions in pg_setting.h
+ */
+ extern void AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt);
+ extern void DropSetting(Oid databaseid, Oid roleid);
+ extern void ApplySetting(Oid databaseid, Oid roleid, Relation pg_setting,
+ GucSource source);
+
+ #endif /* PG_SETTING_H */
Index: src/include/catalog/toasting.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/toasting.h,v
retrieving revision 1.8
diff -c -p -r1.8 toasting.h
*** src/include/catalog/toasting.h 11 Jun 2009 20:46:11 -0000 1.8
--- src/include/catalog/toasting.h 24 Aug 2009 22:40:36 -0000
*************** DECLARE_TOAST(pg_database, 2844, 2845);
*** 58,62 ****
--- 58,65 ----
DECLARE_TOAST(pg_shdescription, 2846, 2847);
#define PgShdescriptionToastTable 2846
#define PgShdescriptionToastIndex 2847
+ DECLARE_TOAST(pg_setting, 2966, 2967);
+ #define PgSettingToastTable 2966
+ #define PgSettingToastIndex 2967
#endif /* TOASTING_H */
Index: src/include/utils/guc.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/utils/guc.h,v
retrieving revision 1.102
diff -c -p -r1.102 guc.h
*** src/include/utils/guc.h 11 Jun 2009 14:49:13 -0000 1.102
--- src/include/utils/guc.h 24 Aug 2009 21:11:00 -0000
*************** typedef enum
*** 86,91 ****
--- 86,92 ----
PGC_S_ARGV, /* postmaster command line */
PGC_S_DATABASE, /* per-database setting */
PGC_S_USER, /* per-user setting */
+ PGC_S_DATABASE_USER, /* per-user-and-database setting */
PGC_S_CLIENT, /* from client connection request */
PGC_S_OVERRIDE, /* special case to forcibly set default */
PGC_S_INTERACTIVE, /* dividing line for error reporting */
Re: TODO item: Allow more complex user/database default GUC settings
--On 25. August 2009 22:17:38 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:
I'm just posting in case somebody has thoughts on the UI part of it.
Other things that need fixed:
- need to figure out locking for roles; this stuff must be synchronized
with role drop
- pg_shadow and pg_roles need a join to obtain settings
- two regression tests need their expected file updated
- catalog version bump
Here's a first shot on this for my current review round. Patch needed to
re-merged into current CVS HEAD due to some merge conflicts, i've also
adjusted the regression tests (minor). On a first look, i like the patch
(especially the code for the utility commands accessing the settings is
better modularized now, which looks much nicer).
--
Thanks
Bernd
Attachments:
complex_guc_review_v1.patchapplication/octet-stream; name=complex_guc_review_v1.patchDownload
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a69cef4..7c17ba2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -199,6 +199,11 @@
</row>
<row>
+ <entry><link linkend="catalog-pg-setting"><structname>pg_setting</structname></link></entry>
+ <entry>per-user and per-database settings</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
<entry>dependencies on shared objects</entry>
</row>
@@ -2132,13 +2137,6 @@
</row>
<row>
- <entry><structfield>datconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>datacl</structfield></entry>
<entry><type>aclitem[]</type></entry>
<entry></entry>
@@ -3996,6 +3994,65 @@
</sect1>
+ <sect1 id="catalog-pg-setting">
+ <title><structname>pg_setting</structname></title>
+
+ <indexterm zone="catalog-pg-setting">
+ <primary>pg_setting</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_setting</structname> records the default
+ values that have been set for run-time configuration variables,
+ for each role and database combination.
+ </para>
+
+ <para>
+ Unlike most system catalogs, <structname>pg_setting</structname>
+ is shared across all databases of a cluster: there is only one
+ copy of <structname>pg_setting</structname> per cluster, not
+ one per database.
+ </para>
+
+ <table>
+ <title><structname>pg_setting</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>setdatabase</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
+ <entry>The OID of the database the setting is applicable to, or zero if not database-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setrole</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
+ <entry>The OID of the role the setting is applicable to, or zero if not role-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setconfig</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry></entry>
+ <entry>Defaults for run-time configuration variables</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="catalog-pg-shdepend">
<title><structname>pg_shdepend</structname></title>
@@ -6448,13 +6505,6 @@
</row>
<row>
- <entry><structfield>rolconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>oid</structfield></entry>
<entry><type>oid</type></entry>
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 861cb1d..941f7db 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -13,7 +13,7 @@ include $(top_builddir)/src/Makefile.global
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
- pg_shdepend.o pg_type.o storage.o toasting.o
+ pg_setting.o pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
@@ -32,7 +32,7 @@ POSTGRES_BKI_SRCS = $(addprefix $(top_srcdir)/src/include/catalog/,\
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
- pg_database.h pg_tablespace.h pg_pltemplate.h \
+ pg_database.h pg_setting.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index d168694..d6f4ea7 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -31,6 +31,7 @@
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_pltemplate.h"
+#include "catalog/pg_setting.h"
#include "catalog/pg_shdepend.h"
#include "catalog/pg_shdescription.h"
#include "catalog/pg_tablespace.h"
@@ -306,7 +307,8 @@ IsSharedRelation(Oid relationId)
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
- relationId == TableSpaceRelationId)
+ relationId == TableSpaceRelationId ||
+ relationId == SettingRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
@@ -320,7 +322,8 @@ IsSharedRelation(Oid relationId)
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
- relationId == TablespaceNameIndexId)
+ relationId == TablespaceNameIndexId ||
+ relationId == SettingDatidRolidIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
@@ -328,7 +331,9 @@ IsSharedRelation(Oid relationId)
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
- relationId == PgShdescriptionToastIndex)
+ relationId == PgShdescriptionToastIndex ||
+ relationId == PgSettingToastTable ||
+ relationId == PgSettingToastIndex)
return true;
return false;
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 67188de..15e1a42 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -18,7 +18,7 @@ CREATE VIEW pg_roles AS
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
- rolconfig,
+ NULL as rolconfig,
oid
FROM pg_authid;
@@ -31,7 +31,7 @@ CREATE VIEW pg_shadow AS
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
- rolconfig AS useconfig
+ NULL AS useconfig
FROM pg_authid
WHERE rolcanlogin;
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 7d4ca41..405c1b2 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -33,6 +33,7 @@
#include "catalog/indexing.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+#include "catalog/pg_setting.h"
#include "catalog/pg_tablespace.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
@@ -50,7 +51,6 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
-#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/pg_locale.h"
#include "utils/snapmgr.h"
@@ -544,12 +544,10 @@ createdb(const CreatedbStmt *stmt)
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
- * We deliberately set datconfig and datacl to defaults (NULL), rather
- * than copying them from the template database. Copying datacl would be
- * a bad idea when the owner is not the same as the template's owner. It's
- * more debatable whether datconfig should be copied.
+ * We deliberately set datacl to default (NULL), rather
+ * than copying it from the template database. Copying it would be
+ * a bad idea when the owner is not the same as the template's owner.
*/
- new_record_nulls[Anum_pg_database_datconfig - 1] = true;
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
@@ -821,6 +819,11 @@ dropdb(const char *dbname, bool missing_ok)
DeleteSharedComments(db_id, DatabaseRelationId);
/*
+ * Remove settings associated with this database
+ */
+ DropSetting(db_id, InvalidOid);
+
+ /*
* Remove shared dependency references for the database.
*/
dropDatabaseDependencies(db_id);
@@ -1397,85 +1400,22 @@ AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
- char *valuestr;
- HeapTuple tuple,
- newtuple;
- Relation rel;
- ScanKeyData scankey;
- SysScanDesc scan;
- Datum repl_val[Natts_pg_database];
- bool repl_null[Natts_pg_database];
- bool repl_repl[Natts_pg_database];
-
- valuestr = ExtractSetVariableArgs(stmt->setstmt);
-
- /*
- * Get the old tuple. We don't need a lock on the database per se,
- * because we're not going to do anything that would mess up incoming
- * connections.
- */
- rel = heap_open(DatabaseRelationId, RowExclusiveLock);
- ScanKeyInit(&scankey,
- Anum_pg_database_datname,
- BTEqualStrategyNumber, F_NAMEEQ,
- NameGetDatum(stmt->dbname));
- scan = systable_beginscan(rel, DatabaseNameIndexId, true,
- SnapshotNow, 1, &scankey);
- tuple = systable_getnext(scan);
- if (!HeapTupleIsValid(tuple))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_DATABASE),
- errmsg("database \"%s\" does not exist", stmt->dbname)));
-
- if (!pg_database_ownercheck(HeapTupleGetOid(tuple), GetUserId()))
- aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
- stmt->dbname);
-
- memset(repl_repl, false, sizeof(repl_repl));
- repl_repl[Anum_pg_database_datconfig - 1] = true;
-
- if (stmt->setstmt->kind == VAR_RESET_ALL)
- {
- /* RESET ALL, so just set datconfig to null */
- repl_null[Anum_pg_database_datconfig - 1] = true;
- repl_val[Anum_pg_database_datconfig - 1] = (Datum) 0;
- }
- else
- {
- Datum datum;
- bool isnull;
- ArrayType *a;
-
- repl_null[Anum_pg_database_datconfig - 1] = false;
-
- /* Extract old value of datconfig */
- datum = heap_getattr(tuple, Anum_pg_database_datconfig,
- RelationGetDescr(rel), &isnull);
- a = isnull ? NULL : DatumGetArrayTypeP(datum);
-
- /* Update (valuestr is NULL in RESET cases) */
- if (valuestr)
- a = GUCArrayAdd(a, stmt->setstmt->name, valuestr);
- else
- a = GUCArrayDelete(a, stmt->setstmt->name);
-
- if (a)
- repl_val[Anum_pg_database_datconfig - 1] = PointerGetDatum(a);
- else
- repl_null[Anum_pg_database_datconfig - 1] = true;
- }
-
- newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
- repl_val, repl_null, repl_repl);
- simple_heap_update(rel, &tuple->t_self, newtuple);
-
- /* Update indexes */
- CatalogUpdateIndexes(rel, newtuple);
-
- systable_endscan(scan);
-
- /* Close pg_database, but keep lock till commit */
- heap_close(rel, NoLock);
+ Oid datid = get_database_oid(stmt->dbname);
+
+ if (!OidIsValid(datid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_DATABASE),
+ errmsg("database \"%s\" does not exist", stmt->dbname)));
+
+ LockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
+
+ if (!pg_database_ownercheck(datid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
+ stmt->dbname);
+
+ AlterSetting(datid, InvalidOid, stmt->setstmt);
+
+ UnlockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
}
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index fa260c4..3f2b520 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -19,6 +19,7 @@
#include "catalog/indexing.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_setting.h"
#include "commands/comment.h"
#include "commands/user.h"
#include "libpq/md5.h"
@@ -27,7 +28,6 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
-#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
@@ -341,8 +341,6 @@ CreateRole(CreateRoleStmt *stmt)
else
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = true;
- new_record_nulls[Anum_pg_authid_rolconfig - 1] = true;
-
tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
/*
@@ -715,30 +713,24 @@ AlterRole(AlterRoleStmt *stmt)
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
- char *valuestr;
- HeapTuple oldtuple,
- newtuple;
- Relation rel;
- Datum repl_val[Natts_pg_authid];
- bool repl_null[Natts_pg_authid];
- bool repl_repl[Natts_pg_authid];
+ HeapTuple roletuple;
- valuestr = ExtractSetVariableArgs(stmt->setstmt);
+ roletuple = SearchSysCache(AUTHNAME,
+ PointerGetDatum(stmt->role),
+ 0, 0, 0);
- rel = heap_open(AuthIdRelationId, RowExclusiveLock);
- oldtuple = SearchSysCache(AUTHNAME,
- PointerGetDatum(stmt->role),
- 0, 0, 0);
- if (!HeapTupleIsValid(oldtuple))
+ if (!HeapTupleIsValid(roletuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
+ /* XXX need some kind of lock here */
+
/*
* 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(oldtuple))->rolsuper)
+ if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
{
if (!superuser())
ereport(ERROR,
@@ -748,54 +740,14 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
else
{
if (!have_createrole_privilege() &&
- HeapTupleGetOid(oldtuple) != GetUserId())
+ HeapTupleGetOid(roletuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
- memset(repl_repl, false, sizeof(repl_repl));
- repl_repl[Anum_pg_authid_rolconfig - 1] = true;
-
- if (stmt->setstmt->kind == VAR_RESET_ALL)
- {
- /* RESET ALL, so just set rolconfig to null */
- repl_null[Anum_pg_authid_rolconfig - 1] = true;
- repl_val[Anum_pg_authid_rolconfig - 1] = (Datum) 0;
- }
- else
- {
- Datum datum;
- bool isnull;
- ArrayType *array;
-
- repl_null[Anum_pg_authid_rolconfig - 1] = false;
-
- /* Extract old value of rolconfig */
- datum = SysCacheGetAttr(AUTHNAME, oldtuple,
- Anum_pg_authid_rolconfig, &isnull);
- array = isnull ? NULL : DatumGetArrayTypeP(datum);
-
- /* Update (valuestr is NULL in RESET cases) */
- if (valuestr)
- array = GUCArrayAdd(array, stmt->setstmt->name, valuestr);
- else
- array = GUCArrayDelete(array, stmt->setstmt->name);
-
- if (array)
- repl_val[Anum_pg_authid_rolconfig - 1] = PointerGetDatum(array);
- else
- repl_null[Anum_pg_authid_rolconfig - 1] = true;
- }
-
- newtuple = heap_modify_tuple(oldtuple, RelationGetDescr(rel),
- repl_val, repl_null, repl_repl);
-
- simple_heap_update(rel, &oldtuple->t_self, newtuple);
- CatalogUpdateIndexes(rel, newtuple);
-
- ReleaseSysCache(oldtuple);
- heap_close(rel, RowExclusiveLock);
+ AlterSetting(InvalidOid, HeapTupleGetOid(roletuple), stmt->setstmt);
+ ReleaseSysCache(roletuple);
}
@@ -944,6 +896,11 @@ DropRole(DropRoleStmt *stmt)
DeleteSharedComments(roleid, AuthIdRelationId);
/*
+ * Remove settings for this role.
+ */
+ DropSetting(InvalidOid, roleid);
+
+ /*
* Advance command counter so that later iterations of this loop will
* see the changes already made. This is essential if, for example,
* we are trying to drop both a role and one of its direct members ---
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index b76be53..e6795f4 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -392,8 +392,6 @@ InitializeSessionUserId(const char *rolename)
{
HeapTuple roleTup;
Form_pg_authid rform;
- Datum datum;
- bool isnull;
Oid roleid;
/*
@@ -470,24 +468,6 @@ InitializeSessionUserId(const char *rolename)
AuthenticatedUserIsSuperuser ? "on" : "off",
PGC_INTERNAL, PGC_S_OVERRIDE);
- /*
- * Set up user-specific configuration variables. This is a good place to
- * do it so we don't have to read pg_authid twice during session startup.
- */
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolconfig, &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_authid was checked when it was
- * inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_USER, GUC_ACTION_SET);
- }
-
ReleaseSysCache(roleTup);
}
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index ab7bd6d..8514db2 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -27,6 +27,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+#include "catalog/pg_setting.h"
#include "catalog/pg_tablespace.h"
#include "libpq/auth.h"
#include "libpq/libpq-be.h"
@@ -63,7 +64,7 @@ static void CheckMyDatabase(const char *name, bool am_superuser);
static void InitCommunication(void);
static void ShutdownPostgres(int code, Datum arg);
static bool ThereIsAtLeastOneRole(void);
-
+static void process_settings(Oid databaseid, Oid roleid);
/*** InitPostgres support ***/
@@ -344,29 +345,6 @@ CheckMyDatabase(const char *name, bool am_superuser)
pg_bind_textdomain_codeset(textdomain(NULL));
#endif
- /*
- * Lastly, set up any database-specific configuration variables.
- */
- if (IsUnderPostmaster)
- {
- Datum datum;
- bool isnull;
-
- datum = SysCacheGetAttr(DATABASEOID, tup, Anum_pg_database_datconfig,
- &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_database was checked when it
- * was inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_DATABASE, GUC_ACTION_SET);
- }
- }
-
ReleaseSysCache(tup);
}
@@ -739,6 +717,9 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
/* set up ACL framework (so CheckMyDatabase can check permissions) */
initialize_acl();
+ /* Process pg_setting options */
+ process_settings(MyDatabaseId, GetSessionUserId());
+
/*
* Re-read the pg_database row for our database, check permissions and
* set up database-specific GUC settings. We can't do this until all the
@@ -851,6 +832,28 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
CommitTransactionCommand();
}
+/*
+ * Load GUC settings from pg_setting.
+ *
+ * We try specific settings for the database/role combination, as well as
+ * general for this database and for this user.
+ */
+static void
+process_settings(Oid databaseid, Oid roleid)
+{
+ Relation pg_setting;
+
+ if (!IsUnderPostmaster)
+ return;
+
+ pg_setting = heap_open(SettingRelationId, AccessShareLock);
+
+ ApplySetting(databaseid, roleid, pg_setting, PGC_S_DATABASE_USER);
+ ApplySetting(InvalidOid, roleid, pg_setting, PGC_S_USER);
+ ApplySetting(databaseid, InvalidOid, pg_setting, PGC_S_DATABASE);
+
+ heap_close(pg_setting, AccessShareLock);
+}
/*
* Backend-shutdown callback. Do cleanup that we want to be sure happens
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 81e18a1..6d5fef2 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -267,6 +267,9 @@ DECLARE_UNIQUE_INDEX(pg_user_mapping_oid_index, 174, on pg_user_mapping using bt
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
+DECLARE_UNIQUE_INDEX(pg_setting_databaseid_rol_index, 2965, on pg_setting using btree(setdatabase oid_ops, setrole oid_ops));
+#define SettingDatidRolidIndexId 2965
+
/* last step of initialization script: build the indexes declared above */
BUILD_INDICES
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 411f63f..e1e1ec6 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -485,8 +485,7 @@ DATA(insert ( 1259 tableoid 26 0 0 4 -7 0 -1 -1 t p i t f f t 0 _null_));
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
-{ 1262, {"datconfig"}, 1009, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \
-{ 1262, {"datacl"}, 1034, -1, 0, -1, 13, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
+{ 1262, {"datacl"}, 1034, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 74c4e89..ad24bba 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -55,7 +55,6 @@ CATALOG(pg_authid,1260) BKI_SHARED_RELATION
/* remaining fields may be null; use heap_getattr to read them! */
text rolpassword; /* password, if any */
timestamptz rolvaliduntil; /* password expiration time, if any */
- text rolconfig[1]; /* GUC settings to apply at login */
} FormData_pg_authid;
#undef timestamptz
@@ -83,7 +82,6 @@ typedef FormData_pg_authid *Form_pg_authid;
#define Anum_pg_authid_rolconnlimit 8
#define Anum_pg_authid_rolpassword 9
#define Anum_pg_authid_rolvaliduntil 10
-#define Anum_pg_authid_rolconfig 11
/* ----------------
* initial contents of pg_authid
@@ -92,7 +90,7 @@ typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
-DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));
+DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h
index 3a0516f..2a614fa 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -41,7 +41,6 @@ CATALOG(pg_database,1262) BKI_SHARED_RELATION
Oid datlastsysoid; /* highest OID to consider a system OID */
TransactionId datfrozenxid; /* all Xids < this are frozen in this DB */
Oid dattablespace; /* default table space for this DB */
- text datconfig[1]; /* database-specific GUC (VAR LENGTH) */
aclitem datacl[1]; /* access permissions (VAR LENGTH) */
} FormData_pg_database;
@@ -56,7 +55,7 @@ typedef FormData_pg_database *Form_pg_database;
* compiler constants for pg_database
* ----------------
*/
-#define Natts_pg_database 13
+#define Natts_pg_database 12
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
@@ -68,10 +67,9 @@ typedef FormData_pg_database *Form_pg_database;
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
-#define Anum_pg_database_datconfig 12
-#define Anum_pg_database_datacl 13
+#define Anum_pg_database_datacl 12
-DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_ _null_));
+DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.h
index bd6e0cf..7a67aa1 100644
--- a/src/include/catalog/toasting.h
+++ b/src/include/catalog/toasting.h
@@ -58,5 +58,8 @@ DECLARE_TOAST(pg_database, 2844, 2845);
DECLARE_TOAST(pg_shdescription, 2846, 2847);
#define PgShdescriptionToastTable 2846
#define PgShdescriptionToastIndex 2847
+DECLARE_TOAST(pg_setting, 2966, 2967);
+#define PgSettingToastTable 2966
+#define PgSettingToastIndex 2967
#endif /* TOASTING_H */
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 16055f5..f731df6 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -86,6 +86,7 @@ typedef enum
PGC_S_ARGV, /* postmaster command line */
PGC_S_DATABASE, /* per-database setting */
PGC_S_USER, /* per-user setting */
+ PGC_S_DATABASE_USER, /* per-user-and-database setting */
PGC_S_CLIENT, /* from client connection request */
PGC_S_OVERRIDE, /* special case to forcibly set default */
PGC_S_INTERACTIVE, /* dividing line for error reporting */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2667a13..c773b81 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1285,10 +1285,10 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
- pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
+ pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, NULL::unknown AS rolconfig, pg_authid.oid FROM pg_authid;
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
- pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin;
+ pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, NULL::unknown AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
@@ -1424,10 +1424,10 @@ insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1"
-DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
+DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey"
-DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".
+DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
where (exists (select 1 from rule_and_refint_t3
where (((rule_and_refint_t3.id3a = new.id3a)
@@ -1439,10 +1439,10 @@ create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
and (rule_and_refint_t3.id3c = new.id3c));
insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1"
-DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
+DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey"
-DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".
+DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
--
-- check for planner problems with complex inherited UPDATES
--
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 7213192..3e6e134 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -112,6 +112,7 @@ SELECT relname, relhasindex
pg_pltemplate | t
pg_proc | t
pg_rewrite | t
+ pg_setting | t
pg_shdepend | t
pg_shdescription | t
pg_statistic | t
@@ -151,7 +152,7 @@ SELECT relname, relhasindex
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
-(140 rows)
+(141 rows)
--
-- another sanity check: every system catalog that has OIDs should have
Re: TODO item: Allow more complex user/database default GUC settings
On Fri, Sep 18, 2009 at 4:03 PM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 25. August 2009 22:17:38 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:I'm just posting in case somebody has thoughts on the UI part of it.
Other things that need fixed:
- need to figure out locking for roles; this stuff must be synchronized
with role drop
- pg_shadow and pg_roles need a join to obtain settings
- two regression tests need their expected file updated
- catalog version bumpHere's a first shot on this for my current review round. Patch needed to
re-merged into current CVS HEAD due to some merge conflicts, i've also
adjusted the regression tests (minor). On a first look, i like the patch
(especially the code for the utility commands accessing the settings is
better modularized now, which looks much nicer).
So is this ready to commit, or what?
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
Robert Haas escribi�:
Here's a first shot on this for my current review round. Patch needed to
re-merged into current CVS HEAD due to some merge conflicts, i've also
adjusted the regression tests (minor). On a first look, i like the patch
(especially the code for the utility commands accessing the settings is
better modularized now, which looks much nicer).So is this ready to commit, or what?
Not really :-( It needs some minor tweaks to qualify as a cleanup
patch, and further extra coding for there to be an actual new feature.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
--On 20. September 2009 22:56:53 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:
So is this ready to commit, or what?
Not yet, see the comments Alvaro did upthread. Please note that i'm still
reviewing this one and i hope to post results tomorrow (there wasn't plenty
of free time over the weekend, i'm sorry).
--
Thanks
Bernd
Re: TODO item: Allow more complex user/database default GUC settings
On Mon, Sep 21, 2009 at 12:21 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Robert Haas escribió:
Here's a first shot on this for my current review round. Patch needed to
re-merged into current CVS HEAD due to some merge conflicts, i've also
adjusted the regression tests (minor). On a first look, i like the patch
(especially the code for the utility commands accessing the settings is
better modularized now, which looks much nicer).So is this ready to commit, or what?
Not really :-( It needs some minor tweaks to qualify as a cleanup
patch, and further extra coding for there to be an actual new feature.
So here's the followup question - do you intend to do one of those
things for this CommitFest, or should we mark this as Returned with
Feedback once Bernd posts the rest of his review?
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
Robert Haas escribi�:
So here's the followup question - do you intend to do one of those
things for this CommitFest, or should we mark this as Returned with
Feedback once Bernd posts the rest of his review?
What feedback is it supposed to be returned with? Precisely what I
wanted is some feedback on the general idea. Brendan's "I like the
approach" is good, but is it enough to deter a later veto from someone
else?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera escribi�:
What feedback is it supposed to be returned with? Precisely what I
wanted is some feedback on the general idea. Brendan's "I like the
approach" is good, but is it enough to deter a later veto from someone
else?
s/Brendan/Bernd/
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
--On 21. September 2009 13:42:21 +0200 Bernd Helmle <mailings@oopsware.de>
wrote:
--On 20. September 2009 22:56:53 -0400 Robert Haas
<robertmhaas@gmail.com> wrote:So is this ready to commit, or what?
Not yet, see the comments Alvaro did upthread. Please note that i'm still
reviewing this one and i hope to post results tomorrow (there wasn't
plenty of free time over the weekend, i'm sorry).
Here some further comments on the current patch:
- I'm not sure i like the name of the new system catalog pg_setting. Wie
already have pg_settings, i think this can be confusing. Maybe we need a
different name, e.g. pg_user_setting? This seems along the line with the
other *user* system objects (e.g. pg_stat_user_tables), where only "user
specific" objects are displayed.
- I have thought a little bit about the changes in the system views.
pg_roles and pg_shadow (as Alvaro already mentioned), need to be adjusted
(joined to the new catalog), to display rolconfig/useconfig. However, it's
unclear *how* to expose those information, for example, do we want to
expose roleconfig specific for the current database or for all databases
the role has a specific config for ?
- The code mentions the lack of lock synchronization. Maybe i'm missing
something obvious (its late here), but is there a reason this can't be done
by obtaining a lock on pg_authid (not sure about the backend user
initialization phase though) ?
- Regarding the missing UI: i go with Alvaro's proposal:
ALTER ROLE <rolename> [ALTER] DATABASE <dbname> SET <config> TO <value>;
Maybe we can make the 2nd ALTER optional.
Thoughts?
--
Thanks
Bernd
Re: TODO item: Allow more complex user/database default GUC settings
On Mon, Sep 21, 2009 at 12:23 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Robert Haas escribió:
So here's the followup question - do you intend to do one of those
things for this CommitFest, or should we mark this as Returned with
Feedback once Bernd posts the rest of his review?What feedback is it supposed to be returned with? Precisely what I
wanted is some feedback on the general idea. Brendan's "I like the
approach" is good, but is it enough to deter a later veto from someone
else?
Well, you've hit there on one of the things that we don't always do
well. Many a patch author has posted an idea, received no feedback,
proceeded to implementation, and then the knives come out. On a good
day, the CommitFest process ensures that every patch gets a second
opinion, but it doesn't guarantee that a third opinion won't come
crawling out of the woodwork at a later date. In this respect, you're
actually operating at a slight advantage relative to most of us,
because you can post your revised patch and commit it if no one
objects too strongly, whereas I (for example) have to convince one of
about two people - Tom or Peter, for nearly anything I'm likely to
develop - to take an affirmative action on my behalf.
This whole phenomenon of proposals to which no objection was made at
the outset later getting flak for one reason or another is, I think, a
source of much frustration and discourages people from putting effort
into projects they might otherwise be willing to undertake. But I
haven't the least idea how to fix it, and I can't offer you any
guarantees with respect to the present situation either.
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
On Tue, Sep 22, 2009 at 4:16 AM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 21. September 2009 13:42:21 +0200 Bernd Helmle <mailings@oopsware.de>
wrote:--On 20. September 2009 22:56:53 -0400 Robert Haas
<robertmhaas@gmail.com> wrote:So is this ready to commit, or what?
Not yet, see the comments Alvaro did upthread. Please note that i'm still
reviewing this one and i hope to post results tomorrow (there wasn't
plenty of free time over the weekend, i'm sorry).Here some further comments on the current patch:
- I'm not sure i like the name of the new system catalog pg_setting. Wie
already have pg_settings, i think this can be confusing. Maybe we need a
different name, e.g. pg_user_setting? This seems along the line with the
other *user* system objects (e.g. pg_stat_user_tables), where only "user
specific" objects are displayed.- I have thought a little bit about the changes in the system views.
pg_roles and pg_shadow (as Alvaro already mentioned), need to be adjusted
(joined to the new catalog), to display rolconfig/useconfig. However, it's
unclear *how* to expose those information, for example, do we want to expose
roleconfig specific for the current database or for all databases the role
has a specific config for ?- The code mentions the lack of lock synchronization. Maybe i'm missing
something obvious (its late here), but is there a reason this can't be done
by obtaining a lock on pg_authid (not sure about the backend user
initialization phase though) ?- Regarding the missing UI: i go with Alvaro's proposal:
ALTER ROLE <rolename> [ALTER] DATABASE <dbname> SET <config> TO <value>;
Maybe we can make the 2nd ALTER optional.
Thoughts?
ON instead of second ALTER looks better, and IMHO DATABASE <dbname> should
be optional too:
ALTER ROLE <rolename> [ON DATABASE <dbname>] SET <config> TO <value>;
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: TODO item: Allow more complex user/database default GUC settings
Gurjeet Singh <singh.gurjeet@gmail.com> writes:
ON instead of second ALTER looks better, and IMHO DATABASE <dbname> should
be optional too:
ALTER ROLE <rolename> [ON DATABASE <dbname>] SET <config> TO <value>;
IN, not ON.
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera <alvherre@commandprompt.com> writes:
Robert Haas escribi�:
So here's the followup question - do you intend to do one of those
things for this CommitFest, or should we mark this as Returned with
Feedback once Bernd posts the rest of his review?
What feedback is it supposed to be returned with? Precisely what I
wanted is some feedback on the general idea. Brendan's "I like the
approach" is good, but is it enough to deter a later veto from someone
else?
FWIW, I looked the patch over quickly, and I think it will be fine once
Bernd's comments are addressed. In particular I agree with the
objection to the name "pg_setting" as being confusingly close to
"pg_settings". But "pg_user_setting" isn't better. Maybe
"pg_db_role_settings"?
As far as the lock issue goes, I don't see any reason why the catalog
change creates a reason for new/different locking than we had before.
Any attempt to make concurrent updates to the same row will generate an
error, and that seems enough to me ...
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
--On 23. September 2009 14:10:39 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
FWIW, I looked the patch over quickly, and I think it will be fine once
Bernd's comments are addressed. In particular I agree with the
objection to the name "pg_setting" as being confusingly close to
"pg_settings". But "pg_user_setting" isn't better. Maybe
"pg_db_role_settings"?
Jepp, that's better, +1 from me.
I'm done with this, too, so i will mark this as "Returned with Feedback",
if no one objects?
--
Thanks
Bernd
Re: TODO item: Allow more complex user/database default GUC settings
On Wed, Sep 23, 2009 at 3:03 PM, Bernd Helmle <mailings@oopsware.de> wrote:
--On 23. September 2009 14:10:39 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
FWIW, I looked the patch over quickly, and I think it will be fine once
Bernd's comments are addressed. In particular I agree with the
objection to the name "pg_setting" as being confusingly close to
"pg_settings". But "pg_user_setting" isn't better. Maybe
"pg_db_role_settings"?Jepp, that's better, +1 from me.
I'm done with this, too, so i will mark this as "Returned with Feedback", if
no one objects?
It can be marked "Waiting on Author" if it's going to be reworked in
the next few days. If no plans to rework, or if the rework doesn't
materialize, then "Returned with Feedback".
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
Tom Lane escribi�:
Gurjeet Singh <singh.gurjeet@gmail.com> writes:
ON instead of second ALTER looks better, and IMHO DATABASE <dbname> should
be optional too:ALTER ROLE <rolename> [ON DATABASE <dbname>] SET <config> TO <value>;
IN, not ON.
This creates a parser conflict with
CREATE ROLE foo IN ROLE bar
I think it can be solved by splitting OptRoleElem in a set of
productions for ALTER and a superset of that for ALTER. I'll go try
that.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera escribi�:
I think it can be solved by splitting OptRoleElem in a set of
productions for ALTER and a superset of that for ALTER. I'll go try
that.
Right, that works. Updated patch attached; should solve the issues
raised in the thread. I renamed the catalog pg_db_role_setting as
suggested by Tom.
I have updated the pg_user and pg_roles definitions so that they include
the settings for the role, but only those that are not specific to any
database.
I have also added a view, whose only purpose is to convert the role and
database OIDs into names. It's been named pg_db_role_settings, but if
anyone has a better suggestion I'm all ears.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachments:
settings-3.patchtext/x-diff; charset=us-asciiDownload
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.207
diff -c -p -r2.207 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 22 Sep 2009 23:43:37 -0000 2.207
--- doc/src/sgml/catalogs.sgml 25 Sep 2009 23:46:31 -0000
***************
*** 199,204 ****
--- 199,209 ----
</row>
<row>
+ <entry><link linkend="catalog-pg-db-role-setting"><structname>pg_db_role_setting</structname></link></entry>
+ <entry>per-role and per-database settings</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
<entry>dependencies on shared objects</entry>
</row>
***************
*** 2132,2144 ****
</row>
<row>
- <entry><structfield>datconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>datacl</structfield></entry>
<entry><type>aclitem[]</type></entry>
<entry></entry>
--- 2137,2142 ----
***************
*** 4014,4019 ****
--- 4012,4076 ----
</sect1>
+ <sect1 id="catalog-pg-db-role-setting">
+ <title><structname>pg_db_role_setting</structname></title>
+
+ <indexterm zone="catalog-pg-db-role-setting">
+ <primary>pg_db_role_setting</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_db_role_setting</structname> records the default
+ values that have been set for run-time configuration variables,
+ for each role and database combination.
+ </para>
+
+ <para>
+ Unlike most system catalogs, <structname>pg_db_role_setting</structname>
+ is shared across all databases of a cluster: there is only one
+ copy of <structname>pg_db_role_setting</structname> per cluster, not
+ one per database.
+ </para>
+
+ <table>
+ <title><structname>pg_db_role_setting</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>setdatabase</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
+ <entry>The OID of the database the setting is applicable to, or zero if not database-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setrole</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
+ <entry>The OID of the role the setting is applicable to, or zero if not role-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setconfig</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry></entry>
+ <entry>Defaults for run-time configuration variables</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="catalog-pg-shdepend">
<title><structname>pg_shdepend</structname></title>
***************
*** 6466,6478 ****
</row>
<row>
- <entry><structfield>rolconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>oid</structfield></entry>
<entry><type>oid</type></entry>
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
--- 6523,6528 ----
Index: src/backend/catalog/Makefile
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/Makefile,v
retrieving revision 1.71
diff -c -p -r1.71 Makefile
*** src/backend/catalog/Makefile 26 Aug 2009 22:24:43 -0000 1.71
--- src/backend/catalog/Makefile 25 Sep 2009 19:20:23 -0000
*************** include $(top_builddir)/src/Makefile.glo
*** 13,19 ****
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
! pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
--- 13,19 ----
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
! pg_db_role_setting.o pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
*************** POSTGRES_BKI_SRCS = $(addprefix $(top_sr
*** 32,38 ****
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
! pg_database.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
--- 32,38 ----
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
! pg_database.h pg_db_role_setting.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
Index: src/backend/catalog/catalog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/catalog.c,v
retrieving revision 1.83
diff -c -p -r1.83 catalog.c
*** src/backend/catalog/catalog.c 11 Jun 2009 14:48:54 -0000 1.83
--- src/backend/catalog/catalog.c 25 Sep 2009 19:54:45 -0000
***************
*** 31,36 ****
--- 31,37 ----
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_pltemplate.h"
+ #include "catalog/pg_db_role_setting.h"
#include "catalog/pg_shdepend.h"
#include "catalog/pg_shdescription.h"
#include "catalog/pg_tablespace.h"
*************** IsSharedRelation(Oid relationId)
*** 306,312 ****
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
! relationId == TableSpaceRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
--- 307,314 ----
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
! relationId == TableSpaceRelationId ||
! relationId == DbRoleSettingRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
*************** IsSharedRelation(Oid relationId)
*** 320,326 ****
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
! relationId == TablespaceNameIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
--- 322,329 ----
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
! relationId == TablespaceNameIndexId ||
! relationId == DbRoleSettingDatidRolidIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
*************** IsSharedRelation(Oid relationId)
*** 328,334 ****
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
! relationId == PgShdescriptionToastIndex)
return true;
return false;
}
--- 331,339 ----
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
! relationId == PgShdescriptionToastIndex ||
! relationId == PgDbRoleSettingToastTable ||
! relationId == PgDbRoleSettingToastIndex)
return true;
return false;
}
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.60
diff -c -p -r1.60 system_views.sql
*** src/backend/catalog/system_views.sql 7 Apr 2009 00:31:26 -0000 1.60
--- src/backend/catalog/system_views.sql 25 Sep 2009 23:13:18 -0000
*************** CREATE VIEW pg_roles AS
*** 18,38 ****
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
! rolconfig,
! oid
! FROM pg_authid;
CREATE VIEW pg_shadow AS
SELECT
rolname AS usename,
! oid AS usesysid,
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
! rolconfig AS useconfig
! FROM pg_authid
WHERE rolcanlogin;
REVOKE ALL on pg_shadow FROM public;
--- 18,40 ----
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
! setconfig as rolconfig,
! pg_authid.oid
! FROM pg_authid LEFT JOIN pg_db_role_setting s
! ON (pg_authid.oid = setrole AND setdatabase = 0);
CREATE VIEW pg_shadow AS
SELECT
rolname AS usename,
! pg_authid.oid AS usesysid,
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
! setconfig AS useconfig
! FROM pg_authid LEFT JOIN pg_db_role_setting s
! ON (pg_authid.oid = setrole AND setdatabase = 0)
WHERE rolcanlogin;
REVOKE ALL on pg_shadow FROM public;
*************** CREATE RULE pg_settings_n AS
*** 171,176 ****
--- 173,187 ----
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+ CREATE VIEW pg_db_role_settings AS
+ SELECT
+ datname AS database,
+ rolname AS role,
+ setconfig AS settings
+ FROM pg_db_role_setting AS s
+ LEFT JOIN pg_database ON pg_database.oid = setdatabase
+ LEFT JOIN pg_roles ON pg_roles.oid = setrole;
+
CREATE VIEW pg_timezone_abbrevs AS
SELECT * FROM pg_timezone_abbrevs();
Index: src/backend/commands/dbcommands.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.226
diff -c -p -r1.226 dbcommands.c
*** src/backend/commands/dbcommands.c 1 Sep 2009 02:54:51 -0000 1.226
--- src/backend/commands/dbcommands.c 25 Sep 2009 19:21:19 -0000
***************
*** 33,38 ****
--- 33,39 ----
#include "catalog/indexing.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+ #include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
***************
*** 50,56 ****
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
- #include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/pg_locale.h"
#include "utils/snapmgr.h"
--- 51,56 ----
*************** createdb(const CreatedbStmt *stmt)
*** 544,555 ****
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
! * We deliberately set datconfig and datacl to defaults (NULL), rather
! * than copying them from the template database. Copying datacl would be
! * a bad idea when the owner is not the same as the template's owner. It's
! * more debatable whether datconfig should be copied.
*/
- new_record_nulls[Anum_pg_database_datconfig - 1] = true;
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
--- 544,553 ----
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
! * We deliberately set datacl to default (NULL), rather
! * than copying it from the template database. Copying it would be
! * a bad idea when the owner is not the same as the template's owner.
*/
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
*************** dropdb(const char *dbname, bool missing_
*** 821,826 ****
--- 819,829 ----
DeleteSharedComments(db_id, DatabaseRelationId);
/*
+ * Remove settings associated with this database
+ */
+ DropSetting(db_id, InvalidOid);
+
+ /*
* Remove shared dependency references for the database.
*/
dropDatabaseDependencies(db_id);
*************** AlterDatabase(AlterDatabaseStmt *stmt, b
*** 1397,1481 ****
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
! char *valuestr;
! HeapTuple tuple,
! newtuple;
! Relation rel;
! ScanKeyData scankey;
! SysScanDesc scan;
! Datum repl_val[Natts_pg_database];
! bool repl_null[Natts_pg_database];
! bool repl_repl[Natts_pg_database];
!
! valuestr = ExtractSetVariableArgs(stmt->setstmt);
!
! /*
! * Get the old tuple. We don't need a lock on the database per se,
! * because we're not going to do anything that would mess up incoming
! * connections.
! */
! rel = heap_open(DatabaseRelationId, RowExclusiveLock);
! ScanKeyInit(&scankey,
! Anum_pg_database_datname,
! BTEqualStrategyNumber, F_NAMEEQ,
! NameGetDatum(stmt->dbname));
! scan = systable_beginscan(rel, DatabaseNameIndexId, true,
! SnapshotNow, 1, &scankey);
! tuple = systable_getnext(scan);
! if (!HeapTupleIsValid(tuple))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_DATABASE),
! errmsg("database \"%s\" does not exist", stmt->dbname)));
!
! if (!pg_database_ownercheck(HeapTupleGetOid(tuple), GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
! stmt->dbname);
!
! memset(repl_repl, false, sizeof(repl_repl));
! repl_repl[Anum_pg_database_datconfig - 1] = true;
!
! if (stmt->setstmt->kind == VAR_RESET_ALL)
! {
! /* RESET ALL, so just set datconfig to null */
! repl_null[Anum_pg_database_datconfig - 1] = true;
! repl_val[Anum_pg_database_datconfig - 1] = (Datum) 0;
! }
! else
! {
! Datum datum;
! bool isnull;
! ArrayType *a;
!
! repl_null[Anum_pg_database_datconfig - 1] = false;
!
! /* Extract old value of datconfig */
! datum = heap_getattr(tuple, Anum_pg_database_datconfig,
! RelationGetDescr(rel), &isnull);
! a = isnull ? NULL : DatumGetArrayTypeP(datum);
!
! /* Update (valuestr is NULL in RESET cases) */
! if (valuestr)
! a = GUCArrayAdd(a, stmt->setstmt->name, valuestr);
! else
! a = GUCArrayDelete(a, stmt->setstmt->name);
! if (a)
! repl_val[Anum_pg_database_datconfig - 1] = PointerGetDatum(a);
! else
! repl_null[Anum_pg_database_datconfig - 1] = true;
! }
!
! newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
! repl_val, repl_null, repl_repl);
! simple_heap_update(rel, &tuple->t_self, newtuple);
!
! /* Update indexes */
! CatalogUpdateIndexes(rel, newtuple);
!
! systable_endscan(scan);
!
! /* Close pg_database, but keep lock till commit */
! heap_close(rel, NoLock);
}
--- 1400,1421 ----
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
! Oid datid = get_database_oid(stmt->dbname);
! if (!OidIsValid(datid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_DATABASE),
! errmsg("database \"%s\" does not exist", stmt->dbname)));
!
! LockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
!
! if (!pg_database_ownercheck(datid, GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
! stmt->dbname);
!
! AlterSetting(datid, InvalidOid, stmt->setstmt);
!
! UnlockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
}
Index: src/backend/commands/user.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/user.c,v
retrieving revision 1.188
diff -c -p -r1.188 user.c
*** src/backend/commands/user.c 1 Sep 2009 02:54:51 -0000 1.188
--- src/backend/commands/user.c 25 Sep 2009 19:22:11 -0000
***************
*** 19,25 ****
--- 19,27 ----
#include "catalog/indexing.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+ #include "catalog/pg_db_role_setting.h"
#include "commands/comment.h"
+ #include "commands/dbcommands.h"
#include "commands/user.h"
#include "libpq/md5.h"
#include "miscadmin.h"
***************
*** 27,33 ****
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
- #include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
--- 29,34 ----
*************** CreateRole(CreateRoleStmt *stmt)
*** 341,348 ****
else
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = true;
- new_record_nulls[Anum_pg_authid_rolconfig - 1] = true;
-
tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
/*
--- 342,347 ----
*************** AlterRole(AlterRoleStmt *stmt)
*** 715,744 ****
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
! char *valuestr;
! HeapTuple oldtuple,
! newtuple;
! Relation rel;
! Datum repl_val[Natts_pg_authid];
! bool repl_null[Natts_pg_authid];
! bool repl_repl[Natts_pg_authid];
! valuestr = ExtractSetVariableArgs(stmt->setstmt);
! rel = heap_open(AuthIdRelationId, RowExclusiveLock);
! oldtuple = SearchSysCache(AUTHNAME,
! PointerGetDatum(stmt->role),
! 0, 0, 0);
! if (!HeapTupleIsValid(oldtuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
/*
* 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(oldtuple))->rolsuper)
{
if (!superuser())
ereport(ERROR,
--- 714,738 ----
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
! HeapTuple roletuple;
! Oid databaseid = InvalidOid;
! roletuple = SearchSysCache(AUTHNAME,
! PointerGetDatum(stmt->role),
! 0, 0, 0);
! if (!HeapTupleIsValid(roletuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
+ /* XXX need some kind of lock here */
+
/*
* 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,
*************** AlterRoleSet(AlterRoleSetStmt *stmt)
*** 748,801 ****
else
{
if (!have_createrole_privilege() &&
! HeapTupleGetOid(oldtuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
! memset(repl_repl, false, sizeof(repl_repl));
! repl_repl[Anum_pg_authid_rolconfig - 1] = true;
!
! if (stmt->setstmt->kind == VAR_RESET_ALL)
{
! /* RESET ALL, so just set rolconfig to null */
! repl_null[Anum_pg_authid_rolconfig - 1] = true;
! repl_val[Anum_pg_authid_rolconfig - 1] = (Datum) 0;
! }
! else
! {
! Datum datum;
! bool isnull;
! ArrayType *array;
!
! repl_null[Anum_pg_authid_rolconfig - 1] = false;
!
! /* Extract old value of rolconfig */
! datum = SysCacheGetAttr(AUTHNAME, oldtuple,
! Anum_pg_authid_rolconfig, &isnull);
! array = isnull ? NULL : DatumGetArrayTypeP(datum);
!
! /* Update (valuestr is NULL in RESET cases) */
! if (valuestr)
! array = GUCArrayAdd(array, stmt->setstmt->name, valuestr);
! else
! array = GUCArrayDelete(array, stmt->setstmt->name);
!
! if (array)
! repl_val[Anum_pg_authid_rolconfig - 1] = PointerGetDatum(array);
! else
! repl_null[Anum_pg_authid_rolconfig - 1] = true;
}
! newtuple = heap_modify_tuple(oldtuple, RelationGetDescr(rel),
! repl_val, repl_null, repl_repl);
!
! simple_heap_update(rel, &oldtuple->t_self, newtuple);
! CatalogUpdateIndexes(rel, newtuple);
!
! ReleaseSysCache(oldtuple);
! heap_close(rel, RowExclusiveLock);
}
--- 742,765 ----
else
{
if (!have_createrole_privilege() &&
! HeapTupleGetOid(roletuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
! /* look up the database, if specified */
! if (stmt->database != NULL)
{
! databaseid = get_database_oid(stmt->database);
! if (!OidIsValid(databaseid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("database \"%s\" not found", stmt->database)));
}
! AlterSetting(databaseid, HeapTupleGetOid(roletuple), stmt->setstmt);
! ReleaseSysCache(roletuple);
}
*************** DropRole(DropRoleStmt *stmt)
*** 944,949 ****
--- 908,918 ----
DeleteSharedComments(roleid, AuthIdRelationId);
/*
+ * Remove settings for this role.
+ */
+ DropSetting(InvalidOid, roleid);
+
+ /*
* Advance command counter so that later iterations of this loop will
* see the changes already made. This is essential if, for example,
* we are trying to drop both a role and one of its direct members ---
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.438
diff -c -p -r1.438 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 22 Sep 2009 23:43:37 -0000 1.438
--- src/backend/nodes/copyfuncs.c 25 Sep 2009 04:02:21 -0000
*************** _copyAlterRoleSetStmt(AlterRoleSetStmt *
*** 3163,3168 ****
--- 3163,3169 ----
AlterRoleSetStmt *newnode = makeNode(AlterRoleSetStmt);
COPY_STRING_FIELD(role);
+ COPY_STRING_FIELD(database);
COPY_NODE_FIELD(setstmt);
return newnode;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.361
diff -c -p -r1.361 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 22 Sep 2009 23:43:38 -0000 1.361
--- src/backend/nodes/equalfuncs.c 25 Sep 2009 04:02:21 -0000
*************** static bool
*** 1716,1721 ****
--- 1716,1722 ----
_equalAlterRoleSetStmt(AlterRoleSetStmt *a, AlterRoleSetStmt *b)
{
COMPARE_STRING_FIELD(role);
+ COMPARE_STRING_FIELD(database);
COMPARE_NODE_FIELD(setstmt);
return true;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.679
diff -c -p -r2.679 gram.y
*** src/backend/parser/gram.y 22 Sep 2009 23:43:38 -0000 2.679
--- src/backend/parser/gram.y 25 Sep 2009 14:09:49 -0000
*************** static TypeName *TableFuncTypeName(List
*** 235,242 ****
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
! %type <list> OptRoleList
! %type <defelt> OptRoleElem
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
--- 235,242 ----
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
! %type <list> OptRoleList AlterOptRoleList
! %type <defelt> CreateOptRoleElem AlterOptRoleElem
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
*************** opt_with: WITH {}
*** 756,766 ****
* is "WITH ADMIN name".
*/
OptRoleList:
! OptRoleList OptRoleElem { $$ = lappend($1, $2); }
| /* EMPTY */ { $$ = NIL; }
;
! OptRoleElem:
PASSWORD Sconst
{
$$ = makeDefElem("password",
--- 756,771 ----
* is "WITH ADMIN name".
*/
OptRoleList:
! OptRoleList CreateOptRoleElem { $$ = lappend($1, $2); }
| /* EMPTY */ { $$ = NIL; }
;
! AlterOptRoleList:
! AlterOptRoleList AlterOptRoleElem { $$ = lappend($1, $2); }
! | /* EMPTY */ { $$ = NIL; }
! ;
!
! AlterOptRoleElem:
PASSWORD Sconst
{
$$ = makeDefElem("password",
*************** OptRoleElem:
*** 842,848 ****
{
$$ = makeDefElem("rolemembers", (Node *)$2);
}
! /* The following are not supported by ALTER ROLE/USER/GROUP */
| SYSID Iconst
{
$$ = makeDefElem("sysid", (Node *)makeInteger($2));
--- 847,857 ----
{
$$ = makeDefElem("rolemembers", (Node *)$2);
}
! ;
!
! CreateOptRoleElem:
! AlterOptRoleElem { $$ = $1; }
! /* The following are not supported by ALTER ROLE/USER/GROUP */
| SYSID Iconst
{
$$ = makeDefElem("sysid", (Node *)makeInteger($2));
*************** CreateUserStmt:
*** 891,897 ****
*****************************************************************************/
AlterRoleStmt:
! ALTER ROLE RoleId opt_with OptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
--- 900,906 ----
*****************************************************************************/
AlterRoleStmt:
! ALTER ROLE RoleId opt_with AlterOptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
*************** AlterRoleStmt:
*** 902,911 ****
;
AlterRoleSetStmt:
! ALTER ROLE RoleId SetResetClause
{
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
n->role = $3;
n->setstmt = $4;
$$ = (Node *)n;
}
--- 911,929 ----
;
AlterRoleSetStmt:
! ALTER ROLE RoleId IN_P DATABASE database_name SetResetClause
! {
! AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
! n->role = $3;
! n->database = $6;
! n->setstmt = $7;
! $$ = (Node *)n;
! }
! | ALTER ROLE RoleId SetResetClause
{
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
n->role = $3;
+ n->database = NULL;
n->setstmt = $4;
$$ = (Node *)n;
}
*************** AlterRoleSetStmt:
*** 919,925 ****
*****************************************************************************/
AlterUserStmt:
! ALTER USER RoleId opt_with OptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
--- 937,943 ----
*****************************************************************************/
AlterUserStmt:
! ALTER USER RoleId opt_with AlterOptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
*************** AlterUserSetStmt:
*** 935,940 ****
--- 953,959 ----
{
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
n->role = $3;
+ n->database = NULL;
n->setstmt = $4;
$$ = (Node *)n;
}
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.177
diff -c -p -r1.177 miscinit.c
*** src/backend/utils/init/miscinit.c 27 Aug 2009 16:59:38 -0000 1.177
--- src/backend/utils/init/miscinit.c 25 Sep 2009 04:02:21 -0000
*************** InitializeSessionUserId(const char *role
*** 392,399 ****
{
HeapTuple roleTup;
Form_pg_authid rform;
- Datum datum;
- bool isnull;
Oid roleid;
/*
--- 392,397 ----
*************** InitializeSessionUserId(const char *role
*** 470,493 ****
AuthenticatedUserIsSuperuser ? "on" : "off",
PGC_INTERNAL, PGC_S_OVERRIDE);
- /*
- * Set up user-specific configuration variables. This is a good place to
- * do it so we don't have to read pg_authid twice during session startup.
- */
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolconfig, &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_authid was checked when it was
- * inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_USER, GUC_ACTION_SET);
- }
-
ReleaseSysCache(roleTup);
}
--- 468,473 ----
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.197
diff -c -p -r1.197 postinit.c
*** src/backend/utils/init/postinit.c 1 Sep 2009 00:09:42 -0000 1.197
--- src/backend/utils/init/postinit.c 25 Sep 2009 23:49:11 -0000
***************
*** 27,32 ****
--- 27,33 ----
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+ #include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
#include "libpq/auth.h"
#include "libpq/libpq-be.h"
*************** static void CheckMyDatabase(const char *
*** 63,68 ****
--- 64,70 ----
static void InitCommunication(void);
static void ShutdownPostgres(int code, Datum arg);
static bool ThereIsAtLeastOneRole(void);
+ static void process_settings(Oid databaseid, Oid roleid);
/*** InitPostgres support ***/
*************** CheckMyDatabase(const char *name, bool a
*** 344,372 ****
pg_bind_textdomain_codeset(textdomain(NULL));
#endif
- /*
- * Lastly, set up any database-specific configuration variables.
- */
- if (IsUnderPostmaster)
- {
- Datum datum;
- bool isnull;
-
- datum = SysCacheGetAttr(DATABASEOID, tup, Anum_pg_database_datconfig,
- &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_database was checked when it
- * was inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_DATABASE, GUC_ACTION_SET);
- }
- }
-
ReleaseSysCache(tup);
}
--- 346,351 ----
*************** InitPostgres(const char *in_dbname, Oid
*** 739,744 ****
--- 718,726 ----
/* set up ACL framework (so CheckMyDatabase can check permissions) */
initialize_acl();
+ /* Process pg_db_role_setting options */
+ process_settings(MyDatabaseId, GetSessionUserId());
+
/*
* Re-read the pg_database row for our database, check permissions and
* set up database-specific GUC settings. We can't do this until all the
*************** InitPostgres(const char *in_dbname, Oid
*** 851,856 ****
--- 833,860 ----
CommitTransactionCommand();
}
+ /*
+ * Load GUC settings from pg_db_role_setting.
+ *
+ * We try specific settings for the database/role combination, as well as
+ * general for this database and for this user.
+ */
+ static void
+ process_settings(Oid databaseid, Oid roleid)
+ {
+ Relation relsetting;
+
+ if (!IsUnderPostmaster)
+ return;
+
+ relsetting = heap_open(DbRoleSettingRelationId, AccessShareLock);
+
+ ApplySetting(databaseid, roleid, relsetting, PGC_S_DATABASE_USER);
+ ApplySetting(InvalidOid, roleid, relsetting, PGC_S_USER);
+ ApplySetting(databaseid, InvalidOid, relsetting, PGC_S_DATABASE);
+
+ heap_close(relsetting, AccessShareLock);
+ }
/*
* Backend-shutdown callback. Do cleanup that we want to be sure happens
Index: src/include/catalog/indexing.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/indexing.h,v
retrieving revision 1.108
diff -c -p -r1.108 indexing.h
*** src/include/catalog/indexing.h 11 Jun 2009 14:49:09 -0000 1.108
--- src/include/catalog/indexing.h 25 Sep 2009 19:54:01 -0000
*************** DECLARE_UNIQUE_INDEX(pg_user_mapping_oid
*** 267,272 ****
--- 267,275 ----
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
+ DECLARE_UNIQUE_INDEX(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops));
+ #define DbRoleSettingDatidRolidIndexId 2965
+
/* last step of initialization script: build the indexes declared above */
BUILD_INDICES
Index: src/include/catalog/pg_attribute.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.152
diff -c -p -r1.152 pg_attribute.h
*** src/include/catalog/pg_attribute.h 12 Aug 2009 20:53:30 -0000 1.152
--- src/include/catalog/pg_attribute.h 25 Sep 2009 04:02:21 -0000
*************** DATA(insert ( 1259 tableoid 26 0 0 4
*** 485,492 ****
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
! { 1262, {"datconfig"}, 1009, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \
! { 1262, {"datacl"}, 1034, -1, 0, -1, 13, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
--- 485,491 ----
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
! { 1262, {"datacl"}, 1034, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
Index: src/include/catalog/pg_authid.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_authid.h,v
retrieving revision 1.9
diff -c -p -r1.9 pg_authid.h
*** src/include/catalog/pg_authid.h 1 Jan 2009 17:23:56 -0000 1.9
--- src/include/catalog/pg_authid.h 25 Sep 2009 23:54:26 -0000
*************** CATALOG(pg_authid,1260) BKI_SHARED_RELAT
*** 55,61 ****
/* remaining fields may be null; use heap_getattr to read them! */
text rolpassword; /* password, if any */
timestamptz rolvaliduntil; /* password expiration time, if any */
- text rolconfig[1]; /* GUC settings to apply at login */
} FormData_pg_authid;
#undef timestamptz
--- 55,60 ----
*************** typedef FormData_pg_authid *Form_pg_auth
*** 83,89 ****
#define Anum_pg_authid_rolconnlimit 8
#define Anum_pg_authid_rolpassword 9
#define Anum_pg_authid_rolvaliduntil 10
- #define Anum_pg_authid_rolconfig 11
/* ----------------
* initial contents of pg_authid
--- 82,87 ----
*************** typedef FormData_pg_authid *Form_pg_auth
*** 92,98 ****
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 90,96 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
Index: src/include/catalog/pg_database.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_database.h,v
retrieving revision 1.49
diff -c -p -r1.49 pg_database.h
*** src/include/catalog/pg_database.h 1 Jan 2009 17:23:57 -0000 1.49
--- src/include/catalog/pg_database.h 25 Sep 2009 23:54:32 -0000
*************** CATALOG(pg_database,1262) BKI_SHARED_REL
*** 41,47 ****
Oid datlastsysoid; /* highest OID to consider a system OID */
TransactionId datfrozenxid; /* all Xids < this are frozen in this DB */
Oid dattablespace; /* default table space for this DB */
- text datconfig[1]; /* database-specific GUC (VAR LENGTH) */
aclitem datacl[1]; /* access permissions (VAR LENGTH) */
} FormData_pg_database;
--- 41,46 ----
*************** typedef FormData_pg_database *Form_pg_da
*** 56,62 ****
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 13
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
--- 55,61 ----
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 12
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
*************** typedef FormData_pg_database *Form_pg_da
*** 68,77 ****
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
! #define Anum_pg_database_datconfig 12
! #define Anum_pg_database_datacl 13
! DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_ _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
--- 67,75 ----
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
! #define Anum_pg_database_datacl 12
! DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
Index: src/include/catalog/toasting.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/toasting.h,v
retrieving revision 1.8
diff -c -p -r1.8 toasting.h
*** src/include/catalog/toasting.h 11 Jun 2009 20:46:11 -0000 1.8
--- src/include/catalog/toasting.h 25 Sep 2009 19:56:03 -0000
*************** DECLARE_TOAST(pg_database, 2844, 2845);
*** 58,62 ****
--- 58,65 ----
DECLARE_TOAST(pg_shdescription, 2846, 2847);
#define PgShdescriptionToastTable 2846
#define PgShdescriptionToastIndex 2847
+ DECLARE_TOAST(pg_db_role_setting, 2966, 2967);
+ #define PgDbRoleSettingToastTable 2966
+ #define PgDbRoleSettingToastIndex 2967
#endif /* TOASTING_H */
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.402
diff -c -p -r1.402 parsenodes.h
*** src/include/nodes/parsenodes.h 22 Sep 2009 23:43:41 -0000 1.402
--- src/include/nodes/parsenodes.h 25 Sep 2009 04:02:21 -0000
*************** typedef struct AlterRoleSetStmt
*** 1619,1624 ****
--- 1619,1625 ----
{
NodeTag type;
char *role; /* role name */
+ char *database; /* database name, or NULL */
VariableSetStmt *setstmt; /* SET or RESET subcommand */
} AlterRoleSetStmt;
Index: src/include/utils/guc.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/utils/guc.h,v
retrieving revision 1.105
diff -c -p -r1.105 guc.h
*** src/include/utils/guc.h 22 Sep 2009 23:43:41 -0000 1.105
--- src/include/utils/guc.h 25 Sep 2009 04:02:21 -0000
*************** typedef enum
*** 86,91 ****
--- 86,92 ----
PGC_S_ARGV, /* postmaster command line */
PGC_S_DATABASE, /* per-database setting */
PGC_S_USER, /* per-user setting */
+ PGC_S_DATABASE_USER, /* per-user-and-database setting */
PGC_S_CLIENT, /* from client connection request */
PGC_S_OVERRIDE, /* special case to forcibly set default */
PGC_S_INTERACTIVE, /* dividing line for error reporting */
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.150
diff -c -p -r1.150 rules.out
*** src/test/regress/expected/rules.out 22 Sep 2009 15:46:34 -0000 1.150
--- src/test/regress/expected/rules.out 25 Sep 2009 23:51:31 -0000
*************** SELECT viewname, definition FROM pg_view
*** 1280,1294 ****
--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
--- 1280,1295 ----
--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
+ pg_db_role_settings | SELECT pg_database.datname AS database, pg_roles.rolname AS role, s.setconfig AS settings FROM ((pg_db_role_setting s LEFT JOIN pg_database ON ((pg_database.oid = s.setdatabase))) LEFT JOIN pg_roles ON ((pg_roles.oid = s.setrole)));
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
*************** SELECT viewname, definition FROM pg_view
*** 1329,1335 ****
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp;
! (51 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1330,1336 ----
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp;
! (52 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
Index: src/test/regress/expected/sanity_check.out
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/test/regress/expected/sanity_check.out,v
retrieving revision 1.40
diff -c -p -r1.40 sanity_check.out
*** src/test/regress/expected/sanity_check.out 29 Jul 2009 20:56:21 -0000 1.40
--- src/test/regress/expected/sanity_check.out 25 Sep 2009 23:51:26 -0000
*************** SELECT relname, relhasindex
*** 95,100 ****
--- 95,101 ----
pg_constraint | t
pg_conversion | t
pg_database | t
+ pg_db_role_setting | t
pg_depend | t
pg_description | t
pg_enum | t
*************** SELECT relname, relhasindex
*** 151,157 ****
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
! (140 rows)
--
-- another sanity check: every system catalog that has OIDs should have
--- 152,158 ----
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
! (141 rows)
--
-- another sanity check: every system catalog that has OIDs should have
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera <alvherre@commandprompt.com> writes:
Right, that works. Updated patch attached; should solve the issues
raised in the thread. I renamed the catalog pg_db_role_setting as
suggested by Tom.
...
I have also added a view, whose only purpose is to convert the role and
database OIDs into names. It's been named pg_db_role_settings, but if
anyone has a better suggestion I'm all ears.
I dislike the idea of having a catalog and a view whose names are the
same except for a plural. It's confusing as heck, because no one will
remember which is which.
Since pg_settings is the existing user view, I think pg_db_role_settings
is a reasonable choice for the new view, but then we need a different
name for the catalog. The only thing that comes to mind right now is
"pg_db_role_default", but I don't like it much. Anybody have other
suggestions?
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
On Sep 26, 2009, at 11:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@commandprompt.com> writes:
Right, that works. Updated patch attached; should solve the issues
raised in the thread. I renamed the catalog pg_db_role_setting as
suggested by Tom.
...
I have also added a view, whose only purpose is to convert the role
and
database OIDs into names. It's been named pg_db_role_settings, but
if
anyone has a better suggestion I'm all ears.I dislike the idea of having a catalog and a view whose names are the
same except for a plural. It's confusing as heck, because no one will
remember which is which.Since pg_settings is the existing user view, I think
pg_db_role_settings
is a reasonable choice for the new view, but then we need a different
name for the catalog. The only thing that comes to mind right now is
"pg_db_role_default", but I don't like it much. Anybody have other
suggestions?
The problem of having both a table and a closely related view is, IME,
one that comes up a lot. I think you just need to pick a convention
and stick with it. Mine is to append "_view" to the table name.
Renaming the underlying table doesn't seem like it helps at all.
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
Robert Haas escribi�:
The problem of having both a table and a closely related view is,
IME, one that comes up a lot. I think you just need to pick a
convention and stick with it. Mine is to append "_view" to the
table name.
That would make the difference clear, but since what the user normally
wants to see is the view, it seems a poor solution to make the view the
more difficult one to type (and the one that isn't tab-completed first
in psql). I'd go with naming the view pg_db_role_setting and append
"_internal" to the catalog or something similar, except that we don't
have any catalog with such a bad name yet and I don't want to start.
Maybe name the table pg_configuration?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: TODO item: Allow more complex user/database default GUC settings
On Sat, Sep 26, 2009 at 11:44 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Robert Haas escribió:
The problem of having both a table and a closely related view is,
IME, one that comes up a lot. I think you just need to pick a
convention and stick with it. Mine is to append "_view" to the
table name.That would make the difference clear, but since what the user normally
wants to see is the view, it seems a poor solution to make the view the
more difficult one to type (and the one that isn't tab-completed first
in psql). I'd go with naming the view pg_db_role_setting and append
"_internal" to the catalog or something similar, except that we don't
have any catalog with such a bad name yet and I don't want to start.Maybe name the table pg_configuration?
That seems to me to be just confusing the issue. Now the table name
and the view name are just totally different with no obvious
connection between them. We have enough nonsense of this type already
(e.g. pg_stats vs. pg_statistic; pg_authid vs. pg_roles vs.
pg_shadow). I think we need to settle on a system for handling
problems of this type and document it in the fine manual or perhaps a
README somewhere, and stick with it. Inventing random unconnected
names is just craziness.
Now, if you/others don't like my _view convention; that's fine. Just
pick something else. Really, I don't believe the tab-completion thing
is much of a problem, you just type underscore-tab and you're there.
But I am 100% OK with whatever we pick, as long as it is something
easy to remember that we have a chance of being able to apply
consistently.
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
On Fri, Sep 25, 2009 at 8:05 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Alvaro Herrera escribió:
I think it can be solved by splitting OptRoleElem in a set of
productions for ALTER and a superset of that for ALTER. I'll go try
that.Right, that works. Updated patch attached; should solve the issues
raised in the thread. I renamed the catalog pg_db_role_setting as
suggested by Tom.I have updated the pg_user and pg_roles definitions so that they include
the settings for the role, but only those that are not specific to any
database.I have also added a view, whose only purpose is to convert the role and
database OIDs into names. It's been named pg_db_role_settings, but if
anyone has a better suggestion I'm all ears.
Bernd,
Can you review this new version and mark this as Ready for Committer
if it looks OK, or else respond with comments and set it back to
Waiting on Author?
Thanks,
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
Robert Haas <robertmhaas@gmail.com> writes:
That seems to me to be just confusing the issue. Now the table name
and the view name are just totally different with no obvious
connection between them. We have enough nonsense of this type already
(e.g. pg_stats vs. pg_statistic; pg_authid vs. pg_roles vs.
pg_shadow). I think we need to settle on a system for handling
problems of this type and document it in the fine manual or perhaps a
README somewhere, and stick with it. Inventing random unconnected
names is just craziness.
Actually, to the extent that we have any convention at all, it's
to use plurals for system view names (pg_tables, pg_views, etc)
and singular for underlying catalogs (pg_index). The only exception
to this on the catalog side is pg_auth_members, which is arguably
misnamed. (pg_inherits is sort of an exception, but it's weird in a
different way: its name is a verb not a noun.) The apparent exceptions
on the view side (pg_group, pg_shadow, pg_user) are actually views that
are backward compatible substitutes for former catalogs, so they are not
really intentional exceptions.
Now it's also the case that we've tended to use tech-speak names
for catalogs (eg, pg_class, pg_namespace not pg_table, pg_schema)
and so that gives us an additional degree of separation between
those names and the more user-facing names chosen for views.
What we seem to be lacking in this case is a good tech-speak
option for the underlying catalog name. I'm still not happy
with having a catalog and a view that are exactly the same
except for "s", especially since as Alvaro notes that won't
lead to desirable tab-completion behavior. OTOH, we have
survived with pg_index vs pg_indexes, so maybe it wouldn't
kill us.
BTW, have we thought much about the simplest possible solution,
which is to not have the view? How badly do we need it? Seems
like dropping the functionality into a psql \d command might be
a viable alternative.
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
--On 27. September 2009 21:59:37 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:
Bernd,
Can you review this new version and mark this as Ready for Committer
if it looks OK, or else respond with comments and set it back to
Waiting on Author?
Seems Alvaro forgot to include pg_db_role_setting.h, it doesn't compile
anymore with this error:
catalog.c:34:40: error: catalog/pg_db_role_setting.h: No such file or
directory
catalog.c: In function ‘IsSharedRelation’:
catalog.c:311: error: ‘DbRoleSettingRelationId’ undeclared (first use
in this function)
--
Thanks
Bernd
Re: TODO item: Allow more complex user/database default GUC settings
On Sep 27, 2009, at 9:19 PM, Tom Lane wrote:
What we seem to be lacking in this case is a good tech-speak
option for the underlying catalog name. I'm still not happy
with having a catalog and a view that are exactly the same
except for "s", especially since as Alvaro notes that won't
lead to desirable tab-completion behavior. OTOH, we have
survived with pg_index vs pg_indexes, so maybe it wouldn't
kill us.
Another option is to revisit the set of system views (http://
pgfoundry.org/projects/newsysviews/). IIRC there was some other
recent reason we wanted to do that.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Re: TODO item: Allow more complex user/database default GUC settings
Bernd Helmle escribi�:
Seems Alvaro forgot to include pg_db_role_setting.h, it doesn't
compile anymore with this error:
Huh, you're right, I did :-( Let me unpack the laptop ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
Bernd Helmle escribi�:
Seems Alvaro forgot to include pg_db_role_setting.h, it doesn't
compile anymore with this error:
Here they are.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
--On 28. September 2009 19:02:34 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Seems Alvaro forgot to include pg_db_role_setting.h, it doesn't
compile anymore with this error:Here they are.
I'll see if i can get to it tonight. I'm currently travelling, so it could
be delayed until Thursday.
--
Thanks
Bernd
Re: TODO item: Allow more complex user/database default GUC settings
And here's the last necessary bit, which is pg_dump support for all
this.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachments:
setting-pgdump.patchtext/x-diff; charset=us-asciiDownload
*** src/bin/pg_dump/pg_dumpall.c 11 Jun 2009 14:49:07 -0000 1.126
--- src/bin/pg_dump/pg_dumpall.c 30 Sep 2009 14:32:47 -0000
***************
*** 43,50 ****
static void dumpCreateDB(PGconn *conn);
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name);
static void dumpDatabases(PGconn *conn);
static void dumpTimestamp(char *msg);
static void doShellQuoting(PQExpBuffer buf, const char *str);
--- 43,52 ----
static void dumpCreateDB(PGconn *conn);
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
+ static void dumpDbRoleConfig(PGconn *conn);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name, const char *type2,
! const char *name2);
static void dumpDatabases(PGconn *conn);
static void dumpTimestamp(char *msg);
static void doShellQuoting(PQExpBuffer buf, const char *str);
***************
*** 501,506 ****
--- 503,515 ----
/* Dump CREATE DATABASE commands */
if (!globals_only && !roles_only && !tablespaces_only)
dumpCreateDB(conn);
+
+ /* Dump role/database settings */
+ if (!tablespaces_only)
+ {
+ if (server_version >= 80500)
+ dumpDbRoleConfig(conn);
+ }
}
if (!globals_only && !roles_only && !tablespaces_only)
***************
*** 1325,1339 ****
{
PGresult *res;
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";");
res = executeQuery(conn, buf->data);
! if (!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname);
PQclear(res);
count++;
}
--- 1334,1357 ----
{
PGresult *res;
! if (server_version >= 80500)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
! else
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
appendStringLiteralConn(buf, dbname, conn);
+
+ if (server_version >= 80500)
+ appendPQExpBuffer(buf, ")");
+
appendPQExpBuffer(buf, ";");
res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname, NULL, NULL);
PQclear(res);
count++;
}
***************
*** 1362,1379 ****
{
PGresult *res;
! if (server_version >= 80100)
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
else
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
appendStringLiteralConn(buf, username, conn);
res = executeQuery(conn, buf->data);
if (PQntuples(res) == 1 &&
!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username);
PQclear(res);
count++;
}
--- 1380,1403 ----
{
PGresult *res;
! if (server_version >= 80500)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setdatabase = 0 AND setrole = "
! "(SELECT oid FROM pg_authid WHERE rolname = ", count);
! else if (server_version >= 80100)
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
else
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
appendStringLiteralConn(buf, username, conn);
+ if (server_version >= 80500)
+ appendPQExpBuffer(buf, ")");
res = executeQuery(conn, buf->data);
if (PQntuples(res) == 1 &&
!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username, NULL, NULL);
PQclear(res);
count++;
}
***************
*** 1388,1400 ****
}
/*
* Helper function for dumpXXXConfig().
*/
static void
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name)
{
char *pos;
char *mine;
--- 1412,1458 ----
}
+ /*
+ * Dump user-and-database-specific configuration
+ */
+ static void
+ dumpDbRoleConfig(PGconn *conn)
+ {
+ PQExpBuffer buf = createPQExpBuffer();
+ PGresult *res;
+ int i;
+
+ printfPQExpBuffer(buf, "SELECT rolname, datname, unnest(setconfig) "
+ "FROM pg_db_role_setting, pg_authid, pg_database "
+ "WHERE setrole = pg_authid.oid AND setdatabase = pg_database.oid");
+ res = executeQuery(conn, buf->data);
+
+ if (PQntuples(res) > 0)
+ {
+ fprintf(OPF, "--\n-- Per-Database Role Settings \n--\n\n");
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ makeAlterConfigCommand(conn, PQgetvalue(res, i, 2),
+ "ROLE", PQgetvalue(res, i, 0),
+ "DATABASE", PQgetvalue(res, i, 1));
+ }
+
+ fprintf(OPF, "\n\n");
+ }
+
+ PQclear(res);
+ destroyPQExpBuffer(buf);
+ }
+
/*
* Helper function for dumpXXXConfig().
*/
static void
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name,
! const char *type2, const char *name2)
{
char *pos;
char *mine;
***************
*** 1407,1412 ****
--- 1465,1472 ----
*pos = 0;
appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
+ if (type2 != NULL && name2 != NULL)
+ appendPQExpBuffer(buf, "IN %s %s ", type2, fmtId(name2));
appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
/*
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera <alvherre@commandprompt.com> writes:
And here's the last necessary bit, which is pg_dump support for all
this.
+ /* Dump role/database settings */ + if (!tablespaces_only) + { + if (server_version >= 80500) + dumpDbRoleConfig(conn); + }
Hmm ... I would kind of think that --roles-only should suppress this too.
Otherwise you're going to be dumping commands that might refer to
nonexistent databases.
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
Tom Lane escribi�:
Alvaro Herrera <alvherre@commandprompt.com> writes:
And here's the last necessary bit, which is pg_dump support for all
this.+ /* Dump role/database settings */ + if (!tablespaces_only) + { + if (server_version >= 80500) + dumpDbRoleConfig(conn); + }Hmm ... I would kind of think that --roles-only should suppress this too.
Otherwise you're going to be dumping commands that might refer to
nonexistent databases.
Those double negatives are confusing as hell. I propose to add
something like this:
do_tablespaces = true;
do_databases = true;
do_roles = true;
if (globals_only)
do_databases = false;
if (tablespaces_only)
{
do_roles = false;
do_databases = false;
}
if (roles_only)
{
do_databases = false;
do_tablespaces = false;
}
Then we can have the new block this way:
/* Dump role/database settings */
if (do_databases && do_roles)
{
if (server_version >= 80500)
dumpDbRoleConfig(conn);
}
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: TODO item: Allow more complex user/database default GUC settings
Tom Lane escribi�:
Alvaro Herrera <alvherre@commandprompt.com> writes:
And here's the last necessary bit, which is pg_dump support for all
this.+ /* Dump role/database settings */ + if (!tablespaces_only) + { + if (server_version >= 80500) + dumpDbRoleConfig(conn); + }Hmm ... I would kind of think that --roles-only should suppress this too.
Otherwise you're going to be dumping commands that might refer to
nonexistent databases.
Hmm. The problem I have with this idea is that the only way to dump the
per-database role settings is if you are also dumping the contents of
all databases. Which seems like a pain to me because the usage I
usually recommend is to backup global objects with pg_dumpall -g.
I wonder if pg_dumpall should have a method for dumping database
creation and settings, excluding contents (leaving that for plain
pg_dump).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera <alvherre@commandprompt.com> writes:
Tom Lane escribi�:
Hmm ... I would kind of think that --roles-only should suppress this too.
Otherwise you're going to be dumping commands that might refer to
nonexistent databases.
Hmm. The problem I have with this idea is that the only way to dump the
per-database role settings is if you are also dumping the contents of
all databases. Which seems like a pain to me because the usage I
usually recommend is to backup global objects with pg_dumpall -g.
Huh? --globals-only would still dump them, no?
I wonder if pg_dumpall should have a method for dumping database
creation and settings, excluding contents (leaving that for plain
pg_dump).
Perhaps. People keep speculating about refactoring the division of
labor between pg_dump and pg_dumpall. I'd advise leaving that for
a separate patch though ...
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
On Wed, Sep 30, 2009 at 10:34 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
And here's the last necessary bit, which is pg_dump support for all
this.
I think it would be helpful if you could post ONE patch with all the
changes and all the new files in the diff. AIUI, "the" patch is now
split across three separate emails. :-(
...Robert
Re: TODO item: Allow more complex user/database default GUC settings
Robert Haas escribi�:
On Wed, Sep 30, 2009 at 10:34 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:And here's the last necessary bit, which is pg_dump support for all
this.I think it would be helpful if you could post ONE patch with all the
changes and all the new files in the diff. AIUI, "the" patch is now
split across three separate emails. :-(
That's correct, here it is.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Attachments:
settings-4.patchtext/x-diff; charset=us-asciiDownload
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.207
diff -c -p -r2.207 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 22 Sep 2009 23:43:37 -0000 2.207
--- doc/src/sgml/catalogs.sgml 30 Sep 2009 13:36:03 -0000
***************
*** 199,204 ****
--- 199,209 ----
</row>
<row>
+ <entry><link linkend="catalog-pg-db-role-setting"><structname>pg_db_role_setting</structname></link></entry>
+ <entry>per-role and per-database settings</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
<entry>dependencies on shared objects</entry>
</row>
***************
*** 2132,2144 ****
</row>
<row>
- <entry><structfield>datconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>datacl</structfield></entry>
<entry><type>aclitem[]</type></entry>
<entry></entry>
--- 2137,2142 ----
***************
*** 4014,4019 ****
--- 4012,4076 ----
</sect1>
+ <sect1 id="catalog-pg-db-role-setting">
+ <title><structname>pg_db_role_setting</structname></title>
+
+ <indexterm zone="catalog-pg-db-role-setting">
+ <primary>pg_db_role_setting</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_db_role_setting</structname> records the default
+ values that have been set for run-time configuration variables,
+ for each role and database combination.
+ </para>
+
+ <para>
+ Unlike most system catalogs, <structname>pg_db_role_setting</structname>
+ is shared across all databases of a cluster: there is only one
+ copy of <structname>pg_db_role_setting</structname> per cluster, not
+ one per database.
+ </para>
+
+ <table>
+ <title><structname>pg_db_role_setting</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>setdatabase</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-database"><structname>pg_database</structname></link>.oid</literal></entry>
+ <entry>The OID of the database the setting is applicable to, or zero if not database-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setrole</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
+ <entry>The OID of the role the setting is applicable to, or zero if not role-specific</entry>
+ </row>
+
+ <row>
+ <entry><structfield>setconfig</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry></entry>
+ <entry>Defaults for run-time configuration variables</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="catalog-pg-shdepend">
<title><structname>pg_shdepend</structname></title>
***************
*** 6466,6478 ****
</row>
<row>
- <entry><structfield>rolconfig</structfield></entry>
- <entry><type>text[]</type></entry>
- <entry></entry>
- <entry>Session defaults for run-time configuration variables</entry>
- </row>
-
- <row>
<entry><structfield>oid</structfield></entry>
<entry><type>oid</type></entry>
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
--- 6523,6528 ----
Index: src/backend/catalog/Makefile
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/catalog/Makefile,v
retrieving revision 1.71
diff -c -p -r1.71 Makefile
*** src/backend/catalog/Makefile 26 Aug 2009 22:24:43 -0000 1.71
--- src/backend/catalog/Makefile 30 Sep 2009 13:36:03 -0000
*************** include $(top_builddir)/src/Makefile.glo
*** 13,19 ****
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
! pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
--- 13,19 ----
OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_aggregate.o pg_constraint.o pg_conversion.o pg_depend.o pg_enum.o \
pg_inherits.o pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o \
! pg_db_role_setting.o pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
*************** POSTGRES_BKI_SRCS = $(addprefix $(top_sr
*** 32,38 ****
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
! pg_database.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
--- 32,38 ----
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_enum.h pg_namespace.h pg_conversion.h pg_depend.h \
! pg_database.h pg_db_role_setting.h pg_tablespace.h pg_pltemplate.h \
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h \
Index: src/backend/catalog/catalog.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/catalog/catalog.c,v
retrieving revision 1.83
diff -c -p -r1.83 catalog.c
*** src/backend/catalog/catalog.c 11 Jun 2009 14:48:54 -0000 1.83
--- src/backend/catalog/catalog.c 30 Sep 2009 13:36:03 -0000
***************
*** 31,36 ****
--- 31,37 ----
#include "catalog/pg_database.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_pltemplate.h"
+ #include "catalog/pg_db_role_setting.h"
#include "catalog/pg_shdepend.h"
#include "catalog/pg_shdescription.h"
#include "catalog/pg_tablespace.h"
*************** IsSharedRelation(Oid relationId)
*** 306,312 ****
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
! relationId == TableSpaceRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
--- 307,314 ----
relationId == PLTemplateRelationId ||
relationId == SharedDescriptionRelationId ||
relationId == SharedDependRelationId ||
! relationId == TableSpaceRelationId ||
! relationId == DbRoleSettingRelationId)
return true;
/* These are their indexes (see indexing.h) */
if (relationId == AuthIdRolnameIndexId ||
*************** IsSharedRelation(Oid relationId)
*** 320,326 ****
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
! relationId == TablespaceNameIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
--- 322,329 ----
relationId == SharedDependDependerIndexId ||
relationId == SharedDependReferenceIndexId ||
relationId == TablespaceOidIndexId ||
! relationId == TablespaceNameIndexId ||
! relationId == DbRoleSettingDatidRolidIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
if (relationId == PgAuthidToastTable ||
*************** IsSharedRelation(Oid relationId)
*** 328,334 ****
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
! relationId == PgShdescriptionToastIndex)
return true;
return false;
}
--- 331,339 ----
relationId == PgDatabaseToastTable ||
relationId == PgDatabaseToastIndex ||
relationId == PgShdescriptionToastTable ||
! relationId == PgShdescriptionToastIndex ||
! relationId == PgDbRoleSettingToastTable ||
! relationId == PgDbRoleSettingToastIndex)
return true;
return false;
}
Index: src/backend/catalog/pg_db_role_setting.c
===================================================================
RCS file: src/backend/catalog/pg_db_role_setting.c
diff -N src/backend/catalog/pg_db_role_setting.c
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/backend/catalog/pg_db_role_setting.c 30 Sep 2009 13:35:52 -0000
***************
*** 0 ****
--- 1,231 ----
+ /*
+ * pg_db_role_setting.c
+ * Routines to support manipulation of the pg_db_role_setting relation
+ *
+ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * $PostgreSQL$
+ */
+ #include "postgres.h"
+
+ #include "access/genam.h"
+ #include "access/heapam.h"
+ #include "access/htup.h"
+ #include "access/skey.h"
+ #include "catalog/indexing.h"
+ #include "catalog/pg_db_role_setting.h"
+ #include "utils/fmgroids.h"
+ #include "utils/rel.h"
+ #include "utils/tqual.h"
+
+ void
+ AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
+ {
+ char *valuestr;
+ HeapTuple tuple;
+ Relation rel;
+ ScanKeyData scankey[2];
+ SysScanDesc scan;
+
+ valuestr = ExtractSetVariableArgs(setstmt);
+
+ /* Get the old tuple, if any. */
+
+ rel = heap_open(DbRoleSettingRelationId, RowExclusiveLock);
+ ScanKeyInit(&scankey[0],
+ Anum_pg_db_role_setting_setdatabase,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(databaseid));
+ ScanKeyInit(&scankey[1],
+ Anum_pg_db_role_setting_setrole,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+ scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+ SnapshotNow, 2, scankey);
+ tuple = systable_getnext(scan);
+
+ /*
+ * There are three cases:
+ *
+ * - in RESET ALL, simply delete the pg_db_role_setting tuple (if any)
+ *
+ * - in other commands, if there's a tuple in pg_db_role_setting, update it;
+ * if it ends up empty, delete it
+ *
+ * - otherwise, insert a new pg_db_role_setting tuple, but only if the
+ * command is not RESET
+ */
+ if (setstmt->kind == VAR_RESET_ALL)
+ {
+ if (HeapTupleIsValid(tuple))
+ simple_heap_delete(rel, &tuple->t_self);
+ }
+ else if (HeapTupleIsValid(tuple))
+ {
+ Datum repl_val[Natts_pg_db_role_setting];
+ bool repl_null[Natts_pg_db_role_setting];
+ bool repl_repl[Natts_pg_db_role_setting];
+ HeapTuple newtuple;
+ Datum datum;
+ bool isnull;
+ ArrayType *a;
+
+ memset(repl_repl, false, sizeof(repl_repl));
+ repl_repl[Anum_pg_db_role_setting_setconfig - 1] = true;
+ repl_null[Anum_pg_db_role_setting_setconfig - 1] = false;
+
+ /* Extract old value of setconfig */
+ datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
+ RelationGetDescr(rel), &isnull);
+ a = isnull ? NULL : DatumGetArrayTypeP(datum);
+
+ /* Update (valuestr is NULL in RESET cases) */
+ if (valuestr)
+ a = GUCArrayAdd(a, setstmt->name, valuestr);
+ else
+ a = GUCArrayDelete(a, setstmt->name);
+
+ if (a)
+ {
+ repl_val[Anum_pg_db_role_setting_setconfig - 1] =
+ PointerGetDatum(a);
+
+ newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
+ repl_val, repl_null, repl_repl);
+ simple_heap_update(rel, &tuple->t_self, newtuple);
+
+ /* Update indexes */
+ CatalogUpdateIndexes(rel, newtuple);
+ }
+ else
+ simple_heap_delete(rel, &tuple->t_self);
+ }
+ else if (valuestr)
+ {
+ /* non-null valuestr means it's not RESET, so insert a new tuple */
+ HeapTuple newtuple;
+ Datum values[Natts_pg_db_role_setting];
+ bool nulls[Natts_pg_db_role_setting];
+ ArrayType *a;
+
+ memset(nulls, false, sizeof(nulls));
+
+ a = GUCArrayAdd(NULL, setstmt->name, valuestr);
+
+ values[Anum_pg_db_role_setting_setdatabase - 1] =
+ ObjectIdGetDatum(databaseid);
+ values[Anum_pg_db_role_setting_setrole - 1] = ObjectIdGetDatum(roleid);
+ values[Anum_pg_db_role_setting_setconfig - 1] = PointerGetDatum(a);
+ newtuple = heap_form_tuple(RelationGetDescr(rel), values, nulls);
+
+ simple_heap_insert(rel, newtuple);
+
+ /* Update indexes */
+ CatalogUpdateIndexes(rel, newtuple);
+ }
+
+ systable_endscan(scan);
+
+ /* Close pg_db_role_setting, but keep lock till commit */
+ heap_close(rel, NoLock);
+ }
+
+ /*
+ * Drop some settings from the catalog. These can be for a particular
+ * database, or for a particular role. (It is of course possible to do both
+ * too, but it doesn't make sense for current uses.)
+ */
+ void
+ DropSetting(Oid databaseid, Oid roleid)
+ {
+ Relation relsetting;
+ HeapScanDesc scan;
+ ScanKeyData keys[2];
+ HeapTuple tup;
+ int numkeys = 0;
+
+ relsetting = heap_open(DbRoleSettingRelationId, RowExclusiveLock);
+
+ if (OidIsValid(databaseid))
+ {
+ ScanKeyInit(&keys[numkeys],
+ Anum_pg_db_role_setting_setdatabase,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(databaseid));
+ numkeys++;
+ }
+ if (OidIsValid(roleid))
+ {
+ ScanKeyInit(&keys[numkeys],
+ Anum_pg_db_role_setting_setrole,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+ numkeys++;
+ }
+
+ scan = heap_beginscan(relsetting, SnapshotNow, numkeys, keys);
+ while (HeapTupleIsValid(tup = heap_getnext(scan, ForwardScanDirection)))
+ {
+ simple_heap_delete(relsetting, &tup->t_self);
+ }
+ heap_endscan(scan);
+
+ heap_close(relsetting, RowExclusiveLock);
+ }
+
+ /*
+ * Scan pg_db_role_setting looking for applicable settings, and load them on
+ * the current process.
+ *
+ * relsetting is pg_db_role_setting, already opened and locked.
+ *
+ * Note: we only consider setting for the exact databaseid/roleid combination.
+ * This probably needs to be called more than once, with InvalidOid passed as
+ * databaseid/roleid.
+ */
+ void
+ ApplySetting(Oid databaseid, Oid roleid, Relation relsetting, GucSource source)
+ {
+ SysScanDesc scan;
+ ScanKeyData keys[2];
+ HeapTuple tup;
+
+ ScanKeyInit(&keys[0],
+ Anum_pg_db_role_setting_setdatabase,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(databaseid));
+ ScanKeyInit(&keys[1],
+ Anum_pg_db_role_setting_setrole,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+
+ scan = systable_beginscan(relsetting, DbRoleSettingDatidRolidIndexId, true,
+ SnapshotNow, 2, keys);
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ bool isnull;
+ Datum datum;
+
+ datum = heap_getattr(tup, Anum_pg_db_role_setting_setconfig,
+ RelationGetDescr(relsetting), &isnull);
+ if (!isnull)
+ {
+ ArrayType *a = DatumGetArrayTypeP(datum);
+
+ /*
+ * We process all the options at SUSET level. We assume that the
+ * right to insert an option into pg_db_role_setting was checked
+ * when it was inserted.
+ */
+ ProcessGUCArray(a, PGC_SUSET, source, GUC_ACTION_SET);
+ }
+ }
+
+ systable_endscan(scan);
+ }
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.60
diff -c -p -r1.60 system_views.sql
*** src/backend/catalog/system_views.sql 7 Apr 2009 00:31:26 -0000 1.60
--- src/backend/catalog/system_views.sql 30 Sep 2009 13:36:03 -0000
*************** CREATE VIEW pg_roles AS
*** 18,38 ****
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
! rolconfig,
! oid
! FROM pg_authid;
CREATE VIEW pg_shadow AS
SELECT
rolname AS usename,
! oid AS usesysid,
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
! rolconfig AS useconfig
! FROM pg_authid
WHERE rolcanlogin;
REVOKE ALL on pg_shadow FROM public;
--- 18,40 ----
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
! setconfig as rolconfig,
! pg_authid.oid
! FROM pg_authid LEFT JOIN pg_db_role_setting s
! ON (pg_authid.oid = setrole AND setdatabase = 0);
CREATE VIEW pg_shadow AS
SELECT
rolname AS usename,
! pg_authid.oid AS usesysid,
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
! setconfig AS useconfig
! FROM pg_authid LEFT JOIN pg_db_role_setting s
! ON (pg_authid.oid = setrole AND setdatabase = 0)
WHERE rolcanlogin;
REVOKE ALL on pg_shadow FROM public;
*************** CREATE RULE pg_settings_n AS
*** 171,176 ****
--- 173,187 ----
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+ CREATE VIEW pg_db_role_settings AS
+ SELECT
+ datname AS database,
+ rolname AS role,
+ setconfig AS settings
+ FROM pg_db_role_setting AS s
+ LEFT JOIN pg_database ON pg_database.oid = setdatabase
+ LEFT JOIN pg_roles ON pg_roles.oid = setrole;
+
CREATE VIEW pg_timezone_abbrevs AS
SELECT * FROM pg_timezone_abbrevs();
Index: src/backend/commands/dbcommands.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.226
diff -c -p -r1.226 dbcommands.c
*** src/backend/commands/dbcommands.c 1 Sep 2009 02:54:51 -0000 1.226
--- src/backend/commands/dbcommands.c 30 Sep 2009 13:36:03 -0000
***************
*** 33,38 ****
--- 33,39 ----
#include "catalog/indexing.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+ #include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
***************
*** 50,56 ****
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
- #include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/pg_locale.h"
#include "utils/snapmgr.h"
--- 51,56 ----
*************** createdb(const CreatedbStmt *stmt)
*** 544,555 ****
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
! * We deliberately set datconfig and datacl to defaults (NULL), rather
! * than copying them from the template database. Copying datacl would be
! * a bad idea when the owner is not the same as the template's owner. It's
! * more debatable whether datconfig should be copied.
*/
- new_record_nulls[Anum_pg_database_datconfig - 1] = true;
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
--- 544,553 ----
new_record[Anum_pg_database_dattablespace - 1] = ObjectIdGetDatum(dst_deftablespace);
/*
! * We deliberately set datacl to default (NULL), rather
! * than copying it from the template database. Copying it would be
! * a bad idea when the owner is not the same as the template's owner.
*/
new_record_nulls[Anum_pg_database_datacl - 1] = true;
tuple = heap_form_tuple(RelationGetDescr(pg_database_rel),
*************** dropdb(const char *dbname, bool missing_
*** 821,826 ****
--- 819,829 ----
DeleteSharedComments(db_id, DatabaseRelationId);
/*
+ * Remove settings associated with this database
+ */
+ DropSetting(db_id, InvalidOid);
+
+ /*
* Remove shared dependency references for the database.
*/
dropDatabaseDependencies(db_id);
*************** AlterDatabase(AlterDatabaseStmt *stmt, b
*** 1397,1481 ****
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
! char *valuestr;
! HeapTuple tuple,
! newtuple;
! Relation rel;
! ScanKeyData scankey;
! SysScanDesc scan;
! Datum repl_val[Natts_pg_database];
! bool repl_null[Natts_pg_database];
! bool repl_repl[Natts_pg_database];
!
! valuestr = ExtractSetVariableArgs(stmt->setstmt);
!
! /*
! * Get the old tuple. We don't need a lock on the database per se,
! * because we're not going to do anything that would mess up incoming
! * connections.
! */
! rel = heap_open(DatabaseRelationId, RowExclusiveLock);
! ScanKeyInit(&scankey,
! Anum_pg_database_datname,
! BTEqualStrategyNumber, F_NAMEEQ,
! NameGetDatum(stmt->dbname));
! scan = systable_beginscan(rel, DatabaseNameIndexId, true,
! SnapshotNow, 1, &scankey);
! tuple = systable_getnext(scan);
! if (!HeapTupleIsValid(tuple))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_DATABASE),
! errmsg("database \"%s\" does not exist", stmt->dbname)));
!
! if (!pg_database_ownercheck(HeapTupleGetOid(tuple), GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
! stmt->dbname);
!
! memset(repl_repl, false, sizeof(repl_repl));
! repl_repl[Anum_pg_database_datconfig - 1] = true;
!
! if (stmt->setstmt->kind == VAR_RESET_ALL)
! {
! /* RESET ALL, so just set datconfig to null */
! repl_null[Anum_pg_database_datconfig - 1] = true;
! repl_val[Anum_pg_database_datconfig - 1] = (Datum) 0;
! }
! else
! {
! Datum datum;
! bool isnull;
! ArrayType *a;
!
! repl_null[Anum_pg_database_datconfig - 1] = false;
!
! /* Extract old value of datconfig */
! datum = heap_getattr(tuple, Anum_pg_database_datconfig,
! RelationGetDescr(rel), &isnull);
! a = isnull ? NULL : DatumGetArrayTypeP(datum);
!
! /* Update (valuestr is NULL in RESET cases) */
! if (valuestr)
! a = GUCArrayAdd(a, stmt->setstmt->name, valuestr);
! else
! a = GUCArrayDelete(a, stmt->setstmt->name);
! if (a)
! repl_val[Anum_pg_database_datconfig - 1] = PointerGetDatum(a);
! else
! repl_null[Anum_pg_database_datconfig - 1] = true;
! }
!
! newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel),
! repl_val, repl_null, repl_repl);
! simple_heap_update(rel, &tuple->t_self, newtuple);
!
! /* Update indexes */
! CatalogUpdateIndexes(rel, newtuple);
!
! systable_endscan(scan);
!
! /* Close pg_database, but keep lock till commit */
! heap_close(rel, NoLock);
}
--- 1400,1421 ----
void
AlterDatabaseSet(AlterDatabaseSetStmt *stmt)
{
! Oid datid = get_database_oid(stmt->dbname);
! if (!OidIsValid(datid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_DATABASE),
! errmsg("database \"%s\" does not exist", stmt->dbname)));
!
! LockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
!
! if (!pg_database_ownercheck(datid, GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
! stmt->dbname);
!
! AlterSetting(datid, InvalidOid, stmt->setstmt);
!
! UnlockSharedObject(DatabaseRelationId, datid, 0, AccessShareLock);
}
Index: src/backend/commands/user.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/user.c,v
retrieving revision 1.188
diff -c -p -r1.188 user.c
*** src/backend/commands/user.c 1 Sep 2009 02:54:51 -0000 1.188
--- src/backend/commands/user.c 30 Sep 2009 13:36:03 -0000
***************
*** 19,25 ****
--- 19,27 ----
#include "catalog/indexing.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+ #include "catalog/pg_db_role_setting.h"
#include "commands/comment.h"
+ #include "commands/dbcommands.h"
#include "commands/user.h"
#include "libpq/md5.h"
#include "miscadmin.h"
***************
*** 27,33 ****
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
- #include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
--- 29,34 ----
*************** CreateRole(CreateRoleStmt *stmt)
*** 341,348 ****
else
new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = true;
- new_record_nulls[Anum_pg_authid_rolconfig - 1] = true;
-
tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
/*
--- 342,347 ----
*************** AlterRole(AlterRoleStmt *stmt)
*** 715,744 ****
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
! char *valuestr;
! HeapTuple oldtuple,
! newtuple;
! Relation rel;
! Datum repl_val[Natts_pg_authid];
! bool repl_null[Natts_pg_authid];
! bool repl_repl[Natts_pg_authid];
! valuestr = ExtractSetVariableArgs(stmt->setstmt);
! rel = heap_open(AuthIdRelationId, RowExclusiveLock);
! oldtuple = SearchSysCache(AUTHNAME,
! PointerGetDatum(stmt->role),
! 0, 0, 0);
! if (!HeapTupleIsValid(oldtuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
/*
* 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(oldtuple))->rolsuper)
{
if (!superuser())
ereport(ERROR,
--- 714,738 ----
void
AlterRoleSet(AlterRoleSetStmt *stmt)
{
! HeapTuple roletuple;
! Oid databaseid = InvalidOid;
! roletuple = SearchSysCache(AUTHNAME,
! PointerGetDatum(stmt->role),
! 0, 0, 0);
! if (!HeapTupleIsValid(roletuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("role \"%s\" does not exist", stmt->role)));
+ /* XXX need some kind of lock here */
+
/*
* 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,
*************** AlterRoleSet(AlterRoleSetStmt *stmt)
*** 748,801 ****
else
{
if (!have_createrole_privilege() &&
! HeapTupleGetOid(oldtuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
! memset(repl_repl, false, sizeof(repl_repl));
! repl_repl[Anum_pg_authid_rolconfig - 1] = true;
!
! if (stmt->setstmt->kind == VAR_RESET_ALL)
{
! /* RESET ALL, so just set rolconfig to null */
! repl_null[Anum_pg_authid_rolconfig - 1] = true;
! repl_val[Anum_pg_authid_rolconfig - 1] = (Datum) 0;
! }
! else
! {
! Datum datum;
! bool isnull;
! ArrayType *array;
!
! repl_null[Anum_pg_authid_rolconfig - 1] = false;
!
! /* Extract old value of rolconfig */
! datum = SysCacheGetAttr(AUTHNAME, oldtuple,
! Anum_pg_authid_rolconfig, &isnull);
! array = isnull ? NULL : DatumGetArrayTypeP(datum);
!
! /* Update (valuestr is NULL in RESET cases) */
! if (valuestr)
! array = GUCArrayAdd(array, stmt->setstmt->name, valuestr);
! else
! array = GUCArrayDelete(array, stmt->setstmt->name);
!
! if (array)
! repl_val[Anum_pg_authid_rolconfig - 1] = PointerGetDatum(array);
! else
! repl_null[Anum_pg_authid_rolconfig - 1] = true;
}
! newtuple = heap_modify_tuple(oldtuple, RelationGetDescr(rel),
! repl_val, repl_null, repl_repl);
!
! simple_heap_update(rel, &oldtuple->t_self, newtuple);
! CatalogUpdateIndexes(rel, newtuple);
!
! ReleaseSysCache(oldtuple);
! heap_close(rel, RowExclusiveLock);
}
--- 742,765 ----
else
{
if (!have_createrole_privilege() &&
! HeapTupleGetOid(roletuple) != GetUserId())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
}
! /* look up the database, if specified */
! if (stmt->database != NULL)
{
! databaseid = get_database_oid(stmt->database);
! if (!OidIsValid(databaseid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("database \"%s\" not found", stmt->database)));
}
! AlterSetting(databaseid, HeapTupleGetOid(roletuple), stmt->setstmt);
! ReleaseSysCache(roletuple);
}
*************** DropRole(DropRoleStmt *stmt)
*** 944,949 ****
--- 908,918 ----
DeleteSharedComments(roleid, AuthIdRelationId);
/*
+ * Remove settings for this role.
+ */
+ DropSetting(InvalidOid, roleid);
+
+ /*
* Advance command counter so that later iterations of this loop will
* see the changes already made. This is essential if, for example,
* we are trying to drop both a role and one of its direct members ---
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.438
diff -c -p -r1.438 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 22 Sep 2009 23:43:37 -0000 1.438
--- src/backend/nodes/copyfuncs.c 30 Sep 2009 13:36:03 -0000
*************** _copyAlterRoleSetStmt(AlterRoleSetStmt *
*** 3163,3168 ****
--- 3163,3169 ----
AlterRoleSetStmt *newnode = makeNode(AlterRoleSetStmt);
COPY_STRING_FIELD(role);
+ COPY_STRING_FIELD(database);
COPY_NODE_FIELD(setstmt);
return newnode;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.361
diff -c -p -r1.361 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 22 Sep 2009 23:43:38 -0000 1.361
--- src/backend/nodes/equalfuncs.c 30 Sep 2009 13:36:03 -0000
*************** static bool
*** 1716,1721 ****
--- 1716,1722 ----
_equalAlterRoleSetStmt(AlterRoleSetStmt *a, AlterRoleSetStmt *b)
{
COMPARE_STRING_FIELD(role);
+ COMPARE_STRING_FIELD(database);
COMPARE_NODE_FIELD(setstmt);
return true;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.679
diff -c -p -r2.679 gram.y
*** src/backend/parser/gram.y 22 Sep 2009 23:43:38 -0000 2.679
--- src/backend/parser/gram.y 30 Sep 2009 17:17:59 -0000
*************** static TypeName *TableFuncTypeName(List
*** 235,246 ****
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
! %type <list> OptRoleList
! %type <defelt> OptRoleElem
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
%type <str> auth_ident
%type <str> OptSchemaName
%type <list> OptSchemaEltList
--- 235,247 ----
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
! %type <list> OptRoleList AlterOptRoleList
! %type <defelt> CreateOptRoleElem AlterOptRoleElem
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
%type <str> auth_ident
+ %type <str> opt_in_database
%type <str> OptSchemaName
%type <list> OptSchemaEltList
*************** opt_with: WITH {}
*** 756,766 ****
* is "WITH ADMIN name".
*/
OptRoleList:
! OptRoleList OptRoleElem { $$ = lappend($1, $2); }
| /* EMPTY */ { $$ = NIL; }
;
! OptRoleElem:
PASSWORD Sconst
{
$$ = makeDefElem("password",
--- 757,772 ----
* is "WITH ADMIN name".
*/
OptRoleList:
! OptRoleList CreateOptRoleElem { $$ = lappend($1, $2); }
| /* EMPTY */ { $$ = NIL; }
;
! AlterOptRoleList:
! AlterOptRoleList AlterOptRoleElem { $$ = lappend($1, $2); }
! | /* EMPTY */ { $$ = NIL; }
! ;
!
! AlterOptRoleElem:
PASSWORD Sconst
{
$$ = makeDefElem("password",
*************** OptRoleElem:
*** 842,848 ****
{
$$ = makeDefElem("rolemembers", (Node *)$2);
}
! /* The following are not supported by ALTER ROLE/USER/GROUP */
| SYSID Iconst
{
$$ = makeDefElem("sysid", (Node *)makeInteger($2));
--- 848,858 ----
{
$$ = makeDefElem("rolemembers", (Node *)$2);
}
! ;
!
! CreateOptRoleElem:
! AlterOptRoleElem { $$ = $1; }
! /* The following are not supported by ALTER ROLE/USER/GROUP */
| SYSID Iconst
{
$$ = makeDefElem("sysid", (Node *)makeInteger($2));
*************** CreateUserStmt:
*** 891,897 ****
*****************************************************************************/
AlterRoleStmt:
! ALTER ROLE RoleId opt_with OptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
--- 901,907 ----
*****************************************************************************/
AlterRoleStmt:
! ALTER ROLE RoleId opt_with AlterOptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
*************** AlterRoleStmt:
*** 901,912 ****
}
;
AlterRoleSetStmt:
! ALTER ROLE RoleId SetResetClause
{
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
n->role = $3;
! n->setstmt = $4;
$$ = (Node *)n;
}
;
--- 911,928 ----
}
;
+ opt_in_database:
+ /* EMPTY */ { $$ = NULL; }
+ | IN_P DATABASE database_name { $$ = $3; }
+ ;
+
AlterRoleSetStmt:
! ALTER ROLE RoleId opt_in_database SetResetClause
{
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
n->role = $3;
! n->database = $4;
! n->setstmt = $5;
$$ = (Node *)n;
}
;
*************** AlterRoleSetStmt:
*** 919,925 ****
*****************************************************************************/
AlterUserStmt:
! ALTER USER RoleId opt_with OptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
--- 935,941 ----
*****************************************************************************/
AlterUserStmt:
! ALTER USER RoleId opt_with AlterOptRoleList
{
AlterRoleStmt *n = makeNode(AlterRoleStmt);
n->role = $3;
*************** AlterUserSetStmt:
*** 935,940 ****
--- 951,957 ----
{
AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt);
n->role = $3;
+ n->database = NULL;
n->setstmt = $4;
$$ = (Node *)n;
}
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.177
diff -c -p -r1.177 miscinit.c
*** src/backend/utils/init/miscinit.c 27 Aug 2009 16:59:38 -0000 1.177
--- src/backend/utils/init/miscinit.c 30 Sep 2009 13:36:03 -0000
*************** InitializeSessionUserId(const char *role
*** 392,399 ****
{
HeapTuple roleTup;
Form_pg_authid rform;
- Datum datum;
- bool isnull;
Oid roleid;
/*
--- 392,397 ----
*************** InitializeSessionUserId(const char *role
*** 470,493 ****
AuthenticatedUserIsSuperuser ? "on" : "off",
PGC_INTERNAL, PGC_S_OVERRIDE);
- /*
- * Set up user-specific configuration variables. This is a good place to
- * do it so we don't have to read pg_authid twice during session startup.
- */
- datum = SysCacheGetAttr(AUTHNAME, roleTup,
- Anum_pg_authid_rolconfig, &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_authid was checked when it was
- * inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_USER, GUC_ACTION_SET);
- }
-
ReleaseSysCache(roleTup);
}
--- 468,473 ----
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.197
diff -c -p -r1.197 postinit.c
*** src/backend/utils/init/postinit.c 1 Sep 2009 00:09:42 -0000 1.197
--- src/backend/utils/init/postinit.c 30 Sep 2009 13:36:03 -0000
***************
*** 27,32 ****
--- 27,33 ----
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_database.h"
+ #include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
#include "libpq/auth.h"
#include "libpq/libpq-be.h"
*************** static void CheckMyDatabase(const char *
*** 63,68 ****
--- 64,70 ----
static void InitCommunication(void);
static void ShutdownPostgres(int code, Datum arg);
static bool ThereIsAtLeastOneRole(void);
+ static void process_settings(Oid databaseid, Oid roleid);
/*** InitPostgres support ***/
*************** CheckMyDatabase(const char *name, bool a
*** 344,372 ****
pg_bind_textdomain_codeset(textdomain(NULL));
#endif
- /*
- * Lastly, set up any database-specific configuration variables.
- */
- if (IsUnderPostmaster)
- {
- Datum datum;
- bool isnull;
-
- datum = SysCacheGetAttr(DATABASEOID, tup, Anum_pg_database_datconfig,
- &isnull);
- if (!isnull)
- {
- ArrayType *a = DatumGetArrayTypeP(datum);
-
- /*
- * We process all the options at SUSET level. We assume that the
- * right to insert an option into pg_database was checked when it
- * was inserted.
- */
- ProcessGUCArray(a, PGC_SUSET, PGC_S_DATABASE, GUC_ACTION_SET);
- }
- }
-
ReleaseSysCache(tup);
}
--- 346,351 ----
*************** InitPostgres(const char *in_dbname, Oid
*** 739,744 ****
--- 718,726 ----
/* set up ACL framework (so CheckMyDatabase can check permissions) */
initialize_acl();
+ /* Process pg_db_role_setting options */
+ process_settings(MyDatabaseId, GetSessionUserId());
+
/*
* Re-read the pg_database row for our database, check permissions and
* set up database-specific GUC settings. We can't do this until all the
*************** InitPostgres(const char *in_dbname, Oid
*** 851,856 ****
--- 833,860 ----
CommitTransactionCommand();
}
+ /*
+ * Load GUC settings from pg_db_role_setting.
+ *
+ * We try specific settings for the database/role combination, as well as
+ * general for this database and for this user.
+ */
+ static void
+ process_settings(Oid databaseid, Oid roleid)
+ {
+ Relation relsetting;
+
+ if (!IsUnderPostmaster)
+ return;
+
+ relsetting = heap_open(DbRoleSettingRelationId, AccessShareLock);
+
+ ApplySetting(databaseid, roleid, relsetting, PGC_S_DATABASE_USER);
+ ApplySetting(InvalidOid, roleid, relsetting, PGC_S_USER);
+ ApplySetting(databaseid, InvalidOid, relsetting, PGC_S_DATABASE);
+
+ heap_close(relsetting, AccessShareLock);
+ }
/*
* Backend-shutdown callback. Do cleanup that we want to be sure happens
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.126
diff -c -p -r1.126 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 11 Jun 2009 14:49:07 -0000 1.126
--- src/bin/pg_dump/pg_dumpall.c 30 Sep 2009 16:24:52 -0000
*************** static void dropDBs(PGconn *conn);
*** 43,50 ****
static void dumpCreateDB(PGconn *conn);
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name);
static void dumpDatabases(PGconn *conn);
static void dumpTimestamp(char *msg);
static void doShellQuoting(PQExpBuffer buf, const char *str);
--- 43,52 ----
static void dumpCreateDB(PGconn *conn);
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
+ static void dumpDbRoleConfig(PGconn *conn);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name, const char *type2,
! const char *name2);
static void dumpDatabases(PGconn *conn);
static void dumpTimestamp(char *msg);
static void doShellQuoting(PQExpBuffer buf, const char *str);
*************** main(int argc, char *argv[])
*** 501,506 ****
--- 503,515 ----
/* Dump CREATE DATABASE commands */
if (!globals_only && !roles_only && !tablespaces_only)
dumpCreateDB(conn);
+
+ /* Dump role/database settings */
+ if (!tablespaces_only && !roles_only)
+ {
+ if (server_version >= 80500)
+ dumpDbRoleConfig(conn);
+ }
}
if (!globals_only && !roles_only && !tablespaces_only)
*************** dumpDatabaseConfig(PGconn *conn, const c
*** 1325,1339 ****
{
PGresult *res;
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";");
res = executeQuery(conn, buf->data);
! if (!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname);
PQclear(res);
count++;
}
--- 1334,1357 ----
{
PGresult *res;
! if (server_version >= 80500)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
! else
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
appendStringLiteralConn(buf, dbname, conn);
+
+ if (server_version >= 80500)
+ appendPQExpBuffer(buf, ")");
+
appendPQExpBuffer(buf, ";");
res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname, NULL, NULL);
PQclear(res);
count++;
}
*************** dumpUserConfig(PGconn *conn, const char
*** 1362,1379 ****
{
PGresult *res;
! if (server_version >= 80100)
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
else
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
appendStringLiteralConn(buf, username, conn);
res = executeQuery(conn, buf->data);
if (PQntuples(res) == 1 &&
!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username);
PQclear(res);
count++;
}
--- 1380,1403 ----
{
PGresult *res;
! if (server_version >= 80500)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setdatabase = 0 AND setrole = "
! "(SELECT oid FROM pg_authid WHERE rolname = ", count);
! else if (server_version >= 80100)
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
else
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
appendStringLiteralConn(buf, username, conn);
+ if (server_version >= 80500)
+ appendPQExpBuffer(buf, ")");
res = executeQuery(conn, buf->data);
if (PQntuples(res) == 1 &&
!PQgetisnull(res, 0, 0))
{
makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username, NULL, NULL);
PQclear(res);
count++;
}
*************** dumpUserConfig(PGconn *conn, const char
*** 1388,1400 ****
}
/*
* Helper function for dumpXXXConfig().
*/
static void
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name)
{
char *pos;
char *mine;
--- 1412,1458 ----
}
+ /*
+ * Dump user-and-database-specific configuration
+ */
+ static void
+ dumpDbRoleConfig(PGconn *conn)
+ {
+ PQExpBuffer buf = createPQExpBuffer();
+ PGresult *res;
+ int i;
+
+ printfPQExpBuffer(buf, "SELECT rolname, datname, unnest(setconfig) "
+ "FROM pg_db_role_setting, pg_authid, pg_database "
+ "WHERE setrole = pg_authid.oid AND setdatabase = pg_database.oid");
+ res = executeQuery(conn, buf->data);
+
+ if (PQntuples(res) > 0)
+ {
+ fprintf(OPF, "--\n-- Per-Database Role Settings \n--\n\n");
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ makeAlterConfigCommand(conn, PQgetvalue(res, i, 2),
+ "ROLE", PQgetvalue(res, i, 0),
+ "DATABASE", PQgetvalue(res, i, 1));
+ }
+
+ fprintf(OPF, "\n\n");
+ }
+
+ PQclear(res);
+ destroyPQExpBuffer(buf);
+ }
+
/*
* Helper function for dumpXXXConfig().
*/
static void
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
! const char *type, const char *name,
! const char *type2, const char *name2)
{
char *pos;
char *mine;
*************** makeAlterConfigCommand(PGconn *conn, con
*** 1407,1412 ****
--- 1465,1472 ----
*pos = 0;
appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
+ if (type2 != NULL && name2 != NULL)
+ appendPQExpBuffer(buf, "IN %s %s ", type2, fmtId(name2));
appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
/*
Index: src/include/catalog/indexing.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/indexing.h,v
retrieving revision 1.108
diff -c -p -r1.108 indexing.h
*** src/include/catalog/indexing.h 11 Jun 2009 14:49:09 -0000 1.108
--- src/include/catalog/indexing.h 30 Sep 2009 13:36:03 -0000
*************** DECLARE_UNIQUE_INDEX(pg_user_mapping_oid
*** 267,272 ****
--- 267,275 ----
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
+ DECLARE_UNIQUE_INDEX(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops));
+ #define DbRoleSettingDatidRolidIndexId 2965
+
/* last step of initialization script: build the indexes declared above */
BUILD_INDICES
Index: src/include/catalog/pg_attribute.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.153
diff -c -p -r1.153 pg_attribute.h
*** src/include/catalog/pg_attribute.h 26 Sep 2009 22:42:02 -0000 1.153
--- src/include/catalog/pg_attribute.h 30 Sep 2009 13:36:03 -0000
*************** DATA(insert ( 1259 tableoid 26 0 0 4
*** 486,493 ****
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
! { 1262, {"datconfig"}, 1009, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \
! { 1262, {"datacl"}, 1034, -1, 0, -1, 13, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
--- 486,492 ----
{ 1262, {"datlastsysoid"}, 26, -1, 0, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"datfrozenxid"}, 28, -1, 0, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
{ 1262, {"dattablespace"}, 26, -1, 0, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \
! { 1262, {"datacl"}, 1034, -1, 0, -1, 12, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }
/* ----------------
* pg_index
Index: src/include/catalog/pg_authid.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/pg_authid.h,v
retrieving revision 1.9
diff -c -p -r1.9 pg_authid.h
*** src/include/catalog/pg_authid.h 1 Jan 2009 17:23:56 -0000 1.9
--- src/include/catalog/pg_authid.h 30 Sep 2009 13:36:03 -0000
*************** CATALOG(pg_authid,1260) BKI_SHARED_RELAT
*** 55,61 ****
/* remaining fields may be null; use heap_getattr to read them! */
text rolpassword; /* password, if any */
timestamptz rolvaliduntil; /* password expiration time, if any */
- text rolconfig[1]; /* GUC settings to apply at login */
} FormData_pg_authid;
#undef timestamptz
--- 55,60 ----
*************** typedef FormData_pg_authid *Form_pg_auth
*** 83,89 ****
#define Anum_pg_authid_rolconnlimit 8
#define Anum_pg_authid_rolpassword 9
#define Anum_pg_authid_rolvaliduntil 10
- #define Anum_pg_authid_rolconfig 11
/* ----------------
* initial contents of pg_authid
--- 82,87 ----
*************** typedef FormData_pg_authid *Form_pg_auth
*** 92,98 ****
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 90,96 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
Index: src/include/catalog/pg_database.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/pg_database.h,v
retrieving revision 1.50
diff -c -p -r1.50 pg_database.h
*** src/include/catalog/pg_database.h 26 Sep 2009 22:42:02 -0000 1.50
--- src/include/catalog/pg_database.h 30 Sep 2009 13:36:03 -0000
*************** CATALOG(pg_database,1262) BKI_SHARED_REL
*** 42,48 ****
Oid datlastsysoid; /* highest OID to consider a system OID */
TransactionId datfrozenxid; /* all Xids < this are frozen in this DB */
Oid dattablespace; /* default table space for this DB */
- text datconfig[1]; /* database-specific GUC (VAR LENGTH) */
aclitem datacl[1]; /* access permissions (VAR LENGTH) */
} FormData_pg_database;
--- 42,47 ----
*************** typedef FormData_pg_database *Form_pg_da
*** 57,63 ****
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 13
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
--- 56,62 ----
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 12
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
*************** typedef FormData_pg_database *Form_pg_da
*** 69,78 ****
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
! #define Anum_pg_database_datconfig 12
! #define Anum_pg_database_datacl 13
! DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_ _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
--- 68,76 ----
#define Anum_pg_database_datlastsysoid 9
#define Anum_pg_database_datfrozenxid 10
#define Anum_pg_database_dattablespace 11
! #define Anum_pg_database_datacl 12
! DATA(insert OID = 1 ( template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_));
SHDESCR("default template database");
#define TemplateDbOid 1
Index: src/include/catalog/pg_db_role_setting.h
===================================================================
RCS file: src/include/catalog/pg_db_role_setting.h
diff -N src/include/catalog/pg_db_role_setting.h
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/include/catalog/pg_db_role_setting.h 30 Sep 2009 13:35:54 -0000
***************
*** 0 ****
--- 1,67 ----
+ /*-------------------------------------------------------------------------
+ *
+ * pg_db_role_setting.h
+ * definition of configuration settings
+ *
+ *
+ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * $PostgreSQL$
+ *
+ * NOTES
+ * the genbki.sh script reads this file and generates .bki
+ * information from the DATA() statements.
+ *
+ * XXX do NOT break up DATA() statements into multiple lines!
+ * the scripts are not as smart as you might think...
+ *
+ *-------------------------------------------------------------------------
+ */
+ #ifndef PG_DB_ROLE_SETTING_H
+ #define PG_DB_ROLE_SETTING_H
+
+ #include "catalog/genbki.h"
+ #include "nodes/parsenodes.h"
+ #include "utils/guc.h"
+ #include "utils/relcache.h"
+
+ /* ----------------
+ * pg_db_role_setting definition. cpp turns this into
+ * typedef struct FormData_pg_db_role_setting
+ * ----------------
+ */
+ #define DbRoleSettingRelationId 2964
+
+ CATALOG(pg_db_role_setting,2964) BKI_SHARED_RELATION BKI_WITHOUT_OIDS
+ {
+ Oid setdatabase; /* database */
+ Oid setrole; /* role */
+ text setconfig[1]; /* GUC settings to apply at login */
+ } FormData_pg_db_role_setting;
+
+ typedef FormData_pg_db_role_setting *Form_pg_db_role_setting;
+
+ /* ----------------
+ * compiler constants for pg_db_role_setting
+ * ----------------
+ */
+ #define Natts_pg_db_role_setting 3
+ #define Anum_pg_db_role_setting_setdatabase 1
+ #define Anum_pg_db_role_setting_setrole 2
+ #define Anum_pg_db_role_setting_setconfig 3
+
+ /* ----------------
+ * initial contents of pg_db_role_setting are NOTHING
+ * ----------------
+ */
+
+ /*
+ * prototypes for functions in pg_db_role_setting.h
+ */
+ extern void AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt);
+ extern void DropSetting(Oid databaseid, Oid roleid);
+ extern void ApplySetting(Oid databaseid, Oid roleid, Relation relsetting,
+ GucSource source);
+
+ #endif /* PG_DB_ROLE_SETTING_H */
Index: src/include/catalog/toasting.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/catalog/toasting.h,v
retrieving revision 1.8
diff -c -p -r1.8 toasting.h
*** src/include/catalog/toasting.h 11 Jun 2009 20:46:11 -0000 1.8
--- src/include/catalog/toasting.h 30 Sep 2009 13:36:03 -0000
*************** DECLARE_TOAST(pg_database, 2844, 2845);
*** 58,62 ****
--- 58,65 ----
DECLARE_TOAST(pg_shdescription, 2846, 2847);
#define PgShdescriptionToastTable 2846
#define PgShdescriptionToastIndex 2847
+ DECLARE_TOAST(pg_db_role_setting, 2966, 2967);
+ #define PgDbRoleSettingToastTable 2966
+ #define PgDbRoleSettingToastIndex 2967
#endif /* TOASTING_H */
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.402
diff -c -p -r1.402 parsenodes.h
*** src/include/nodes/parsenodes.h 22 Sep 2009 23:43:41 -0000 1.402
--- src/include/nodes/parsenodes.h 30 Sep 2009 13:36:03 -0000
*************** typedef struct AlterRoleSetStmt
*** 1619,1624 ****
--- 1619,1625 ----
{
NodeTag type;
char *role; /* role name */
+ char *database; /* database name, or NULL */
VariableSetStmt *setstmt; /* SET or RESET subcommand */
} AlterRoleSetStmt;
Index: src/include/utils/guc.h
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/utils/guc.h,v
retrieving revision 1.105
diff -c -p -r1.105 guc.h
*** src/include/utils/guc.h 22 Sep 2009 23:43:41 -0000 1.105
--- src/include/utils/guc.h 30 Sep 2009 13:36:03 -0000
*************** typedef enum
*** 86,91 ****
--- 86,92 ----
PGC_S_ARGV, /* postmaster command line */
PGC_S_DATABASE, /* per-database setting */
PGC_S_USER, /* per-user setting */
+ PGC_S_DATABASE_USER, /* per-user-and-database setting */
PGC_S_CLIENT, /* from client connection request */
PGC_S_OVERRIDE, /* special case to forcibly set default */
PGC_S_INTERACTIVE, /* dividing line for error reporting */
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.150
diff -c -p -r1.150 rules.out
*** src/test/regress/expected/rules.out 22 Sep 2009 15:46:34 -0000 1.150
--- src/test/regress/expected/rules.out 30 Sep 2009 13:36:03 -0000
*************** SELECT viewname, definition FROM pg_view
*** 1280,1294 ****
--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid;
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
--- 1280,1295 ----
--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
+ pg_db_role_settings | SELECT pg_database.datname AS database, pg_roles.rolname AS role, s.setconfig AS settings FROM ((pg_db_role_setting s LEFT JOIN pg_database ON ((pg_database.oid = s.setdatabase))) LEFT JOIN pg_roles ON ((pg_roles.oid = s.setrole)));
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
*************** SELECT viewname, definition FROM pg_view
*** 1329,1335 ****
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp;
! (51 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1330,1336 ----
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp;
! (52 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
Index: src/test/regress/expected/sanity_check.out
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/test/regress/expected/sanity_check.out,v
retrieving revision 1.40
diff -c -p -r1.40 sanity_check.out
*** src/test/regress/expected/sanity_check.out 29 Jul 2009 20:56:21 -0000 1.40
--- src/test/regress/expected/sanity_check.out 30 Sep 2009 13:36:04 -0000
*************** SELECT relname, relhasindex
*** 95,100 ****
--- 95,101 ----
pg_constraint | t
pg_conversion | t
pg_database | t
+ pg_db_role_setting | t
pg_depend | t
pg_description | t
pg_enum | t
*************** SELECT relname, relhasindex
*** 151,157 ****
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
! (140 rows)
--
-- another sanity check: every system catalog that has OIDs should have
--- 152,158 ----
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
! (141 rows)
--
-- another sanity check: every system catalog that has OIDs should have
Re: TODO item: Allow more complex user/database default GUC settings
Tom Lane escribi�:
BTW, have we thought much about the simplest possible solution,
which is to not have the view? How badly do we need it? Seems
like dropping the functionality into a psql \d command might be
a viable alternative.
FWIW I came up with a preliminary patch for a new psql command \dus that
shows settings. It takes a pattern that's used to constrain on roles.
Thus there is no way to view settings for a database. If there's a need
for that we could use another command, say \dls.
Sample output
alvherre=# \dus fo*
List of settings
role | database | settings
------+----------+-----------------------
fob | | log_duration=true
foo | alvherre | work_mem=256MB
: statement_timeout=10s
foo | | work_mem=512MB
: statement_timeout=1s
(3 rows)
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachments:
settings-psql.patchtext/x-diff; charset=us-asciiDownload
*** src/bin/psql/command.c 13 Sep 2009 22:18:22 -0000 1.207
--- src/bin/psql/command.c 30 Sep 2009 19:28:07 -0000
***************
*** 409,415 ****
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
! success = describeRoles(pattern, show_verbose);
break;
case 'F': /* text search subsystem */
switch (cmd[2])
--- 409,418 ----
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
! if (cmd[2] && cmd[2] == 's')
! success = listRoleSettings(pattern);
! else
! success = describeRoles(pattern, show_verbose);
break;
case 'F': /* text search subsystem */
switch (cmd[2])
*** src/bin/psql/describe.c 29 Jul 2009 20:56:19 -0000 1.226
--- src/bin/psql/describe.c 30 Sep 2009 19:54:42 -0000
***************
*** 2176,2181 ****
--- 2176,2232 ----
appendPQExpBufferStr(buf, str);
}
+ /*
+ * \dus
+ */
+ bool
+ listRoleSettings(const char *pattern)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+
+ if (pset.sversion >= 80500)
+ {
+ printfPQExpBuffer(&buf, "SELECT rolname AS role, datname AS database,\n"
+ "pg_catalog.array_to_string(setconfig, E'\\n') AS settings\n"
+ "FROM pg_db_role_setting AS s\n"
+ "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
+ "LEFT JOIN pg_roles ON pg_roles.oid = setrole ");
+ processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "pg_roles.rolname", NULL, NULL);
+ appendPQExpBufferStr(&buf, " ORDER BY role, database");
+ }
+ else
+ return false;
+
+ res = PSQLexec(buf.data, false);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0 && !pset.quiet)
+ {
+ if (pattern)
+ fprintf(pset.queryFout, _("No matching roles found.\n"));
+ else
+ fprintf(pset.queryFout, _("No settings found.\n"));
+ }
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of settings");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, pset.logfile);
+ }
+
+ PQclear(res);
+ resetPQExpBuffer(&buf);
+ return true;
+ }
+
/*
* listTables()
*** src/bin/psql/describe.h 21 Apr 2009 15:49:06 -0000 1.40
--- src/bin/psql/describe.h 30 Sep 2009 19:29:20 -0000
***************
*** 27,32 ****
--- 27,35 ----
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
+ /* \dus */
+ extern bool listRoleSettings(const char *pattern);
+
/* \z (or \dp) */
extern bool permissionsList(const char *pattern);
Re: TODO item: Allow more complex user/database default GUC settings
Alvaro Herrera <alvherre@commandprompt.com> writes:
FWIW I came up with a preliminary patch for a new psql command \dus that
shows settings. It takes a pattern that's used to constrain on roles.
Thus there is no way to view settings for a database. If there's a need
for that we could use another command, say \dls.
Why not two pattern arguments?
\drds [ role-pattern [ db-pattern ]]
Omitted patterns are presumed to be *
regards, tom lane
Re: TODO item: Allow more complex user/database default GUC settings
Tom Lane escribi�:
Alvaro Herrera <alvherre@commandprompt.com> writes:
FWIW I came up with a preliminary patch for a new psql command \dus that
shows settings. It takes a pattern that's used to constrain on roles.
Thus there is no way to view settings for a database. If there's a need
for that we could use another command, say \dls.Why not two pattern arguments?
\drds [ role-pattern [ db-pattern ]]
Hmm, interesting idea, patch attached. This required changing the API
of processSQLNamePattern to return a bool indicating whether a clause
was added; otherwise, when processing the second pattern it was
impossible to figure out if we needed a WHERE or not.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Attachments:
settings-psql-2.patchtext/x-diff; charset=us-asciiDownload
*** src/bin/pg_dump/dumputils.c 4 Aug 2009 21:56:08 -0000 1.48
--- src/bin/pg_dump/dumputils.c 30 Sep 2009 20:58:27 -0000
***************
*** 894,900 ****
*
* Scan a wildcard-pattern string and generate appropriate WHERE clauses
* to limit the set of objects returned. The WHERE clauses are appended
! * to the already-partially-constructed query in buf.
*
* conn: connection query will be sent to (consulted for escaping rules).
* buf: output parameter.
--- 894,901 ----
*
* Scan a wildcard-pattern string and generate appropriate WHERE clauses
* to limit the set of objects returned. The WHERE clauses are appended
! * to the already-partially-constructed query in buf. Returns whether
! * any clause was added.
*
* conn: connection query will be sent to (consulted for escaping rules).
* buf: output parameter.
***************
*** 913,919 ****
* Formatting note: the text already present in buf should end with a newline.
* The appended text, if any, will end with one too.
*/
! void
processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
--- 914,920 ----
* Formatting note: the text already present in buf should end with a newline.
* The appended text, if any, will end with one too.
*/
! bool
processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
***************
*** 925,933 ****
bool inquotes;
const char *cp;
int i;
#define WHEREAND() \
! (appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), have_where = true)
if (pattern == NULL)
{
--- 926,936 ----
bool inquotes;
const char *cp;
int i;
+ bool added_clause = false;
#define WHEREAND() \
! (appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), \
! have_where = true, added_clause = true)
if (pattern == NULL)
{
***************
*** 937,943 ****
WHEREAND();
appendPQExpBuffer(buf, "%s\n", visibilityrule);
}
! return;
}
initPQExpBuffer(&schemabuf);
--- 940,946 ----
WHEREAND();
appendPQExpBuffer(buf, "%s\n", visibilityrule);
}
! return added_clause;
}
initPQExpBuffer(&schemabuf);
***************
*** 1094,1098 ****
--- 1097,1102 ----
termPQExpBuffer(&schemabuf);
termPQExpBuffer(&namebuf);
+ return added_clause;
#undef WHEREAND
}
*** src/bin/pg_dump/dumputils.h 4 Aug 2009 21:56:08 -0000 1.25
--- src/bin/pg_dump/dumputils.h 30 Sep 2009 20:57:42 -0000
***************
*** 36,42 ****
const char *type, const char *acls, const char *owner,
int remoteVersion,
PQExpBuffer sql);
! extern void processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
--- 36,42 ----
const char *type, const char *acls, const char *owner,
int remoteVersion,
PQExpBuffer sql);
! extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
*** src/bin/psql/command.c 13 Sep 2009 22:18:22 -0000 1.207
--- src/bin/psql/command.c 30 Sep 2009 20:47:53 -0000
***************
*** 408,413 ****
--- 408,426 ----
case 's':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
+ case 'r':
+ if (cmd[2] == 'd' && cmd[3] == 's')
+ {
+ char *pattern2 = NULL;
+
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+ success = listDbRoleSettings(pattern, pattern2);
+ }
+ else
+ success = PSQL_CMD_UNKNOWN;
+ break;
case 'u':
success = describeRoles(pattern, show_verbose);
break;
*** src/bin/psql/describe.c 29 Jul 2009 20:56:19 -0000 1.226
--- src/bin/psql/describe.c 30 Sep 2009 20:59:13 -0000
***************
*** 2176,2181 ****
--- 2176,2240 ----
appendPQExpBufferStr(buf, str);
}
+ /*
+ * \drds
+ */
+ bool
+ listDbRoleSettings(const char *pattern, const char *pattern2)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+
+ if (pset.sversion >= 80500)
+ {
+ bool havewhere;
+
+ printfPQExpBuffer(&buf, "SELECT rolname AS role, datname AS database,\n"
+ "pg_catalog.array_to_string(setconfig, E'\\n') AS settings\n"
+ "FROM pg_db_role_setting AS s\n"
+ "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
+ "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n");
+ havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "pg_roles.rolname", NULL, NULL);
+ processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
+ NULL, "pg_database.datname", NULL, NULL);
+ appendPQExpBufferStr(&buf, "ORDER BY role, database");
+ }
+ else
+ {
+ fprintf(pset.queryFout,
+ _("No per-database role settings support in this server version.\n"));
+ return false;
+ }
+
+ res = PSQLexec(buf.data, false);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0 && !pset.quiet)
+ {
+ if (pattern)
+ fprintf(pset.queryFout, _("No matching settings found.\n"));
+ else
+ fprintf(pset.queryFout, _("No settings found.\n"));
+ }
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of settings");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, pset.logfile);
+ }
+
+ PQclear(res);
+ resetPQExpBuffer(&buf);
+ return true;
+ }
+
/*
* listTables()
*** src/bin/psql/describe.h 21 Apr 2009 15:49:06 -0000 1.40
--- src/bin/psql/describe.h 30 Sep 2009 20:34:54 -0000
***************
*** 27,32 ****
--- 27,35 ----
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
+ /* \drds */
+ extern bool listDbRoleSettings(const char *pattern1, const char *pattern2);
+
/* \z (or \dp) */
extern bool permissionsList(const char *pattern);
Re: TODO item: Allow more complex user/database default GUC settings
--On 30. September 2009 13:19:53 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:
I think it would be helpful if you could post ONE patch with all the
changes and all the new files in the diff. AIUI, "the" patch is now
split across three separate emails. :-(That's correct, here it is.
Some additional notes:
- ALTER ROLE ... IN DATABASE is missing some documentation. If you want, i
can work on this.
- The patch as is has still some locking problems (AlterRoleSet() has a XXX
about that): I've managed to create dead entries for a role or a database
in pg_db_role_setting while altering and dropping a role/database in two
concurrent sessions.
--
Thanks
Bernd
Re: TODO item: Allow more complex user/database default GUC settings
Bernd Helmle escribi�:
--On 30. September 2009 13:19:53 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:I think it would be helpful if you could post ONE patch with all the
changes and all the new files in the diff. AIUI, "the" patch is now
split across three separate emails. :-(That's correct, here it is.
Some additional notes:
- ALTER ROLE ... IN DATABASE is missing some documentation. If you
want, i can work on this.
Please.
- The patch as is has still some locking problems (AlterRoleSet()
has a XXX about that): I've managed to create dead entries for a
role or a database in pg_db_role_setting while altering and dropping
a role/database in two concurrent sessions.
Yeah, I was playing with that too. I think we need a few extra
LockSharedObject calls, and not only in the new code :-( (This troubles
me in the case of databases, because we already grab a lock on it during
connection establishing, so this could cause extra contention there.)
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: TODO item: Allow more complex user/database default GUC settings
--On 1. Oktober 2009 17:22:06 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:
- ALTER ROLE ... IN DATABASE is missing some documentation. If you
want, i can work on this.Please.
Here's a patch for this. I've kept it separately, so it's easier for you to
merge it into
your version.
--
Thanks
Bernd
Attachments:
alter_role_docs.patchapplication/octet-stream; name=alter_role_docs.patchDownload
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 67352c2..f0e7a9b 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -41,6 +41,10 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>co
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
+
+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> RESET <replaceable>configuration_parameter</replaceable>
+ALTER ROLE <replaceable class="PARAMETER">name</replaceable> IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
</synopsis>
</refsynopsisdiv>
@@ -80,14 +84,16 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
</para>
<para>
- The remaining variants change a role's session default for a
- specified configuration variable. Whenever the role subsequently
+ The remaining variants change a role's session default for a configuration variable
+ for all databases or, when used with the variant of <literal>IN DATABASE</literal>,
+ for a specific database. 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 postgres
command line. This only happens at login time, so configuration
settings associated with a role to which you've <xref
- linkend="sql-set-role" endterm="sql-set-role-title"> will be ignored.
+ linkend="sql-set-role" endterm="sql-set-role-title"> will be ignored. Settings set to
+ a role directly are overridden by any database specific settings attached to a role.
Superusers can change anyone's session defaults. Roles having
<literal>CREATEROLE</> privilege can change defaults for non-superuser
roles. Certain variables cannot be set this way, or can only be
@@ -146,6 +152,17 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
</varlistentry>
<varlistentry>
+ <term><replaceable>database_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the database the configuration variable should be set in. Database-Role-specific
+ configuration settings are always applied after any other Role-specific settings, thus overriding
+ any role settings with the same <replaceable>configuration_parameter</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable>configuration_parameter</replaceable></term>
<term><replaceable>value</replaceable></term>
<listitem>
@@ -159,6 +176,8 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
<literal>RESET ALL</literal> to clear all role-specific settings.
<literal>SET FROM CURRENT</> saves the session's current value of
the parameter as the role-specific value.
+ If used in conjunction with <literal>IN DATABASE</literal>, the configuration
+ parameter is set or removed for the given role and database only.
</para>
<para>
@@ -207,8 +226,9 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL
It is also possible to tie a
session default to a specific database rather than to a role; see
<xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">.
- Role-specific settings override database-specific
- ones if there is a conflict.
+ The rule how conflicting settings are applied to a session then is the following:
+ Database-Role-specific settings override Role-specific settings and Role-specific
+ settings override Database-specific ones if there is a conflict.
</para>
</refsect1>
@@ -263,6 +283,15 @@ ALTER ROLE miriam CREATEROLE CREATEDB;
ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
</programlisting>
</para>
+
+ <para>
+ Give a role a non-default, database-specific setting of the
+ <xref linkend="guc-client-min-messages"> parameter:
+
+<programlisting>
+ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
+</programlisting>
+ </para>
</refsect1>
<refsect1>
Re: TODO item: Allow more complex user/database default GUC settings
--On 1. Oktober 2009 17:22:06 -0400 Alvaro Herrera
<alvherre@commandprompt.com> wrote:
- The patch as is has still some locking problems (AlterRoleSet()
has a XXX about that): I've managed to create dead entries for a
role or a database in pg_db_role_setting while altering and dropping
a role/database in two concurrent sessions.Yeah, I was playing with that too. I think we need a few extra
LockSharedObject calls, and not only in the new code :-( (This troubles
me in the case of databases, because we already grab a lock on it during
connection establishing, so this could cause extra contention there.)
I have marked the patch as "Ready For Committer", so it can be taken by a
committer to help to resolve the remaining locking issue. There seems no
other issues left. If this is too hasty, i can set it back to whatever you
think its appropriate.
--
Thanks
Bernd