Re: Largeobject Access Controls (r2460) - Mailing list pgsql-hackers

KaiGai Kohei wrote:
> > What happens when
> > there is no entry in pg_largeobject_metadata for a specific row?
>
> In this case, these rows become orphan.
> So, I think we need to create an empty large object with same LOID on
> pg_migrator. It makes an entry on pg_largeobject_metadata without
> writing anything to the pg_largeobject.
> I guess rest of migrations are not difference. Correct?

Agreed.  I have modified pg_migrator with the attached patch which
creates a script that adds default permissions for all large object
tables.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
? tools
? log
? src/pg_migrator
Index: src/info.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/info.c,v
retrieving revision 1.25
diff -c -r1.25 info.c
*** src/info.c    10 Dec 2009 23:14:25 -0000    1.25
--- src/info.c    13 Dec 2009 01:17:37 -0000
***************
*** 480,486 ****
                                          "SELECT DISTINCT probin "
                                          "FROM    pg_catalog.pg_proc "
                                          "WHERE    prolang = 13 /* C */ AND "
!                                         "        probin IS NOT NULL");
          totaltups += PQntuples(ress[dbnum]);

          PQfinish(conn);
--- 480,488 ----
                                          "SELECT DISTINCT probin "
                                          "FROM    pg_catalog.pg_proc "
                                          "WHERE    prolang = 13 /* C */ AND "
!                                         "        probin IS NOT NULL AND "
!                                         "        oid >= "
!                                         STRINGIFY(FirstNormalObjectId) ";");
          totaltups += PQntuples(ress[dbnum]);

          PQfinish(conn);
Index: src/pg_migrator.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.c,v
retrieving revision 1.69
diff -c -r1.69 pg_migrator.c
*** src/pg_migrator.c    10 Dec 2009 14:34:19 -0000    1.69
--- src/pg_migrator.c    13 Dec 2009 01:17:37 -0000
***************
*** 92,97 ****
--- 92,100 ----
              sequence_script_file_name =
                  v8_3_create_sequence_script(&ctx, CLUSTER_OLD);
      }
+     if (GET_MAJOR_VERSION(ctx.old.pg_version) <= 804 &&
+         GET_MAJOR_VERSION(ctx.new.pg_version) >= 805)
+         v8_4_populate_pg_largeobject_metadata(&ctx, true, CLUSTER_OLD);

      /* Looks okay so far.  Prepare the pg_dump output */
      generate_old_dump(&ctx);
***************
*** 294,299 ****
--- 297,309 ----
          v8_3_invalidate_bpchar_pattern_ops_indexes(&ctx, false, CLUSTER_NEW);
          stop_postmaster(&ctx, false, true);
      }
+     if (GET_MAJOR_VERSION(ctx.old.pg_version) <= 804 &&
+         GET_MAJOR_VERSION(ctx.new.pg_version) >= 805)
+     {
+         start_postmaster(&ctx, CLUSTER_NEW, true);
+         v8_4_populate_pg_largeobject_metadata(&ctx, false, CLUSTER_NEW);
+         stop_postmaster(&ctx, false, true);
+     }

      pg_log(&ctx, PG_REPORT, "\n*Upgrade complete*\n");

***************
*** 416,422 ****
      char        new_clog_path[MAXPGPATH];

      /* copy old commit logs to new data dir */
!     prep_status(ctx, "Deleting old commit clogs");

      snprintf(old_clog_path, sizeof(old_clog_path), "%s/pg_clog", ctx->old.pgdata);
      snprintf(new_clog_path, sizeof(new_clog_path), "%s/pg_clog", ctx->new.pgdata);
--- 426,432 ----
      char        new_clog_path[MAXPGPATH];

      /* copy old commit logs to new data dir */
!     prep_status(ctx, "Deleting new commit clogs");

      snprintf(old_clog_path, sizeof(old_clog_path), "%s/pg_clog", ctx->old.pgdata);
      snprintf(new_clog_path, sizeof(new_clog_path), "%s/pg_clog", ctx->new.pgdata);
***************
*** 424,430 ****
          pg_log(ctx, PG_FATAL, "Unable to delete directory %s\n", new_clog_path);
      check_ok(ctx);

!     prep_status(ctx, "Copying commit clogs");
      /* libpgport's copydir() doesn't work in FRONTEND code */
  #ifndef WIN32
      exec_prog(ctx, true, SYSTEMQUOTE "%s \"%s\" \"%s\"" SYSTEMQUOTE,
--- 434,440 ----
          pg_log(ctx, PG_FATAL, "Unable to delete directory %s\n", new_clog_path);
      check_ok(ctx);

!     prep_status(ctx, "Copying old commit clogs to new server");
      /* libpgport's copydir() doesn't work in FRONTEND code */
  #ifndef WIN32
      exec_prog(ctx, true, SYSTEMQUOTE "%s \"%s\" \"%s\"" SYSTEMQUOTE,
Index: src/pg_migrator.h
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.h,v
retrieving revision 1.75
diff -c -r1.75 pg_migrator.h
*** src/pg_migrator.h    12 Dec 2009 16:56:23 -0000    1.75
--- src/pg_migrator.h    13 Dec 2009 01:17:37 -0000
***************
*** 395,400 ****
--- 395,402 ----
                              bool check_mode, Cluster whichCluster);
  void        v8_3_invalidate_bpchar_pattern_ops_indexes(migratorContext *ctx,
                              bool check_mode, Cluster whichCluster);
+ void        v8_4_populate_pg_largeobject_metadata(migratorContext *ctx,
+                             bool check_mode, Cluster whichCluster);
  char         *v8_3_create_sequence_script(migratorContext *ctx,
                              Cluster whichCluster);
  void        check_for_composite_types(migratorContext *ctx,
Index: src/version.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/version.c,v
retrieving revision 1.32
diff -c -r1.32 version.c
*** src/version.c    7 Aug 2009 20:16:12 -0000    1.32
--- src/version.c    13 Dec 2009 01:17:37 -0000
***************
*** 421,427 ****
                      "| between your old and new clusters so the tables\n"
                      "| must be rebuilt.  The file:\n"
                      "| \t%s\n"
!                     "| when executed by psql by the database super-user,\n"
                      "| will rebuild all tables with tsvector columns.\n\n",
                      output_path);
      }
--- 421,427 ----
                      "| between your old and new clusters so the tables\n"
                      "| must be rebuilt.  The file:\n"
                      "| \t%s\n"
!                     "| when executed by psql by the database super-user\n"
                      "| will rebuild all tables with tsvector columns.\n\n",
                      output_path);
      }
***************
*** 535,541 ****
                      "| they must be reindexed with the REINDEX command.\n"
                      "| The file:\n"
                      "| \t%s\n"
!                     "| when executed by psql by the database super-user,\n"
                      "| will recreate all invalid indexes; until then,\n"
                      "| none of these indexes will be used.\n\n",
                      output_path);
--- 535,541 ----
                      "| they must be reindexed with the REINDEX command.\n"
                      "| The file:\n"
                      "| \t%s\n"
!                     "| when executed by psql by the database super-user\n"
                      "| will recreate all invalid indexes; until then,\n"
                      "| none of these indexes will be used.\n\n",
                      output_path);
***************
*** 664,670 ****
                      "| new clusters so they must be reindexed with the\n"
                      "| REINDEX command.  The file:\n"
                      "| \t%s\n"
!                     "| when executed by psql by the database super-user,\n"
                      "| will recreate all invalid indexes; until then,\n"
                      "| none of these indexes will be used.\n\n",
                      output_path);
--- 664,670 ----
                      "| new clusters so they must be reindexed with the\n"
                      "| REINDEX command.  The file:\n"
                      "| \t%s\n"
!                     "| when executed by psql by the database super-user\n"
                      "| will recreate all invalid indexes; until then,\n"
                      "| none of these indexes will be used.\n\n",
                      output_path);
***************
*** 675,680 ****
--- 675,762 ----


  /*
+  * v8_4_populate_pg_largeobject_metadata()
+  *
+  *    8.5 has a new pg_largeobject permission table
+  */
+ void
+ v8_4_populate_pg_largeobject_metadata(migratorContext *ctx, bool check_mode,
+                                       Cluster whichCluster)
+ {
+     ClusterInfo    *active_cluster = (whichCluster == CLUSTER_OLD) ?
+                     &ctx->old : &ctx->new;
+     int            dbnum;
+     FILE        *script = NULL;
+     bool        found = false;
+     char        output_path[MAXPGPATH];
+
+     prep_status(ctx, "Checking for large objects");
+
+     snprintf(output_path, sizeof(output_path), "%s/pg_largeobject.sql",
+             ctx->home_dir);
+
+     for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
+     {
+         PGresult   *res;
+         int            i_count;
+         DbInfo       *active_db = &active_cluster->dbarr.dbs[dbnum];
+         PGconn       *conn = connectToServer(ctx, active_db->db_name, whichCluster);
+
+         /* find if there are any large objects */
+         res = executeQueryOrDie(ctx, conn,
+                                 "SELECT count(*) "
+                                 "FROM    pg_catalog.pg_largeobject ");
+
+         i_count = PQfnumber(res, "count");
+         if (atoi(PQgetvalue(res, 0, i_count)) != 0)
+         {
+             found = true;
+             if (!check_mode)
+             {
+                 if (script == NULL && (script = fopen(output_path, "w")) == NULL)
+                         pg_log(ctx, PG_FATAL, "Could not create necessary file:  %s\n", output_path);
+                 fprintf(script, "\\connect %s\n",
+                         quote_identifier(ctx, active_db->db_name));
+                 fprintf(script,
+                     "INSERT INTO pg_catalog.pg_largeobject_metadata (lomowner)\n"
+                                 "SELECT DISTINCT loid\n"
+                                 "FROM pg_catalog.pg_largeobject;\n");
+             }
+         }
+
+         PQclear(res);
+         PQfinish(conn);
+     }
+
+     if (found)
+     {
+         if (!check_mode)
+             fclose(script);
+         report_status(ctx, PG_WARNING, "warning");
+         if (check_mode)
+             pg_log(ctx, PG_WARNING, "\n"
+                     "| Your installation contains large objects.\n"
+                     "| The new database has an additional large object\n"
+                     "| permission table.  After migration, you will be\n"
+                     "| given a command to populate the pg_largeobject\n"
+                     "| permission table with default permissions.\n\n");
+         else
+             pg_log(ctx, PG_WARNING, "\n"
+                     "| Your installation contains large objects.\n"
+                     "| The new database has an additional large object\n"
+                     "| permission table so default permissions must be\n"
+                     "| defined for all large objects.  The file:\n"
+                     "| \t%s\n"
+                     "| when executed by psql by the database super-user\n"
+                     "| will define the default permissions.\n\n",
+                     output_path);
+     }
+     else
+         check_ok(ctx);
+ }
+
+
+ /*
   * v8_3_create_sequence_script()
   *
   *    8.4 added the column "start_value" to all sequences.  For this reason,