public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v6 1/7] pg_dump: make CLUSTER ON a separate dump object.. 3+ messages / 3 participants [nested] [flat]
* [PATCH v6 1/7] pg_dump: make CLUSTER ON a separate dump object.. @ 2020-11-26 20:37 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2020-11-26 20:37 UTC (permalink / raw) ..since it needs to be restored after any child indexes are restored *and attached*. The order needs to be: 1) restore child and parent index (order doesn't matter); 2) attach child index; 3) set cluster on child and parent index (order doesn't matter); --- src/bin/pg_dump/pg_dump.c | 86 ++++++++++++++++++++++++++-------- src/bin/pg_dump/pg_dump.h | 8 ++++ src/bin/pg_dump/pg_dump_sort.c | 8 ++++ 3 files changed, 82 insertions(+), 20 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 798d14580e..e6526392e5 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -208,6 +208,7 @@ static void dumpSequence(Archive *fout, TableInfo *tbinfo); static void dumpSequenceData(Archive *fout, TableDataInfo *tdinfo); static void dumpIndex(Archive *fout, IndxInfo *indxinfo); static void dumpIndexAttach(Archive *fout, IndexAttachInfo *attachinfo); +static void dumpIndexClusterOn(Archive *fout, IndexClusterInfo *clusterinfo); static void dumpStatisticsExt(Archive *fout, StatsExtInfo *statsextinfo); static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo); @@ -7092,6 +7093,11 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_inddependcollversions; int ntups; + int ncluster = 0; + IndexClusterInfo *clusterinfo; + clusterinfo = (IndexClusterInfo *) + pg_malloc0(numTables * sizeof(IndexClusterInfo)); + for (i = 0; i < numTables; i++) { TableInfo *tbinfo = &tblinfo[i]; @@ -7471,6 +7477,27 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) /* Plain secondary index */ indxinfo[j].indexconstraint = 0; } + + /* Record each table's CLUSTERed index, if any */ + if (indxinfo[j].indisclustered) + { + IndxInfo *index = &indxinfo[j]; + IndexClusterInfo *cluster = &clusterinfo[ncluster]; + + cluster->dobj.objType = DO_INDEX_CLUSTER_ON; + cluster->dobj.catId.tableoid = 0; + cluster->dobj.catId.oid = 0; + AssignDumpId(&cluster->dobj); + cluster->dobj.name = pg_strdup(index->dobj.name); + cluster->dobj.namespace = index->indextable->dobj.namespace; + cluster->index = index; + cluster->indextable = &tblinfo[i]; + + /* The CLUSTER ON depends on its index.. */ + addObjectDependency(&cluster->dobj, index->dobj.dumpId); + + ncluster++; + } } PQclear(res); @@ -10296,6 +10323,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) case DO_SUBSCRIPTION: dumpSubscription(fout, (SubscriptionInfo *) dobj); break; + case DO_INDEX_CLUSTER_ON: + dumpIndexClusterOn(fout, (IndexClusterInfo *) dobj); + break; case DO_PRE_DATA_BOUNDARY: case DO_POST_DATA_BOUNDARY: /* never dumped, nothing to do */ @@ -16516,6 +16546,41 @@ getAttrName(int attrnum, TableInfo *tblInfo) return NULL; /* keep compiler quiet */ } +/* + * dumpIndexClusterOn + * record that the index is clustered. + */ +static void +dumpIndexClusterOn(Archive *fout, IndexClusterInfo *clusterinfo) +{ + DumpOptions *dopt = fout->dopt; + TableInfo *tbinfo = clusterinfo->indextable; + char *qindxname; + PQExpBuffer q; + + if (dopt->dataOnly) + return; + + q = createPQExpBuffer(); + qindxname = pg_strdup(fmtId(clusterinfo->dobj.name)); + + /* index name is not qualified in this syntax */ + appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER ON %s;\n", + fmtQualifiedDumpable(tbinfo), qindxname); + + if (clusterinfo->dobj.dump & DUMP_COMPONENT_DEFINITION) + ArchiveEntry(fout, clusterinfo->dobj.catId, clusterinfo->dobj.dumpId, + ARCHIVE_OPTS(.tag = clusterinfo->dobj.name, + .namespace = tbinfo->dobj.namespace->dobj.name, + .owner = tbinfo->rolname, + .description = "INDEX CLUSTER ON", + .section = SECTION_POST_DATA, + .createStmt = q->data)); + + destroyPQExpBuffer(q); + free(qindxname); +} + /* * dumpIndex * write out to fout a user-defined index @@ -16570,16 +16635,6 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo) * similar code in dumpConstraint! */ - /* If the index is clustered, we need to record that. */ - if (indxinfo->indisclustered) - { - appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER", - fmtQualifiedDumpable(tbinfo)); - /* index name is not qualified in this syntax */ - appendPQExpBuffer(q, " ON %s;\n", - qindxname); - } - /* * If the index has any statistics on some of its columns, generate * the associated ALTER INDEX queries. @@ -16906,16 +16961,6 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) * similar code in dumpIndex! */ - /* If the index is clustered, we need to record that. */ - if (indxinfo->indisclustered) - { - appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER", - fmtQualifiedDumpable(tbinfo)); - /* index name is not qualified in this syntax */ - appendPQExpBuffer(q, " ON %s;\n", - fmtId(indxinfo->dobj.name)); - } - /* If the index defines identity, we need to record that. */ if (indxinfo->indisreplident) { @@ -18421,6 +18466,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs, break; case DO_INDEX: case DO_INDEX_ATTACH: + case DO_INDEX_CLUSTER_ON: case DO_STATSEXT: case DO_REFRESH_MATVIEW: case DO_TRIGGER: diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 1290f9659b..57def4c009 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -54,6 +54,7 @@ typedef enum DO_ATTRDEF, DO_INDEX, DO_INDEX_ATTACH, + DO_INDEX_CLUSTER_ON, DO_STATSEXT, DO_RULE, DO_TRIGGER, @@ -386,6 +387,13 @@ typedef struct _indxInfo DumpId indexconstraint; } IndxInfo; +typedef struct _indexClusterInfo +{ + DumpableObject dobj; + TableInfo *indextable; /* link to table the index is for */ + IndxInfo *index; /* link to index itself */ +} IndexClusterInfo; + typedef struct _indexAttachInfo { DumpableObject dobj; diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index 46461fb6a1..dd5b233196 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -75,6 +75,7 @@ enum dbObjectTypePriorities PRIO_CONSTRAINT, PRIO_INDEX, PRIO_INDEX_ATTACH, + PRIO_INDEX_CLUSTER_ON, PRIO_STATSEXT, PRIO_RULE, PRIO_TRIGGER, @@ -108,6 +109,7 @@ static const int dbObjectTypePriority[] = PRIO_ATTRDEF, /* DO_ATTRDEF */ PRIO_INDEX, /* DO_INDEX */ PRIO_INDEX_ATTACH, /* DO_INDEX_ATTACH */ + PRIO_INDEX_CLUSTER_ON, /* DO_INDEX_CLUSTER_ON */ PRIO_STATSEXT, /* DO_STATSEXT */ PRIO_RULE, /* DO_RULE */ PRIO_TRIGGER, /* DO_TRIGGER */ @@ -136,6 +138,7 @@ static const int dbObjectTypePriority[] = PRIO_PUBLICATION, /* DO_PUBLICATION */ PRIO_PUBLICATION_REL, /* DO_PUBLICATION_REL */ PRIO_SUBSCRIPTION /* DO_SUBSCRIPTION */ + }; StaticAssertDecl(lengthof(dbObjectTypePriority) == (DO_SUBSCRIPTION + 1), @@ -1348,6 +1351,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize) "INDEX ATTACH %s (ID %d)", obj->name, obj->dumpId); return; + case DO_INDEX_CLUSTER_ON: + snprintf(buf, bufsize, + "INDEX CLUSTER ON %s (ID %d)", + obj->name, obj->dumpId); + return; case DO_STATSEXT: snprintf(buf, bufsize, "STATISTICS %s (ID %d OID %u)", -- 2.17.0 --FsscpQKzF/jJk6ya Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0002-Implement-CLUSTER-of-partitioned-table.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* restrict_nonsystem_relation_kind led to regression (kinda) @ 2024-09-23 15:38 Magnus Holmgren <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Magnus Holmgren @ 2024-09-23 15:38 UTC (permalink / raw) To: pgsql-hackers Hello, We've set allow_system_table_mods = on so that we could rename pg_database and in its place put a custom view that only lists the databases the current user has CONNECT privileges to. This is because 1) we allow customers direct (read only) access to their databases, but 2) we don't want them to see the other customers, and 3) restricting access to pg_database altogether leads to the GUIs the customers use spamming error messages because they expect pg_database to be readable, and that makes the customers (or their consultants) annoyed. A problem arose after the fix for CVE-2024-7348, because certain queries that pg_dump runs use pg_database, and those are now blocked, so pg_dump fails. Well, actually, it's just subscriptions that are the problem when it comes to pg_dump: pg_dump --no-subscriptions works in our case. However, pg_dumpall runs a different query that also uses pg_database and that I don't think is possible to avoid. I realise that if you use allow_system_table_mods, you're kinda on your own, but it exists after all, and this security fix seems to make it less usable, if not unusable. Could views owned by postgres and/or in the pg_catalog namespace be considered system relations, even if customized? There's no way to suppress the use of restrict_nonsystem_relation_kind if you know that there are no untrusted users with object creation privileges, is there? Alternatively, do you have any other suggestions as to how to solve the original problem (we'd like to avoid renaming the databases so they don't reveal the customer names)? -- Greetings, Magnus Holmgren Milient Software | www.milientsoftware.com ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: restrict_nonsystem_relation_kind led to regression (kinda) @ 2024-09-23 15:50 Alvaro Herrera <[email protected]> parent: Magnus Holmgren <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Alvaro Herrera @ 2024-09-23 15:50 UTC (permalink / raw) To: Magnus Holmgren <[email protected]>; +Cc: pgsql-hackers Hi Magnus, On 2024-Sep-23, Magnus Holmgren wrote: > We've set allow_system_table_mods = on so that we could rename > pg_database and in its place put a custom view that only lists the > databases the current user has CONNECT privileges to. This is because > 1) we allow customers direct (read only) access to their databases, but > 2) we don't want them to see the other customers, and 3) restricting > access to pg_database altogether leads to the GUIs the customers use > spamming error messages because they expect pg_database to be readable, > and that makes the customers (or their consultants) annoyed. Your use case and problem seem to match bug report #18604 almost exactly: https://postgr.es/m/[email protected] I suggest to read that discussion, as it contains useful information. As I understand, you're only really safe (not just theatrically safe) by giving each customer a separate Postgres instance. Regards -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-09-23 15:50 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-26 20:37 [PATCH v6 1/7] pg_dump: make CLUSTER ON a separate dump object.. Justin Pryzby <[email protected]> 2024-09-23 15:38 restrict_nonsystem_relation_kind led to regression (kinda) Magnus Holmgren <[email protected]> 2024-09-23 15:50 ` Re: restrict_nonsystem_relation_kind led to regression (kinda) Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox