Dear hackers,
Please find attached a preliminary patch to fix schema ownership on first
connection. It is for comments and advices as I still have doubts about
various how-and-where issues, thus it is not submitted to the patch list.
(1) It adds a new "datisinit" attribute to pg_database, which tells
whether the database initialization was performed or not.
The documentation is updated accordingly.
(2) This boolean is tested in postinit.c:ReverifyMyDatabase,
and InitializeDatabase is called if necessary.
(3) The routine updates pg_database datisinit, as well as pg_namespace
ownership and acl stuff. The acl item update part is not yet
appropriate: I'd like some answers before going on.
(4) Some validation is added. It passes for me.
My questions:
- is the place for the first connection housekeeping updates appropriate?
it seems so from ReverifyMyDatabase comments, but these are only comments.
- it is quite convenient to use SPI_* stuff for this very rare updates,
but I'm not that confident about the issue... On the other hand, the
all-C solution would result in a much longer and less obvious code.
- it is unclear to me whether it should be allowed to skip this under
some condition, when the database is accessed in "debug" mode from
a standalone postgres for instance?
- also I'm wondering how to react (what to do and how to do it) on
errors within or under these initialization. For instance, how to
be sure that the CurrentUser is reset as expected?
Thanks in advance for your answers and comments.
Have a nice day.
--
Fabien Coelho - [EMAIL PROTECTED]*** ./doc/src/sgml/catalogs.sgml.orig Mon Apr 5 12:06:40 2004
--- ./doc/src/sgml/catalogs.sgml Fri May 7 08:46:38 2004
***************
*** 1536,1541 ****
--- 1536,1552 ----
</row>
<row>
+ <entry><structfield>datisinit</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>
+ False when a database is just created but housekeeping initialization
+ tasks are not performed yet. On the first connection, the initialization
+ is performed and the boolean is turned to true.
+ </entry>
+ </row>
+
+ <row>
<entry><structfield>datlastsysoid</structfield></entry>
<entry><type>oid</type></entry>
<entry></entry>
*** ./src/backend/commands/dbcommands.c.orig Tue Apr 20 09:35:43 2004
--- ./src/backend/commands/dbcommands.c Fri May 7 21:52:38 2004
***************
*** 424,429 ****
--- 424,430 ----
new_record[Anum_pg_database_encoding - 1] = Int32GetDatum(encoding);
new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(false);
new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(true);
+ new_record[Anum_pg_database_datisinit - 1] = BoolGetDatum(false);
new_record[Anum_pg_database_datlastsysoid - 1] =
ObjectIdGetDatum(src_lastsysoid);
new_record[Anum_pg_database_datvacuumxid - 1] =
TransactionIdGetDatum(src_vacuumxid);
new_record[Anum_pg_database_datfrozenxid - 1] =
TransactionIdGetDatum(src_frozenxid);
*** ./src/backend/utils/init/postinit.c.orig Fri Dec 12 19:45:09 2003
--- ./src/backend/utils/init/postinit.c Sat May 8 11:05:36 2004
***************
*** 49,54 ****
--- 49,108 ----
/*** InitPostgres support ***/
+ #include "executor/spi.h"
+
+ /* Do housekeeping initializations if required, on first connection.
+ */
+ static void InitializeDatabase(const char * dbname)
+ {
+ /* su */
+ AclId saved_user = GetUserId();
+ SetUserId(1);
+
+ /* Querying in C is nice, but SQL is nicer.
+ * This is only done once in a lifetime of the database,
+ * so paying for the parser/optimiser cost is not that bad?
+ * What if that fails?
+ */
+ SetQuerySnapshot();
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ ereport(ERROR, (errmsg("SPI_connect failed")));
+
+ if (SPI_exec("UPDATE " SystemCatalogName "." DatabaseRelationName
+ " SET datisinit=TRUE"
+ " WHERE datname=CURRENT_DATABASE()"
+ " AND datisinit=FALSE" , 0) != SPI_OK_UPDATE)
+ ereport(ERROR, (errmsg("database initialization %s update failed",
+ DatabaseRelationName)));
+
+ if (SPI_processed==1)
+ {
+ /* ok, we have it! otherwise, some concurrent connection did it. */
+
+ if (SPI_exec("UPDATE " SystemCatalogName "." NamespaceRelationName
+ " SET nspowner=datdba,"
+ " nspacl=NULL "
+ // nspacl = aclitems_switch_grantor(nspacl,
datdba) -- ???
+ " FROM " SystemCatalogName "."
DatabaseRelationName " "
+ " WHERE nspname NOT LIKE"
+ "
ALL(ARRAY['pg_%','information_schema'])"
+ " AND datname=CURRENT_DATABASE()"
+ " AND nspowner!=datdba;", 0) !=
SPI_OK_UPDATE)
+ ereport(ERROR, (errmsg("database initialization %s update
failed",
+
NamespaceRelationName)));
+
+ if (SPI_processed>0)
+ ereport(LOG, /* don't bother the user about these details... */
+ (errmsg("database initialization schema owner
updates: %d",
+ SPI_processed)));
+ }
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ ereport(ERROR, (errmsg("SPI_finish failed")));
+
+ SetUserId(saved_user);
+ }
/* --------------------------------
* ReverifyMyDatabase
***************
*** 129,134 ****
--- 183,194 ----
errmsg("database \"%s\" is not currently accepting connections",
name)));
+ /* Do we need the housekeeping initialization of the database?
+ * could be skipped on standalone "panic" mode?
+ */
+ if (!dbform->datisinit)
+ InitializeDatabase(name);
+
/*
* OK, we're golden. Only other to-do item is to save the encoding
* info out of the pg_database tuple.
*** ./src/include/catalog/catname.h.orig Sat Nov 29 23:40:58 2003
--- ./src/include/catalog/catname.h Sat May 8 09:09:11 2004
***************
*** 14,19 ****
--- 14,20 ----
#ifndef CATNAME_H
#define CATNAME_H
+ #define SystemCatalogName "pg_catalog"
#define AggregateRelationName "pg_aggregate"
#define AccessMethodRelationName "pg_am"
*** ./src/include/catalog/catversion.h.orig Mon May 3 10:45:38 2004
--- ./src/include/catalog/catversion.h Sat May 8 10:43:47 2004
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200405020
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200405080
#endif
*** ./src/include/catalog/pg_attribute.h.orig Mon Apr 5 12:06:43 2004
--- ./src/include/catalog/pg_attribute.h Fri May 7 21:46:24 2004
***************
*** 287,299 ****
DATA(insert ( 1262 encoding 23 -1 4 3 0 -1 -1 t p i t f f t 0));
DATA(insert ( 1262 datistemplate 16 -1 1 4 0 -1 -1 t p c t f f t 0));
DATA(insert ( 1262 datallowconn 16 -1 1 5 0 -1 -1 t p c t f f t 0));
! DATA(insert ( 1262 datlastsysoid 26 -1 4 6 0 -1 -1 t p i t f f t 0));
! DATA(insert ( 1262 datvacuumxid 28 -1 4 7 0 -1 -1 t p i t f f t 0));
! DATA(insert ( 1262 datfrozenxid 28 -1 4 8 0 -1 -1 t p i t f f t 0));
/* do not mark datpath as toastable; GetRawDatabaseInfo won't cope */
! DATA(insert ( 1262 datpath 25 -1 -1 9 0 -1 -1 f p i t f f t 0));
! DATA(insert ( 1262 datconfig 1009 -1 -1 10 1 -1 -1 f x i f f f t 0));
! DATA(insert ( 1262 datacl 1034 -1 -1 11 1 -1 -1 f x i f f f t 0));
DATA(insert ( 1262 ctid 27 0 6 -1 0 -1 -1 f p s t f
f t 0));
DATA(insert ( 1262 oid 26 0 4 -2 0 -1 -1 t p i t f
f t 0));
DATA(insert ( 1262 xmin 28 0 4 -3 0 -1 -1 t p i t f
f t 0));
--- 287,300 ----
DATA(insert ( 1262 encoding 23 -1 4 3 0 -1 -1 t p i t f f t 0));
DATA(insert ( 1262 datistemplate 16 -1 1 4 0 -1 -1 t p c t f f t 0));
DATA(insert ( 1262 datallowconn 16 -1 1 5 0 -1 -1 t p c t f f t 0));
! DATA(insert ( 1262 datisinit 16 -1 1 6 0 -1 -1 t p c t f f t 0));
! DATA(insert ( 1262 datlastsysoid 26 -1 4 7 0 -1 -1 t p i t f f t 0));
! DATA(insert ( 1262 datvacuumxid 28 -1 4 8 0 -1 -1 t p i t f f t 0));
! DATA(insert ( 1262 datfrozenxid 28 -1 4 9 0 -1 -1 t p i t f f t 0));
/* do not mark datpath as toastable; GetRawDatabaseInfo won't cope */
! DATA(insert ( 1262 datpath 25 -1 -1 10 0 -1 -1 f p i t f f t 0));
! DATA(insert ( 1262 datconfig 1009 -1 -1 11 1 -1 -1 f x i f f f t 0));
! DATA(insert ( 1262 datacl 1034 -1 -1 12 1 -1 -1 f x i f f f t 0));
DATA(insert ( 1262 ctid 27 0 6 -1 0 -1 -1 f p s t f
f t 0));
DATA(insert ( 1262 oid 26 0 4 -2 0 -1 -1 t p i t f
f t 0));
DATA(insert ( 1262 xmin 28 0 4 -3 0 -1 -1 t p i t f
f t 0));
*** ./src/include/catalog/pg_class.h.orig Mon Apr 5 12:06:43 2004
--- ./src/include/catalog/pg_class.h Fri May 7 21:39:32 2004
***************
*** 146,152 ****
DESCR("");
DATA(insert OID = 1261 ( pg_group PGNSP 87 PGUID 0 1261 0 0 0 0 f t r 3
0 0 0 0 0 f f f f _null_ ));
DESCR("");
! DATA(insert OID = 1262 ( pg_database PGNSP 88 PGUID 0 1262 0 0 0 0 f t r 11 0 0 0
0 0 t f f f _null_ ));
DESCR("");
DATA(insert OID = 376 ( pg_xactlock PGNSP 0 PGUID 0 0 0 0 0 0 f t s 1 0 0
0 0 0 f f f f _null_ ));
DESCR("");
--- 146,152 ----
DESCR("");
DATA(insert OID = 1261 ( pg_group PGNSP 87 PGUID 0 1261 0 0 0 0 f t r 3
0 0 0 0 0 f f f f _null_ ));
DESCR("");
! DATA(insert OID = 1262 ( pg_database PGNSP 88 PGUID 0 1262 0 0 0 0 f t r 12 0 0 0
0 0 t f f f _null_ ));
DESCR("");
DATA(insert OID = 376 ( pg_xactlock PGNSP 0 PGUID 0 0 0 0 0 0 f t s 1 0 0
0 0 0 f f f f _null_ ));
DESCR("");
*** ./src/include/catalog/pg_database.h.orig Tue Feb 10 02:55:26 2004
--- ./src/include/catalog/pg_database.h Fri May 7 08:57:38 2004
***************
*** 38,43 ****
--- 38,44 ----
int4 encoding; /* character encoding */
bool datistemplate; /* allowed as CREATE DATABASE template? */
bool datallowconn; /* new connections allowed? */
+ bool datisinit; /* was it already initialized? */
Oid datlastsysoid; /* highest OID to consider a system
OID */
TransactionId datvacuumxid; /* all XIDs before this are vacuumed */
TransactionId datfrozenxid; /* all XIDs before this are frozen */
***************
*** 57,76 ****
* compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database 11
#define Anum_pg_database_datname 1
#define Anum_pg_database_datdba 2
#define Anum_pg_database_encoding 3
#define Anum_pg_database_datistemplate 4
#define Anum_pg_database_datallowconn 5
! #define Anum_pg_database_datlastsysoid 6
! #define Anum_pg_database_datvacuumxid 7
! #define Anum_pg_database_datfrozenxid 8
! #define Anum_pg_database_datpath 9
! #define Anum_pg_database_datconfig 10
! #define Anum_pg_database_datacl 11
! DATA(insert OID = 1 ( template1 PGUID ENCODING t t 0 0 0 "" _null_ _null_ ));
DESCR("Default template database");
#define TemplateDbOid 1
--- 58,78 ----
* 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
#define Anum_pg_database_datistemplate 4
#define Anum_pg_database_datallowconn 5
! #define Anum_pg_database_datisinit 6
! #define Anum_pg_database_datlastsysoid 7
! #define Anum_pg_database_datvacuumxid 8
! #define Anum_pg_database_datfrozenxid 9
! #define Anum_pg_database_datpath 10
! #define Anum_pg_database_datconfig 11
! #define Anum_pg_database_datacl 12
! DATA(insert OID = 1 ( template1 PGUID ENCODING t t t 0 0 0 "" _null_ _null_ ));
DESCR("Default template database");
#define TemplateDbOid 1
*** ./src/test/regress/expected/create_database.out.orig Sat May 8 11:07:33
2004
--- ./src/test/regress/expected/create_database.out Sat May 8 11:08:57 2004
***************
*** 0 ****
--- 1,66 ----
+ CREATE USER calvin;
+ CREATE DATABASE calvin WITH OWNER calvin;
+ CREATE USER hobbes;
+ CREATE DATABASE hobbes WITH OWNER hobbes;
+ SELECT datname, datdba, datisinit
+ FROM pg_database
+ WHERE datname = ANY(ARRAY['calvin', 'hobbes'])
+ ORDER BY datname;
+ datname | datdba | datisinit
+ ---------+--------+-----------
+ calvin | 100 | f
+ hobbes | 101 | f
+ (2 rows)
+
+ \c calvin calvin
+ SELECT CURRENT_USER;
+ current_user
+ --------------
+ calvin
+ (1 row)
+
+ SELECT datname, datdba, datisinit
+ FROM pg_database
+ WHERE datname = ANY(ARRAY['calvin', 'hobbes'])
+ ORDER BY datname;
+ datname | datdba | datisinit
+ ---------+--------+-----------
+ calvin | 100 | t
+ hobbes | 101 | f
+ (2 rows)
+
+ SELECT *
+ FROM pg_namespace
+ WHERE nspname NOT LIKE ALL(ARRAY['pg_%', 'information_schema'])
+ ORDER BY nspname;
+ nspname | nspowner | nspacl
+ ---------+----------+--------
+ public | 100 |
+ (1 row)
+
+ \c hobbes calvin
+ SELECT CURRENT_USER;
+ current_user
+ --------------
+ calvin
+ (1 row)
+
+ SELECT datname, datdba, datisinit
+ FROM pg_database
+ WHERE datname = ANY(ARRAY['calvin', 'hobbes'])
+ ORDER BY datname;
+ datname | datdba | datisinit
+ ---------+--------+-----------
+ calvin | 100 | t
+ hobbes | 101 | t
+ (2 rows)
+
+ SELECT *
+ FROM pg_namespace
+ WHERE nspname NOT LIKE ALL(ARRAY['pg_%', 'information_schema'])
+ ORDER BY nspname;
+ nspname | nspowner | nspacl
+ ---------+----------+--------
+ public | 101 |
+ (1 row)
+
*** ./src/test/regress/serial_schedule.orig Sun Jan 11 05:58:17 2004
--- ./src/test/regress/serial_schedule Sat May 8 11:02:52 2004
***************
*** 49,54 ****
--- 49,55 ----
test: create_aggregate
test: create_operator
test: create_index
+ test: create_database
test: inherit
test: vacuum
test: create_view
*** ./src/test/regress/sql/create_database.sql.orig Fri May 7 21:58:06 2004
--- ./src/test/regress/sql/create_database.sql Sat May 8 10:01:47 2004
***************
*** 0 ****
--- 1,36 ----
+ CREATE USER calvin;
+ CREATE DATABASE calvin WITH OWNER calvin;
+
+ CREATE USER hobbes;
+ CREATE DATABASE hobbes WITH OWNER hobbes;
+
+ SELECT datname, datdba, datisinit
+ FROM pg_database
+ WHERE datname = ANY(ARRAY['calvin', 'hobbes'])
+ ORDER BY datname;
+
+ \c calvin calvin
+ SELECT CURRENT_USER;
+
+ SELECT datname, datdba, datisinit
+ FROM pg_database
+ WHERE datname = ANY(ARRAY['calvin', 'hobbes'])
+ ORDER BY datname;
+
+ SELECT *
+ FROM pg_namespace
+ WHERE nspname NOT LIKE ALL(ARRAY['pg_%', 'information_schema'])
+ ORDER BY nspname;
+
+ \c hobbes calvin
+ SELECT CURRENT_USER;
+
+ SELECT datname, datdba, datisinit
+ FROM pg_database
+ WHERE datname = ANY(ARRAY['calvin', 'hobbes'])
+ ORDER BY datname;
+
+ SELECT *
+ FROM pg_namespace
+ WHERE nspname NOT LIKE ALL(ARRAY['pg_%', 'information_schema'])
+ ORDER BY nspname;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
