From 2eda8dfb52ed9962920282d8384da8bb4c22514d Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 7 Oct 2009 22:14:26 +0000 Subject: [PATCH] Make it possibly to specify GUC params per user and per database. Create a new catalog pg_db_role_setting where they are now stored, and better encapsulate the code that deals with settings into its realm. The old datconfig and rolconfig columns are removed. psql has gained a \drds command to display the settings. Backwards compatibility warning: while the backwards-compatible system views still have the config columns, they no longer completely represent the configuration for a user or database. Catalog version bumped. --- doc/src/sgml/catalogs.sgml | 80 +++++-- doc/src/sgml/ref/alter_role.sgml | 42 +++- src/backend/catalog/Makefile | 6 +- src/backend/catalog/catalog.c | 13 +- src/backend/catalog/pg_db_role_setting.c | 231 +++++++++++++++++++++ src/backend/catalog/pg_shdepend.c | 21 +- src/backend/catalog/system_views.sql | 16 +- src/backend/commands/dbcommands.c | 106 +++------- src/backend/commands/user.c | 91 +++----- src/backend/nodes/copyfuncs.c | 3 +- src/backend/nodes/equalfuncs.c | 3 +- src/backend/parser/gram.y | 37 +++- src/backend/utils/init/miscinit.c | 22 +- src/backend/utils/init/postinit.c | 52 ++--- src/bin/pg_dump/dumputils.c | 14 +- src/bin/pg_dump/dumputils.h | 4 +- src/bin/pg_dump/pg_dumpall.c | 76 ++++++- src/bin/psql/command.c | 15 +- src/bin/psql/describe.c | 61 +++++- src/bin/psql/describe.h | 5 +- src/include/catalog/catversion.h | 4 +- src/include/catalog/dependency.h | 4 +- src/include/catalog/indexing.h | 5 +- src/include/catalog/pg_attribute.h | 5 +- src/include/catalog/pg_authid.h | 6 +- src/include/catalog/pg_database.h | 10 +- src/include/catalog/pg_db_role_setting.h | 67 ++++++ src/include/catalog/toasting.h | 5 +- src/include/nodes/parsenodes.h | 3 +- src/include/utils/guc.h | 3 +- src/test/regress/expected/rules.out | 4 +- src/test/regress/expected/sanity_check.out | 3 +- 32 files changed, 738 insertions(+), 279 deletions(-) create mode 100644 src/backend/catalog/pg_db_role_setting.c create mode 100644 src/include/catalog/pg_db_role_setting.h diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 15dab71cc0..487dd7e169 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -203,6 +203,11 @@ query rewrite rules + + pg_db_role_setting + per-role and per-database settings + + pg_shdepend dependencies on shared objects @@ -2136,13 +2141,6 @@ - - datconfig - text[] - - Session defaults for run-time configuration variables - - datacl aclitem[] @@ -4106,6 +4104,65 @@ + + <structname>pg_db_role_setting</structname> + + + pg_db_role_setting + + + + The catalog pg_db_role_setting records the default + values that have been set for run-time configuration variables, + for each role and database combination. + + + + Unlike most system catalogs, pg_db_role_setting + is shared across all databases of a cluster: there is only one + copy of pg_db_role_setting per cluster, not + one per database. + + + + <structname>pg_db_role_setting</> Columns + + + + + Name + Type + References + Description + + + + + + setdatabase + oid + pg_database.oid + The OID of the database the setting is applicable to, or zero if not database-specific + + + + setrole + oid + pg_authid.oid + The OID of the role the setting is applicable to, or zero if not role-specific + + + + setconfig + text[] + + Defaults for run-time configuration variables + + + +
+ + <structname>pg_shdepend</structname> @@ -6557,13 +6614,6 @@ NULL if no expiration - - rolconfig - text[] - - Session defaults for run-time configuration variables - - oid oid diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml index 9be5812463..2d09de10c4 100644 --- a/doc/src/sgml/ref/alter_role.sgml +++ b/doc/src/sgml/ref/alter_role.sgml @@ -1,5 +1,5 @@ @@ -37,10 +37,10 @@ ALTER ROLE name [ [ WITH ] name RENAME TO new_name -ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT } -ALTER ROLE name SET configuration_parameter FROM CURRENT -ALTER ROLE name RESET configuration_parameter -ALTER ROLE name RESET ALL +ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } +ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT +ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter +ALTER ROLE name [ IN DATABASE database_name ] RESET ALL @@ -80,14 +80,16 @@ ALTER ROLE name RESET ALL - 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 the IN DATABASE clause is specified, + for the named database. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in 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 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 CREATEROLE privilege can change defaults for non-superuser roles. Certain variables cannot be set this way, or can only be @@ -145,6 +147,15 @@ ALTER ROLE name RESET ALL + + database_name + + + The name of the database the configuration variable should be set in. + + + + configuration_parameter value @@ -159,6 +170,8 @@ ALTER ROLE name RESET ALL RESET ALL to clear all role-specific settings. SET FROM CURRENT saves the session's current value of the parameter as the role-specific value. + If used in conjunction with IN DATABASE, the configuration + parameter is set or removed for the given role and database only. @@ -207,8 +220,8 @@ ALTER ROLE name RESET ALL It is also possible to tie a session default to a specific database rather than to a role; see . - Role-specific settings override database-specific - ones if there is a conflict. + If there is a conflict, database-role-specific settings override role-specific + ones, which in turn override database-specific ones. @@ -261,6 +274,15 @@ ALTER ROLE miriam CREATEROLE CREATEDB; ALTER ROLE worker_bee SET maintenance_work_mem = 100000; + + + + + Give a role a non-default, database-specific setting of the + parameter: + + +ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG; diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 53784e9c54..ec548990b1 100644 --- a/src/backend/catalog/Makefile +++ b/src/backend/catalog/Makefile @@ -2,7 +2,7 @@ # # Makefile for backend/catalog # -# $PostgreSQL: pgsql/src/backend/catalog/Makefile,v 1.72 2009/10/05 19:24:34 tgl Exp $ +# $PostgreSQL: pgsql/src/backend/catalog/Makefile,v 1.73 2009/10/07 22:14:16 alvherre Exp $ # #------------------------------------------------------------------------- @@ -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_db_role_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_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 \ diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c index 42371d5137..82d02f9609 100644 --- a/src/backend/catalog/catalog.c +++ b/src/backend/catalog/catalog.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/catalog.c,v 1.83 2009/06/11 14:48:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/catalog.c,v 1.84 2009/10/07 22:14:18 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -31,6 +31,7 @@ #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" @@ -306,7 +307,8 @@ IsSharedRelation(Oid relationId) relationId == PLTemplateRelationId || relationId == SharedDescriptionRelationId || relationId == SharedDependRelationId || - relationId == TableSpaceRelationId) + relationId == TableSpaceRelationId || + relationId == DbRoleSettingRelationId) 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 == DbRoleSettingDatidRolidIndexId) 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 == PgDbRoleSettingToastTable || + relationId == PgDbRoleSettingToastIndex) return true; return false; } diff --git a/src/backend/catalog/pg_db_role_setting.c b/src/backend/catalog/pg_db_role_setting.c new file mode 100644 index 0000000000..74870113d0 --- /dev/null +++ b/src/backend/catalog/pg_db_role_setting.c @@ -0,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: pgsql/src/backend/catalog/pg_db_role_setting.c,v 1.1 2009/10/07 22:14:18 alvherre Exp $ + */ +#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); +} diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c index 869ec1fdd5..be70143ea2 100644 --- a/src/backend/catalog/pg_shdepend.c +++ b/src/backend/catalog/pg_shdepend.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/pg_shdepend.c,v 1.35 2009/10/05 19:24:36 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/pg_shdepend.c,v 1.36 2009/10/07 22:14:18 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -31,6 +31,7 @@ #include "catalog/pg_shdepend.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_type.h" +#include "commands/dbcommands.h" #include "commands/conversioncmds.h" #include "commands/defrem.h" #include "commands/proclang.h" @@ -55,7 +56,6 @@ typedef enum static int getOidListDiff(Oid *list1, int nlist1, Oid *list2, int nlist2, Oid **diff); static Oid classIdGetDbId(Oid classId); -static void shdepLockAndCheckObject(Oid classId, Oid objectId); static void shdepChangeDep(Relation sdepRel, Oid classid, Oid objid, int32 objsubid, Oid refclassid, Oid refobjid, @@ -963,7 +963,7 @@ classIdGetDbId(Oid classId) * weren't looking. If the object has been dropped, this function * does not return! */ -static void +void shdepLockAndCheckObject(Oid classId, Oid objectId) { /* AccessShareLock should be OK, since we are not modifying the object */ @@ -1003,6 +1003,21 @@ shdepLockAndCheckObject(Oid classId, Oid objectId) } #endif + case DatabaseRelationId: + { + /* For lack of a syscache on pg_database, do this: */ + char *database = get_database_name(objectId); + + if (database == NULL) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("database %u was concurrently dropped", + objectId))); + pfree(database); + break; + } + + default: elog(ERROR, "unrecognized shared classId: %u", classId); } diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 73e391c896..c2fbfcdf0a 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.60 2009/04/07 00:31:26 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.61 2009/10/07 22:14:18 alvherre Exp $ */ CREATE VIEW pg_roles AS @@ -18,21 +18,23 @@ CREATE VIEW pg_roles AS rolconnlimit, '********'::text as rolpassword, rolvaliduntil, - rolconfig, - oid - FROM pg_authid; + 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, - oid AS usesysid, + pg_authid.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 + 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; diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 2e6edc4832..7df44c9ec4 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -13,7 +13,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.226 2009/09/01 02:54:51 alvherre Exp $ + * $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.227 2009/10/07 22:14:18 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -33,6 +33,7 @@ #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,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), @@ -820,6 +818,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. */ @@ -1397,85 +1400,26 @@ 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); + Oid datid = get_database_oid(stmt->dbname); + if (!OidIsValid(datid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_DATABASE), + errmsg("database \"%s\" does not exist", stmt->dbname))); + /* - * 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. + * Obtain a lock on the database and make sure it didn't go away in the + * meantime. */ - 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))); + shdepLockAndCheckObject(DatabaseRelationId, datid); - 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 (!pg_database_ownercheck(datid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE, + stmt->dbname); - 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); + 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 c157ead472..ef546cf360 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.188 2009/09/01 02:54:51 alvherre Exp $ + * $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.189 2009/10/07 22:14:19 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -19,7 +19,10 @@ #include "catalog/indexing.h" #include "catalog/pg_auth_members.h" #include "catalog/pg_authid.h" +#include "catalog/pg_database.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,7 +30,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 +343,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 +715,29 @@ 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; + Oid databaseid = InvalidOid; - 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))); + /* + * Obtain a lock on the role and make sure it didn't go away in the + * meantime. + */ + shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple)); + /* * To mess with a superuser you gotta be superuser; else you need * createrole, or just want to change your own settings */ - if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) + if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper) { if (!superuser()) ereport(ERROR, @@ -748,54 +747,25 @@ 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) + /* look up and lock the database, if specified */ + if (stmt->database != NULL) { - /* 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; + databaseid = get_database_oid(stmt->database); + if (!OidIsValid(databaseid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("database \"%s\" not found", stmt->database))); + shdepLockAndCheckObject(DatabaseRelationId, databaseid); } - 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(databaseid, HeapTupleGetOid(roletuple), stmt->setstmt); + ReleaseSysCache(roletuple); } @@ -943,6 +913,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, diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 7c3cb049b0..9319aa84c5 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.440 2009/10/06 00:55:26 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.441 2009/10/07 22:14:20 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -3174,6 +3174,7 @@ _copyAlterRoleSetStmt(AlterRoleSetStmt *from) AlterRoleSetStmt *newnode = makeNode(AlterRoleSetStmt); COPY_STRING_FIELD(role); + COPY_STRING_FIELD(database); COPY_NODE_FIELD(setstmt); return newnode; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index bf978f871d..6a61112b99 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -22,7 +22,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.363 2009/10/06 00:55:26 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.364 2009/10/07 22:14:20 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -1725,6 +1725,7 @@ static bool _equalAlterRoleSetStmt(AlterRoleSetStmt *a, AlterRoleSetStmt *b) { COMPARE_STRING_FIELD(role); + COMPARE_STRING_FIELD(database); COMPARE_NODE_FIELD(setstmt); return true; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index af2f080b63..ed265f516b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.680 2009/10/05 19:24:38 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.681 2009/10/07 22:14:21 alvherre Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -237,12 +237,13 @@ static TypeName *TableFuncTypeName(List *columns); opt_grant_grant_option opt_grant_admin_option opt_nowait opt_if_exists opt_with_data -%type OptRoleList -%type OptRoleElem +%type OptRoleList AlterOptRoleList +%type CreateOptRoleElem AlterOptRoleElem %type opt_type %type foreign_server_version opt_foreign_server_version %type auth_ident +%type opt_in_database %type OptSchemaName %type OptSchemaEltList @@ -762,11 +763,16 @@ opt_with: WITH {} * is "WITH ADMIN name". */ OptRoleList: - OptRoleList OptRoleElem { $$ = lappend($1, $2); } + OptRoleList CreateOptRoleElem { $$ = lappend($1, $2); } | /* EMPTY */ { $$ = NIL; } ; -OptRoleElem: +AlterOptRoleList: + AlterOptRoleList AlterOptRoleElem { $$ = lappend($1, $2); } + | /* EMPTY */ { $$ = NIL; } + ; + +AlterOptRoleElem: PASSWORD Sconst { $$ = makeDefElem("password", @@ -848,7 +854,11 @@ OptRoleElem: { $$ = makeDefElem("rolemembers", (Node *)$2); } - /* The following are not supported by ALTER ROLE/USER/GROUP */ + ; + +CreateOptRoleElem: + AlterOptRoleElem { $$ = $1; } + /* The following are not supported by ALTER ROLE/USER/GROUP */ | SYSID Iconst { $$ = makeDefElem("sysid", (Node *)makeInteger($2)); @@ -897,7 +907,7 @@ CreateUserStmt: *****************************************************************************/ AlterRoleStmt: - ALTER ROLE RoleId opt_with OptRoleList + ALTER ROLE RoleId opt_with AlterOptRoleList { AlterRoleStmt *n = makeNode(AlterRoleStmt); n->role = $3; @@ -907,12 +917,18 @@ AlterRoleStmt: } ; +opt_in_database: + /* EMPTY */ { $$ = NULL; } + | IN_P DATABASE database_name { $$ = $3; } + ; + AlterRoleSetStmt: - ALTER ROLE RoleId SetResetClause + ALTER ROLE RoleId opt_in_database SetResetClause { AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt); n->role = $3; - n->setstmt = $4; + n->database = $4; + n->setstmt = $5; $$ = (Node *)n; } ; @@ -925,7 +941,7 @@ AlterRoleSetStmt: *****************************************************************************/ AlterUserStmt: - ALTER USER RoleId opt_with OptRoleList + ALTER USER RoleId opt_with AlterOptRoleList { AlterRoleStmt *n = makeNode(AlterRoleStmt); n->role = $3; @@ -941,6 +957,7 @@ AlterUserSetStmt: { AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt); n->role = $3; + n->database = NULL; n->setstmt = $4; $$ = (Node *)n; } diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c index 85f1507ba3..f7a27b9e43 100644 --- a/src/backend/utils/init/miscinit.c +++ b/src/backend/utils/init/miscinit.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/init/miscinit.c,v 1.177 2009/08/27 16:59:38 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/init/miscinit.c,v 1.178 2009/10/07 22:14:22 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -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 5321afc1b8..b6c93c7f8e 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/init/postinit.c,v 1.197 2009/09/01 00:09:42 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/init/postinit.c,v 1.198 2009/10/07 22:14:23 alvherre Exp $ * * *------------------------------------------------------------------------- @@ -27,6 +27,7 @@ #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" @@ -63,6 +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 +346,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 +718,9 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username, /* 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 @@ -851,6 +833,28 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username, 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 diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index a9a2762570..f8aa9e64b9 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.49 2009/10/05 19:24:45 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.50 2009/10/07 22:14:24 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -942,7 +942,8 @@ AddAcl(PQExpBuffer aclbuf, const char *keyword, const char *subname) * * 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. + * 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. @@ -961,7 +962,7 @@ AddAcl(PQExpBuffer aclbuf, const char *keyword, const char *subname) * Formatting note: the text already present in buf should end with a newline. * The appended text, if any, will end with one too. */ -void +bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern, bool have_where, bool force_escape, const char *schemavar, const char *namevar, @@ -973,9 +974,11 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern, bool inquotes; const char *cp; int i; + bool added_clause = false; #define WHEREAND() \ - (appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), have_where = true) + (appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), \ + have_where = true, added_clause = true) if (pattern == NULL) { @@ -985,7 +988,7 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern, WHEREAND(); appendPQExpBuffer(buf, "%s\n", visibilityrule); } - return; + return added_clause; } initPQExpBuffer(&schemabuf); @@ -1142,5 +1145,6 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern, termPQExpBuffer(&schemabuf); termPQExpBuffer(&namebuf); + return added_clause; #undef WHEREAND } diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h index a5bfe1bcfd..0b73701eb8 100644 --- a/src/bin/pg_dump/dumputils.h +++ b/src/bin/pg_dump/dumputils.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.26 2009/10/05 19:24:45 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.27 2009/10/07 22:14:24 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -40,7 +40,7 @@ extern bool buildDefaultACLCommands(const char *type, const char *nspname, const char *acls, const char *owner, int remoteVersion, PQExpBuffer sql); -extern void processSQLNamePattern(PGconn *conn, PQExpBuffer buf, +extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern, bool have_where, bool force_escape, const char *schemavar, const char *namevar, diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index f0a4d67d2a..5567b07caf 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.127 2009/10/05 19:24:46 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.128 2009/10/07 22:14:24 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -43,8 +43,10 @@ static void dropDBs(PGconn *conn); 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 *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,6 +503,13 @@ main(int argc, char *argv[]) /* 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) @@ -1325,15 +1334,24 @@ dumpDatabaseConfig(PGconn *conn, const char *dbname) { PGresult *res; - printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count); + 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 (!PQgetisnull(res, 0, 0)) + if (PQntuples(res) == 1 && + !PQgetisnull(res, 0, 0)) { makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0), - "DATABASE", dbname); + "DATABASE", dbname, NULL, NULL); PQclear(res); count++; } @@ -1362,18 +1380,24 @@ dumpUserConfig(PGconn *conn, const char *username) { PGresult *res; - if (server_version >= 80100) + 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); + "ROLE", username, NULL, NULL); PQclear(res); count++; } @@ -1388,13 +1412,47 @@ dumpUserConfig(PGconn *conn, const char *username) } +/* + * 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 *type, const char *name, + const char *type2, const char *name2) { char *pos; char *mine; @@ -1407,6 +1465,8 @@ makeAlterConfigCommand(PGconn *conn, const char *arrayitem, *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)); /* diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index d94d8b80c5..cea3942f01 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -3,7 +3,7 @@ * * Copyright (c) 2000-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.208 2009/10/05 19:24:46 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.209 2009/10/07 22:14:24 alvherre Exp $ */ #include "postgres_fe.h" #include "command.h" @@ -411,6 +411,19 @@ exec_command(const char *cmd, 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; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 1644623812..0c49d812ee 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -8,7 +8,7 @@ * * Copyright (c) 2000-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.227 2009/10/05 19:24:46 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.228 2009/10/07 22:14:24 alvherre Exp $ */ #include "postgres_fe.h" @@ -2243,6 +2243,65 @@ add_role_attribute(PQExpBuffer buf, const char *const str) 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() diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 169ceb3739..aaef69d703 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -3,7 +3,7 @@ * * Copyright (c) 2000-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/describe.h,v 1.41 2009/10/05 19:24:46 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/psql/describe.h,v 1.42 2009/10/07 22:14:24 alvherre Exp $ */ #ifndef DESCRIBE_H #define DESCRIBE_H @@ -27,6 +27,9 @@ extern bool describeOperators(const char *pattern, bool showSystem); /* \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); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 1d3bfb0726..20eac6aa1c 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.541 2009/10/05 19:24:46 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.542 2009/10/07 22:14:24 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200910051 +#define CATALOG_VERSION_NO 200910071 #endif diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index 954d3808a8..5134479c3d 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/dependency.h,v 1.41 2009/10/05 19:24:48 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/dependency.h,v 1.42 2009/10/07 22:14:24 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -238,6 +238,8 @@ extern void updateAclDependencies(Oid classId, Oid objectId, int32 objectSubId, extern bool checkSharedDependencies(Oid classId, Oid objectId, char **detail_msg, char **detail_log_msg); +extern void shdepLockAndCheckObject(Oid classId, Oid objectId); + extern void copyTemplateDependencies(Oid templateDbId, Oid newDbId); extern void dropDatabaseDependencies(Oid databaseId); diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h index 0272334f2e..4f9f9e9c2a 100644 --- a/src/include/catalog/indexing.h +++ b/src/include/catalog/indexing.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/indexing.h,v 1.109 2009/10/05 19:24:48 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/indexing.h,v 1.110 2009/10/07 22:14:25 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -272,6 +272,9 @@ DECLARE_UNIQUE_INDEX(pg_default_acl_role_nsp_obj_index, 827, on pg_default_acl u DECLARE_UNIQUE_INDEX(pg_default_acl_oid_index, 828, on pg_default_acl using btree(oid oid_ops)); #define DefaultAclOidIndexId 828 +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 diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h index 1315e4241a..f5a737f9f0 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -8,7 +8,7 @@ * 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_attribute.h,v 1.153 2009/09/26 22:42:02 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.154 2009/10/07 22:14:25 alvherre Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -486,8 +486,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 660c3c7c14..a8fdcf748a 100644 --- a/src/include/catalog/pg_authid.h +++ b/src/include/catalog/pg_authid.h @@ -10,7 +10,7 @@ * 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_authid.h,v 1.9 2009/01/01 17:23:56 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_authid.h,v 1.10 2009/10/07 22:14:25 alvherre Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -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 730c23c863..e041fe1154 100644 --- a/src/include/catalog/pg_database.h +++ b/src/include/catalog/pg_database.h @@ -8,7 +8,7 @@ * 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_database.h,v 1.50 2009/09/26 22:42:02 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_database.h,v 1.51 2009/10/07 22:14:25 alvherre Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -42,7 +42,6 @@ CATALOG(pg_database,1262) BKI_SHARED_RELATION BKI_ROWTYPE_OID(1248) 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; @@ -57,7 +56,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 @@ -69,10 +68,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/pg_db_role_setting.h b/src/include/catalog/pg_db_role_setting.h new file mode 100644 index 0000000000..7c8e8224ae --- /dev/null +++ b/src/include/catalog/pg_db_role_setting.h @@ -0,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: pgsql/src/include/catalog/pg_db_role_setting.h,v 1.1 2009/10/07 22:14:25 alvherre 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_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 */ diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.h index 5f4a4a79e2..fb83aa75df 100644 --- a/src/include/catalog/toasting.h +++ b/src/include/catalog/toasting.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/toasting.h,v 1.8 2009/06/11 20:46:11 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/toasting.h,v 1.9 2009/10/07 22:14:25 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -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_db_role_setting, 2966, 2967); +#define PgDbRoleSettingToastTable 2966 +#define PgDbRoleSettingToastIndex 2967 #endif /* TOASTING_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b182dcfd53..5f0d763b68 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.404 2009/10/06 00:55:26 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.405 2009/10/07 22:14:26 alvherre Exp $ * *------------------------------------------------------------------------- */ @@ -1629,6 +1629,7 @@ typedef struct AlterRoleSetStmt { NodeTag type; char *role; /* role name */ + char *database; /* database name, or NULL */ VariableSetStmt *setstmt; /* SET or RESET subcommand */ } AlterRoleSetStmt; diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index ef93d0f27c..5c59b02e4f 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -7,7 +7,7 @@ * Copyright (c) 2000-2009, PostgreSQL Global Development Group * Written by Peter Eisentraut . * - * $PostgreSQL: pgsql/src/include/utils/guc.h,v 1.106 2009/10/03 18:04:57 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/guc.h,v 1.107 2009/10/07 22:14:26 alvherre Exp $ *-------------------------------------------------------------------- */ #ifndef 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 9244376a3b..9561a23559 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, 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, 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, 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; diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 1994edc905..fe0d93670f 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -95,6 +95,7 @@ SELECT relname, relhasindex pg_constraint | t pg_conversion | t pg_database | t + pg_db_role_setting | t pg_default_acl | t pg_depend | t pg_description | t @@ -152,7 +153,7 @@ SELECT relname, relhasindex timetz_tbl | f tinterval_tbl | f varchar_tbl | f -(141 rows) +(142 rows) -- -- another sanity check: every system catalog that has OIDs should have -- 2.40.0