This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit ed7d24463b7c820095e61264909596c6fa2f0d0e Author: Jingyu Wang <[email protected]> AuthorDate: Tue Jul 11 12:12:59 2023 -0700 Pass requiredPerms from parse tree to plstmt Background: ICW test privileges was turned off on ORCA in 2014 after ORCA generated plans requesting incorrect privilege, causing queries that should be executed denied. To revive the test, we have a few issues to address -- 1. Some tests in the suite failed the non-NULL target list assertion in DXL -> planned statement translation. This issue was fixed by PR #15939, and the assertion is no more considered necessary. 2. Duplicate rte's in planned statement. ORCA may generate plans with duplicate range table entries, each annotated with a different required permission. This issue was fixed by PR #14304. Now we reuse rte's in the range table entries list, and update the permission in the process. 3. ORCA requires more privilege than needed, which is addressed by this PR. ORCA constructs required permissions when building DML plan nodes. For INSERT/UPDATE/DELETE operations, ORCA requests SELECT permission as well. This is usually the right thing to do, cause most non trivial DML operations would require SELECT permission as well. However, there are DML queries that don't require SELECT permission. Example: Requires UPDATE permission only ``` update my_table set my_col = 1; ``` Requires UPDATE and SELECT permissions ``` update my_table set my_col = 1 where my_col = 2; ``` Implementation: As of now, ORCA's translator discards the required permission info from the query parse tree. Instead, ORCAconfigures the permission at a much later stage when building the plan nodes for DML operations based on the operation type. To avoid invoking unnecessary permissions, we pass the required permission from the parse tree to the planned statement. Specifically, when serializing a query rte into a table descriptor, we attach the required permission to the table descriptor. Eventually, when building the DML plan nodes, we use the table descriptor's permission for the plan rte. Required permission is of the type AclMode, which is essentially 32-bit unsigned integer. Since ORCA doesn't have access to Postgres utilities, we cannot use the AclMode alias within ORCA. Instead, we use 32-bit integer, and cast it back to AclMode when exiting ORCA. Test: By default, table created by CTAS with planner is hash distributed, and is randomly distributed with ORCA. We specify the distribution spec for a CTAS statement in the test suite, so that ORCA falls back onto the same planner plan using nested loop join. --- .../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 48 +- src/backend/gpopt/translate/CTranslatorUtils.cpp | 7 +- .../gporca/data/dxl/minidump/RTErequiredPerms.mdp | 454 ++++++++++++++++++ .../include/gpopt/metadata/CTableDescriptor.h | 11 +- .../libgpopt/src/metadata/CTableDescriptor.cpp | 3 +- .../src/translate/CTranslatorDXLToExpr.cpp | 6 +- .../src/translate/CTranslatorExprToDXL.cpp | 9 +- .../naucrates/dxl/operators/CDXLTableDescr.h | 8 +- .../include/naucrates/dxl/xml/dxltokens.h | 1 + .../src/operators/CDXLOperatorFactory.cpp | 9 +- .../libnaucrates/src/operators/CDXLTableDescr.cpp | 15 + .../gporca/libnaucrates/src/xml/dxltokens.cpp | 1 + .../gporca/server/src/unittest/CTestUtils.cpp | 1 + .../unittest/dxl/statistics/CStatisticsTest.cpp | 1 + .../src/unittest/gpopt/minidump/CDMLTest.cpp | 1 + .../gpopt/translate/CTranslatorDXLToExprTest.cpp | 1 + .../gpopt/translate/CTranslatorDXLToPlStmt.h | 3 +- src/test/regress/expected/privileges.out | 11 +- .../{privileges.out => privileges_optimizer.out} | 508 +++++++-------------- src/test/regress/sql/privileges.sql | 12 +- 20 files changed, 725 insertions(+), 385 deletions(-) diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp index 740b5af832..7bcd23c6bf 100644 --- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp +++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp @@ -38,6 +38,7 @@ extern "C" { } #include <algorithm> +#include <limits> // std::numeric_limits #include <numeric> #include <tuple> @@ -596,8 +597,7 @@ CTranslatorDXLToPlStmt::TranslateDXLTblScan( GPOS_ASSERT(dxl_table_descr->LockMode() != -1); gpdb::GPDBLockRelationOid(mdid->Oid(), dxl_table_descr->LockMode()); - Index index = - ProcessDXLTblDescr(dxl_table_descr, &base_table_context, ACL_SELECT); + Index index = ProcessDXLTblDescr(dxl_table_descr, &base_table_context); // a table scan node must have 2 children: projection list and filter GPOS_ASSERT(2 == tbl_scan_dxlnode->Arity()); @@ -760,8 +760,7 @@ CTranslatorDXLToPlStmt::TranslateDXLIndexScan( GPOS_ASSERT(dxl_table_descr->LockMode() != -1); gpdb::GPDBLockRelationOid(mdid->Oid(), dxl_table_descr->LockMode()); - Index index = - ProcessDXLTblDescr(dxl_table_descr, &base_table_context, ACL_SELECT); + Index index = ProcessDXLTblDescr(dxl_table_descr, &base_table_context); IndexScan *index_scan = nullptr; index_scan = MakeNode(IndexScan); @@ -937,8 +936,7 @@ CTranslatorDXLToPlStmt::TranslateDXLIndexOnlyScan( const IMDRelation *md_rel = m_md_accessor->RetrieveRel( physical_idx_scan_dxlop->GetDXLTableDescr()->MDId()); - Index index = - ProcessDXLTblDescr(table_desc, &base_table_context, ACL_SELECT); + Index index = ProcessDXLTblDescr(table_desc, &base_table_context); IndexOnlyScan *index_scan = MakeNode(IndexOnlyScan); index_scan->scan.scanrelid = index; @@ -3960,7 +3958,7 @@ CTranslatorDXLToPlStmt::TranslateDXLAppend( CDXLTranslateContextBaseTable base_table_context(m_mp); (void) ProcessDXLTblDescr(phy_append_dxlop->GetDXLTableDesc(), - &base_table_context, ACL_SELECT); + &base_table_context); append->join_prune_paramids = NIL; const ULongPtrArray *selector_ids = phy_append_dxlop->GetSelectorIds(); @@ -4326,7 +4324,7 @@ CTranslatorDXLToPlStmt::TranslateDXLDynTblScan( CDXLTranslateContextBaseTable base_table_context(m_mp); Index index = ProcessDXLTblDescr(dyn_tbl_scan_dxlop->GetDXLTableDescr(), - &base_table_context, ACL_SELECT); + &base_table_context); // create dynamic scan node DynamicSeqScan *dyn_seq_scan = MakeNode(DynamicSeqScan); @@ -4417,8 +4415,7 @@ CTranslatorDXLToPlStmt::TranslateDXLDynIdxScan( const CDXLTableDescr *table_desc = dyn_index_scan_dxlop->GetDXLTableDescr(); const IMDRelation *md_rel = m_md_accessor->RetrieveRel(table_desc->MDId()); - Index index = - ProcessDXLTblDescr(table_desc, &base_table_context, ACL_SELECT); + Index index = ProcessDXLTblDescr(table_desc, &base_table_context); DynamicIndexScan *dyn_idx_scan = MakeNode(DynamicIndexScan); @@ -4566,7 +4563,7 @@ CTranslatorDXLToPlStmt::TranslateDXLDynForeignScan( CDXLTranslateContextBaseTable base_table_context(m_mp); Index index = ProcessDXLTblDescr(dyn_foreign_scan_dxlop->GetDXLTableDescr(), - &base_table_context, ACL_SELECT); + &base_table_context); // rte of root dynamic scan RangeTblEntry *rte = m_dxl_to_plstmt_context->GetRTEByIndex(index); Oid oid_root = rte->relid; @@ -4709,7 +4706,6 @@ CTranslatorDXLToPlStmt::TranslateDXLDml( // create ModifyTable node ModifyTable *dml = MakeNode(ModifyTable); Plan *plan = &(dml->plan); - AclMode acl_mode = ACL_NO_RIGHTS; BOOL isSplit = phy_dml_dxlop->FSplit(); List *updateCols = NIL; @@ -4718,19 +4714,16 @@ CTranslatorDXLToPlStmt::TranslateDXLDml( case gpdxl::Edxldmldelete: { m_cmd_type = CMD_DELETE; - acl_mode = ACL_DELETE; break; } case gpdxl::Edxldmlupdate: { m_cmd_type = CMD_UPDATE; - acl_mode = ACL_UPDATE; break; } case gpdxl::Edxldmlinsert: { m_cmd_type = CMD_INSERT; - acl_mode = ACL_INSERT; break; } case gpdxl::EdxldmlSentinel: @@ -4777,8 +4770,7 @@ CTranslatorDXLToPlStmt::TranslateDXLDml( CDXLTableDescr *table_descr = phy_dml_dxlop->GetDXLTableDescr(); - Index index = - ProcessDXLTblDescr(table_descr, &base_table_context, acl_mode); + Index index = ProcessDXLTblDescr(table_descr, &base_table_context); m_result_rel_list = gpdb::LAppendInt(m_result_rel_list, index); @@ -5245,7 +5237,7 @@ CTranslatorDXLToPlStmt::TranslateDXLAssert( Index CTranslatorDXLToPlStmt::ProcessDXLTblDescr( const CDXLTableDescr *table_descr, - CDXLTranslateContextBaseTable *base_table_context, AclMode acl_mode) + CDXLTranslateContextBaseTable *base_table_context) { GPOS_ASSERT(nullptr != table_descr); @@ -5280,13 +5272,18 @@ CTranslatorDXLToPlStmt::ProcessDXLTblDescr( (void) base_table_context->InsertMapping(dxl_col_descr->Id(), attno); } + INT acl_mode = table_descr->GetAclMode(); + GPOS_ASSERT(acl_mode >= 0 && + acl_mode <= std::numeric_limits<AclMode>::max()); + AclMode required_perms = static_cast<AclMode>(acl_mode); + // descriptor was already processed, and translated RTE is stored at // context rtable list (only update required perms of this rte is needed) if (rte_was_translated) { RangeTblEntry *rte = m_dxl_to_plstmt_context->GetRTEByIndex(index); GPOS_ASSERT(nullptr != rte); - rte->requiredPerms |= acl_mode; + rte->requiredPerms |= required_perms; return index; } @@ -5295,7 +5292,7 @@ CTranslatorDXLToPlStmt::ProcessDXLTblDescr( rte->rtekind = RTE_RELATION; rte->relid = oid; rte->checkAsUser = table_descr->GetExecuteAsUserId(); - rte->requiredPerms |= acl_mode; + rte->requiredPerms |= required_perms; rte->rellockmode = table_descr->LockMode(); Alias *alias = MakeNode(Alias); @@ -5344,10 +5341,16 @@ CTranslatorDXLToPlStmt::ProcessDXLTblDescr( rte->eref = alias; + // A new RTE is added to the range table entries list if it's not found in the look + // up table. However, it is only added to the look up table if it's a result relation + // This is because the look up table is our way of merging duplicate result relations m_dxl_to_plstmt_context->AddRTE(rte); GPOS_ASSERT(gpdb::ListLength( m_dxl_to_plstmt_context->GetRTableEntriesList()) == index); - m_dxl_to_plstmt_context->InsertUsedRTEIndexes(assigned_query_id, index); + if (UNASSIGNED_QUERYID != assigned_query_id) + { + m_dxl_to_plstmt_context->InsertUsedRTEIndexes(assigned_query_id, index); + } return index; } @@ -6312,8 +6315,7 @@ CTranslatorDXLToPlStmt::TranslateDXLBitmapTblScan( GPOS_ASSERT(table_descr->LockMode() != -1); gpdb::GPDBLockRelationOid(mdid->Oid(), table_descr->LockMode()); - Index index = - ProcessDXLTblDescr(table_descr, &base_table_context, ACL_SELECT); + Index index = ProcessDXLTblDescr(table_descr, &base_table_context); DynamicBitmapHeapScan *dscan; BitmapHeapScan *bitmap_tbl_scan; diff --git a/src/backend/gpopt/translate/CTranslatorUtils.cpp b/src/backend/gpopt/translate/CTranslatorUtils.cpp index 65bfdbaa27..730e3676f5 100644 --- a/src/backend/gpopt/translate/CTranslatorUtils.cpp +++ b/src/backend/gpopt/translate/CTranslatorUtils.cpp @@ -129,9 +129,10 @@ CTranslatorUtils::GetTableDescr(CMemoryPool *mp, CMDAccessor *md_accessor, const CWStringConst *tablename = rel->Mdname().GetMDName(); CMDName *table_mdname = GPOS_NEW(mp) CMDName(mp, tablename); - CDXLTableDescr *table_descr = GPOS_NEW(mp) - CDXLTableDescr(mp, mdid, table_mdname, rte->checkAsUser, - rte->rellockmode, assigned_query_id_for_target_rel); + INT required_perms = static_cast<INT>(rte->requiredPerms); + CDXLTableDescr *table_descr = GPOS_NEW(mp) CDXLTableDescr( + mp, mdid, table_mdname, rte->checkAsUser, rte->rellockmode, + required_perms, assigned_query_id_for_target_rel); const ULONG len = rel->ColumnCount(); diff --git a/src/backend/gporca/data/dxl/minidump/RTErequiredPerms.mdp b/src/backend/gporca/data/dxl/minidump/RTErequiredPerms.mdp new file mode 100644 index 0000000000..c863083cb0 --- /dev/null +++ b/src/backend/gporca/data/dxl/minidump/RTErequiredPerms.mdp @@ -0,0 +1,454 @@ +<?xml version="1.0" encoding="UTF-8"?> +<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/";> + <dxl:Comment><![CDATA[ + Objective: Verify the generation of AclMode, which should be + the bit OR of all required permissions. The query requests + SELECT privilege on t2, and SELECT + DELETE privileges on t1 + + create table t1 (i int); + create table t2 (j int); + insert into t1 values (1), (2), (3); + insert into t2 values (1), (2); + delete from t1 where i in (select j from t2); + ]]></dxl:Comment> + <dxl:Thread Id="0"> + <dxl:OptimizerConfig> + <dxl:EnumeratorConfig Id="0" PlanSamples="0" CostThreshold="0"/> + <dxl:StatisticsConfig DampingFactorFilter="0.750000" DampingFactorJoin="0.000000" DampingFactorGroupBy="0.750000" MaxStatsBuckets="100"/> + <dxl:CTEConfig CTEInliningCutoff="0"/> + <dxl:WindowOids RowNumber="3100" Rank="3101"/> + <dxl:CostModelConfig CostModelType="1" SegmentsForCosting="3"> + <dxl:CostParams> + <dxl:CostParam Name="NLJFactor" Value="1024.000000" LowerBound="1023.500000" UpperBound="1024.500000"/> + </dxl:CostParams> + </dxl:CostModelConfig> + <dxl:Hint JoinArityForAssociativityCommutativity="18" ArrayExpansionThreshold="20" JoinOrderDynamicProgThreshold="10" BroadcastThreshold="100000" EnforceConstraintsOnDML="false" PushGroupByBelowSetopThreshold="10" XformBindThreshold="0" SkewFactor="0"/> + <dxl:TraceFlags Value="101013,102001,102002,102003,102043,102074,102120,102144,102162,102163,103001,103014,103015,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,106000"/> + </dxl:OptimizerConfig> + <dxl:Metadata SystemIds="0.GPDB"> + <dxl:RelationExtendedStatistics Mdid="10.25685.1.0" Name="t1"/> + <dxl:RelationExtendedStatistics Mdid="10.25688.1.0" Name="t2"/> + <dxl:Type Mdid="0.16.1.0" Name="bool" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="1" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2222.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7124.1.0"/> + <dxl:PartOpfamily Mdid="0.424.1.0"/> + <dxl:EqualityOp Mdid="0.91.1.0"/> + <dxl:InequalityOp Mdid="0.85.1.0"/> + <dxl:LessThanOp Mdid="0.58.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.1694.1.0"/> + <dxl:GreaterThanOp Mdid="0.59.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.1695.1.0"/> + <dxl:ComparisonOp Mdid="0.1693.1.0"/> + <dxl:ArrayType Mdid="0.1000.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.20.1.0" Name="Int8" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="8" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:PartOpfamily Mdid="0.1976.1.0"/> + <dxl:EqualityOp Mdid="0.410.1.0"/> + <dxl:InequalityOp Mdid="0.411.1.0"/> + <dxl:LessThanOp Mdid="0.412.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.414.1.0"/> + <dxl:GreaterThanOp Mdid="0.413.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.415.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1016.1.0"/> + <dxl:MinAgg Mdid="0.2131.1.0"/> + <dxl:MaxAgg Mdid="0.2115.1.0"/> + <dxl:AvgAgg Mdid="0.2100.1.0"/> + <dxl:SumAgg Mdid="0.2107.1.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.23.1.0" Name="int4" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/> + <dxl:PartOpfamily Mdid="0.1976.1.0"/> + <dxl:EqualityOp Mdid="0.96.1.0"/> + <dxl:InequalityOp Mdid="0.518.1.0"/> + <dxl:LessThanOp Mdid="0.97.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.523.1.0"/> + <dxl:GreaterThanOp Mdid="0.521.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.525.1.0"/> + <dxl:ComparisonOp Mdid="0.351.1.0"/> + <dxl:ArrayType Mdid="0.1007.1.0"/> + <dxl:MinAgg Mdid="0.2132.1.0"/> + <dxl:MaxAgg Mdid="0.2116.1.0"/> + <dxl:AvgAgg Mdid="0.2101.1.0"/> + <dxl:SumAgg Mdid="0.2108.1.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:GPDBScalarOp Mdid="0.410.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.20.1.0"/> + <dxl:RightType Mdid="0.20.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.467.1.0"/> + <dxl:Commutator Mdid="0.410.1.0"/> + <dxl:InverseOp Mdid="0.411.1.0"/> + <dxl:HashOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyHashOpfamily Mdid="0.7100.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1976.1.0"/> + <dxl:Opfamily Mdid="0.1977.1.0"/> + <dxl:Opfamily Mdid="0.4054.1.0"/> + <dxl:Opfamily Mdid="0.7100.1.0"/> + <dxl:Opfamily Mdid="0.10009.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:Type Mdid="0.26.1.0" Name="oid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.1990.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7109.1.0"/> + <dxl:PartOpfamily Mdid="0.1989.1.0"/> + <dxl:EqualityOp Mdid="0.607.1.0"/> + <dxl:InequalityOp Mdid="0.608.1.0"/> + <dxl:LessThanOp Mdid="0.609.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.611.1.0"/> + <dxl:GreaterThanOp Mdid="0.610.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.612.1.0"/> + <dxl:ComparisonOp Mdid="0.356.1.0"/> + <dxl:ArrayType Mdid="0.1028.1.0"/> + <dxl:MinAgg Mdid="0.2134.1.0"/> + <dxl:MaxAgg Mdid="0.2118.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.27.1.0" Name="tid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="6" PassByValue="false"> + <dxl:DistrOpfamily Mdid="0.2227.1.0"/> + <dxl:LegacyDistrOpfamily Mdid="0.7110.1.0"/> + <dxl:PartOpfamily Mdid="0.2789.1.0"/> + <dxl:EqualityOp Mdid="0.387.1.0"/> + <dxl:InequalityOp Mdid="0.402.1.0"/> + <dxl:LessThanOp Mdid="0.2799.1.0"/> + <dxl:LessThanEqualsOp Mdid="0.2801.1.0"/> + <dxl:GreaterThanOp Mdid="0.2800.1.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.2802.1.0"/> + <dxl:ComparisonOp Mdid="0.2794.1.0"/> + <dxl:ArrayType Mdid="0.1010.1.0"/> + <dxl:MinAgg Mdid="0.2798.1.0"/> + <dxl:MaxAgg Mdid="0.2797.1.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:GPDBScalarOp Mdid="0.413.1.0" Name=">" ComparisonType="GT" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.20.1.0"/> + <dxl:RightType Mdid="0.20.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.470.1.0"/> + <dxl:Commutator Mdid="0.412.1.0"/> + <dxl:InverseOp Mdid="0.414.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1976.1.0"/> + <dxl:Opfamily Mdid="0.4054.1.0"/> + <dxl:Opfamily Mdid="0.10009.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:Type Mdid="0.29.1.0" Name="cid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2226.1.0"/> + <dxl:EqualityOp Mdid="0.385.1.0"/> + <dxl:InequalityOp Mdid="0.0.0.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1012.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:Type Mdid="0.28.1.0" Name="xid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true"> + <dxl:DistrOpfamily Mdid="0.2225.1.0"/> + <dxl:EqualityOp Mdid="0.352.1.0"/> + <dxl:InequalityOp Mdid="0.3315.1.0"/> + <dxl:LessThanOp Mdid="0.0.0.0"/> + <dxl:LessThanEqualsOp Mdid="0.0.0.0"/> + <dxl:GreaterThanOp Mdid="0.0.0.0"/> + <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/> + <dxl:ComparisonOp Mdid="0.0.0.0"/> + <dxl:ArrayType Mdid="0.1011.1.0"/> + <dxl:MinAgg Mdid="0.0.0.0"/> + <dxl:MaxAgg Mdid="0.0.0.0"/> + <dxl:AvgAgg Mdid="0.0.0.0"/> + <dxl:SumAgg Mdid="0.0.0.0"/> + <dxl:CountAgg Mdid="0.2147.1.0"/> + </dxl:Type> + <dxl:ColumnStatistics Mdid="1.25685.1.0.0" Name="i" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:GPDBAgg Mdid="0.2108.1.0" Name="sum" IsRepSafe="true" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + <dxl:MDCast Mdid="3.23.1.0;23.1.0" Name="int4" BinaryCoercible="true" SourceTypeId="0.23.1.0" DestinationTypeId="0.23.1.0" CastFuncId="0.0.0.0" CoercePathType="0"/> + <dxl:ColumnStatistics Mdid="1.25688.1.0.0" Name="j" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="true"/> + <dxl:GPDBScalarOp Mdid="0.96.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false"> + <dxl:LeftType Mdid="0.23.1.0"/> + <dxl:RightType Mdid="0.23.1.0"/> + <dxl:ResultType Mdid="0.16.1.0"/> + <dxl:OpFunc Mdid="0.65.1.0"/> + <dxl:Commutator Mdid="0.96.1.0"/> + <dxl:InverseOp Mdid="0.518.1.0"/> + <dxl:HashOpfamily Mdid="0.1977.1.0"/> + <dxl:LegacyHashOpfamily Mdid="0.7100.1.0"/> + <dxl:Opfamilies> + <dxl:Opfamily Mdid="0.1976.1.0"/> + <dxl:Opfamily Mdid="0.1977.1.0"/> + <dxl:Opfamily Mdid="0.4054.1.0"/> + <dxl:Opfamily Mdid="0.7100.1.0"/> + <dxl:Opfamily Mdid="0.10009.1.0"/> + </dxl:Opfamilies> + </dxl:GPDBScalarOp> + <dxl:RelationStatistics Mdid="2.25685.1.0" Name="t1" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Relation Mdid="6.25685.1.0" Name="t1" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="7,1"> + <dxl:Columns> + <dxl:Column Name="i" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + </dxl:Relation> + <dxl:RelationStatistics Mdid="2.25688.1.0" Name="t2" Rows="0.000000" RelPages="0" RelAllVisible="0" EmptyRelation="true"/> + <dxl:Relation Mdid="6.25688.1.0" Name="t2" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="7,1"> + <dxl:Columns> + <dxl:Column Name="j" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4"> + <dxl:DefaultValue/> + </dxl:Column> + </dxl:Columns> + <dxl:IndexInfoList/> + <dxl:CheckConstraints/> + <dxl:DistrOpfamilies> + <dxl:DistrOpfamily Mdid="0.1977.1.0"/> + </dxl:DistrOpfamilies> + </dxl:Relation> + <dxl:GPDBAgg Mdid="0.2803.1.0" Name="count" IsRepSafe="true" IsSplittable="true" HashAggCapable="true"> + <dxl:ResultType Mdid="0.20.1.0"/> + <dxl:IntermediateResultType Mdid="0.20.1.0"/> + </dxl:GPDBAgg> + </dxl:Metadata> + <dxl:Query> + <dxl:OutputColumns/> + <dxl:CTEList/> + <dxl:LogicalDelete DeleteColumns="1" CtidCol="2" SegmentIdCol="8"> + <dxl:TableDescriptor Mdid="6.25685.1.0" TableName="t1" LockMode="7" AclMode="10" AssignedQueryIdForTargetRel="1"> + <dxl:Columns> + <dxl:Column ColId="17" Attno="1" ColName="i" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="18" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="19" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="20" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="21" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="22" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="23" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="24" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + <dxl:LogicalSelect> + <dxl:SubqueryAny OperatorName="=" OperatorMdid="0.96.1.0" ColId="9"> + <dxl:Ident ColId="1" ColName="i" TypeMdid="0.23.1.0"/> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="6.25688.1.0" TableName="t2" LockMode="1" AclMode="2"> + <dxl:Columns> + <dxl:Column ColId="9" Attno="1" ColName="j" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="10" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="11" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="12" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="13" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="14" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="15" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="16" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + </dxl:SubqueryAny> + <dxl:LogicalGet> + <dxl:TableDescriptor Mdid="6.25685.1.0" TableName="t1" LockMode="7" AclMode="10" AssignedQueryIdForTargetRel="1"> + <dxl:Columns> + <dxl:Column ColId="1" Attno="1" ColName="i" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="3" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="4" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="8" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:LogicalGet> + </dxl:LogicalSelect> + </dxl:LogicalDelete> + </dxl:Query> + <dxl:Plan Id="0" SpaceSize="33"> + <dxl:DMLDelete Columns="0" ActionCol="20" CtidCol="1" SegmentIdCol="7"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="862.023904" Rows="1.000000" Width="1"/> + </dxl:Properties> + <dxl:DirectDispatchInfo/> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="i"> + <dxl:Ident ColId="0" ColName="i" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:TableDescriptor Mdid="6.25685.1.0" TableName="t1" LockMode="7" AclMode="10" AssignedQueryIdForTargetRel="1"> + <dxl:Columns> + <dxl:Column ColId="21" Attno="1" ColName="i" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="22" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="23" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="24" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="25" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="26" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="27" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="28" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + <dxl:Result> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="862.000466" Rows="1.000000" Width="18"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="i"> + <dxl:Ident ColId="0" ColName="i" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="ctid"> + <dxl:Ident ColId="1" ColName="ctid" TypeMdid="0.27.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="7" Alias="gp_segment_id"> + <dxl:Ident ColId="7" ColName="gp_segment_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="20" Alias="ColRef_0020"> + <dxl:ConstValue TypeMdid="0.23.1.0" Value="0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:OneTimeFilter/> + <dxl:HashJoin JoinType="In"> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="862.000460" Rows="1.000000" Width="14"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="i"> + <dxl:Ident ColId="0" ColName="i" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="ctid"> + <dxl:Ident ColId="1" ColName="ctid" TypeMdid="0.27.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="7" Alias="gp_segment_id"> + <dxl:Ident ColId="7" ColName="gp_segment_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:JoinFilter/> + <dxl:HashCondList> + <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0"> + <dxl:Ident ColId="0" ColName="i" TypeMdid="0.23.1.0"/> + <dxl:Ident ColId="8" ColName="j" TypeMdid="0.23.1.0"/> + </dxl:Comparison> + </dxl:HashCondList> + <dxl:TableScan> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000019" Rows="1.000000" Width="14"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="0" Alias="i"> + <dxl:Ident ColId="0" ColName="i" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="1" Alias="ctid"> + <dxl:Ident ColId="1" ColName="ctid" TypeMdid="0.27.1.0"/> + </dxl:ProjElem> + <dxl:ProjElem ColId="7" Alias="gp_segment_id"> + <dxl:Ident ColId="7" ColName="gp_segment_id" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:TableDescriptor Mdid="6.25685.1.0" TableName="t1" LockMode="7" AclMode="10" AssignedQueryIdForTargetRel="1"> + <dxl:Columns> + <dxl:Column ColId="0" Attno="1" ColName="i" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="1" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="2" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="3" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="4" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="5" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="6" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="7" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:TableScan> + <dxl:TableScan> + <dxl:Properties> + <dxl:Cost StartupCost="0" TotalCost="431.000019" Rows="1.000000" Width="4"/> + </dxl:Properties> + <dxl:ProjList> + <dxl:ProjElem ColId="8" Alias="j"> + <dxl:Ident ColId="8" ColName="j" TypeMdid="0.23.1.0"/> + </dxl:ProjElem> + </dxl:ProjList> + <dxl:Filter/> + <dxl:TableDescriptor Mdid="6.25688.1.0" TableName="t2" LockMode="1" AclMode="2"> + <dxl:Columns> + <dxl:Column ColId="8" Attno="1" ColName="j" TypeMdid="0.23.1.0" ColWidth="4"/> + <dxl:Column ColId="9" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/> + <dxl:Column ColId="10" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="11" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="12" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/> + <dxl:Column ColId="13" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/> + <dxl:Column ColId="14" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/> + <dxl:Column ColId="15" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/> + </dxl:Columns> + </dxl:TableDescriptor> + </dxl:TableScan> + </dxl:HashJoin> + </dxl:Result> + </dxl:DMLDelete> + </dxl:Plan> + </dxl:Thread> +</dxl:DXLMessage> diff --git a/src/backend/gporca/libgpopt/include/gpopt/metadata/CTableDescriptor.h b/src/backend/gporca/libgpopt/include/gpopt/metadata/CTableDescriptor.h index c5974aee26..1875112369 100644 --- a/src/backend/gporca/libgpopt/include/gpopt/metadata/CTableDescriptor.h +++ b/src/backend/gporca/libgpopt/include/gpopt/metadata/CTableDescriptor.h @@ -88,6 +88,9 @@ private: // lockmode from the parser INT m_lockmode; + // acl mode from the parser + INT m_acl_mode; + // identifier of query to which current table belongs. // This field is used for assigning current table entry with // target one within DML operation. If descriptor doesn't point @@ -102,7 +105,7 @@ public: BOOL convert_hash_to_random, IMDRelation::Ereldistrpolicy rel_distr_policy, IMDRelation::Erelstoragetype erelstoragetype, - ULONG ulExecuteAsUser, INT lockmode, + ULONG ulExecuteAsUser, INT lockmode, INT acl_mode, ULONG assigned_query_id_for_target_rel); // dtor @@ -151,6 +154,12 @@ public: return m_lockmode; } + INT + GetAclMode() const + { + return m_acl_mode; + } + // return the position of a particular attribute (identified by attno) ULONG GetAttributePosition(INT attno) const; diff --git a/src/backend/gporca/libgpopt/src/metadata/CTableDescriptor.cpp b/src/backend/gporca/libgpopt/src/metadata/CTableDescriptor.cpp index e5843f771c..580a7cfdea 100644 --- a/src/backend/gporca/libgpopt/src/metadata/CTableDescriptor.cpp +++ b/src/backend/gporca/libgpopt/src/metadata/CTableDescriptor.cpp @@ -38,7 +38,7 @@ CTableDescriptor::CTableDescriptor( CMemoryPool *mp, IMDId *mdid, const CName &name, BOOL convert_hash_to_random, IMDRelation::Ereldistrpolicy rel_distr_policy, IMDRelation::Erelstoragetype erelstoragetype, ULONG ulExecuteAsUser, - INT lockmode, ULONG assigned_query_id_for_target_rel) + INT lockmode, INT acl_mode, ULONG assigned_query_id_for_target_rel) : m_mp(mp), m_mdid(mdid), m_name(mp, name), @@ -52,6 +52,7 @@ CTableDescriptor::CTableDescriptor( m_pdrgpbsKeys(nullptr), m_execute_as_user_id(ulExecuteAsUser), m_lockmode(lockmode), + m_acl_mode(acl_mode), m_assigned_query_id_for_target_rel(assigned_query_id_for_target_rel) { GPOS_ASSERT(nullptr != mp); diff --git a/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp b/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp index a162b416e0..456322499d 100644 --- a/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp +++ b/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp @@ -2143,7 +2143,8 @@ CTranslatorDXLToExpr::Ptabdesc(CDXLTableDescr *table_descr) CTableDescriptor *ptabdesc = GPOS_NEW(m_mp) CTableDescriptor( m_mp, mdid, CName(m_mp, &strName), pmdrel->ConvertHashToRandom(), rel_distr_policy, rel_storage_type, table_descr->GetExecuteAsUserId(), - table_descr->LockMode(), table_descr->GetAssignedQueryIdForTargetRel()); + table_descr->LockMode(), table_descr->GetAclMode(), + table_descr->GetAssignedQueryIdForTargetRel()); const ULONG ulColumns = table_descr->Arity(); for (ULONG ul = 0; ul < ulColumns; ul++) @@ -2342,7 +2343,8 @@ CTranslatorDXLToExpr::PtabdescFromCTAS(CDXLLogicalCTAS *pdxlopCTAS) m_mp, mdid, CName(m_mp, &strName), pmdrel->ConvertHashToRandom(), rel_distr_policy, rel_storage_type, 0, // ulExecuteAsUser, use permissions of current user - 3, // CTEs always use a RowExclusiveLock on the table. See createas.c + 3, // CTAS always uses a RowExclusiveLock on the table. See createas.c + 2, // CTAS always requires SELECT and SELECT only privilege UNASSIGNED_QUERYID); // populate column information from the dxl table descriptor diff --git a/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp b/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp index ed73c62b90..6d653e7888 100644 --- a/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp +++ b/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp @@ -1254,7 +1254,7 @@ CTranslatorExprToDXL::MakeTableDescForPart(const IMDRelation *part, m_mp, part_mdid, part->Mdname().GetMDName(), part->ConvertHashToRandom(), part->GetRelDistribution(), part->RetrieveRelStorageType(), root_table_desc->GetExecuteAsUserId(), - root_table_desc->LockMode(), + root_table_desc->LockMode(), root_table_desc->GetAclMode(), root_table_desc->GetAssignedQueryIdForTargetRel()); for (ULONG ul = 0; ul < part->ColumnCount(); ++ul) @@ -6708,9 +6708,10 @@ CTranslatorExprToDXL::MakeDXLTableDescr( CMDIdGPDB *mdid = CMDIdGPDB::CastMdid(ptabdesc->MDId()); mdid->AddRef(); - CDXLTableDescr *table_descr = GPOS_NEW(m_mp) CDXLTableDescr( - m_mp, mdid, pmdnameTbl, ptabdesc->GetExecuteAsUserId(), - ptabdesc->LockMode(), ptabdesc->GetAssignedQueryIdForTargetRel()); + CDXLTableDescr *table_descr = GPOS_NEW(m_mp) + CDXLTableDescr(m_mp, mdid, pmdnameTbl, ptabdesc->GetExecuteAsUserId(), + ptabdesc->LockMode(), ptabdesc->GetAclMode(), + ptabdesc->GetAssignedQueryIdForTargetRel()); const ULONG ulColumns = ptabdesc->ColumnCount(); // translate col descriptors diff --git a/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLTableDescr.h b/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLTableDescr.h index 4395335e72..b196e4aa9d 100644 --- a/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLTableDescr.h +++ b/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLTableDescr.h @@ -53,6 +53,9 @@ private: // lock mode from the parser INT m_lockmode; + // acl mode from the parser + INT m_acl_mode; + // identifier of query to which current table belongs. // This field is used for assigning current table entry with // target one within DML operation. If descriptor doesn't point @@ -66,7 +69,7 @@ public: // ctor/dtor CDXLTableDescr(CMemoryPool *mp, IMDId *mdid, CMDName *mdname, - ULONG ulExecuteAsUser, int lockmode, + ULONG ulExecuteAsUser, int lockmode, INT acl_mode, ULONG assigned_query_id_for_target_rel = UNASSIGNED_QUERYID); ~CDXLTableDescr() override; @@ -91,6 +94,9 @@ public: // lock mode INT LockMode() const; + // acl mode + INT GetAclMode() const; + // get the column descriptor at the given position const CDXLColDescr *GetColumnDescrAt(ULONG idx) const; diff --git a/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h b/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h index 6daad61c9f..5a2ddb5375 100644 --- a/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h +++ b/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h @@ -450,6 +450,7 @@ enum Edxltoken EdxltokenVersion, EdxltokenMdid, EdxltokenLockMode, + EdxltokenAclMode, EdxltokenMDTypeRequest, EdxltokenTypeInfo, EdxltokenFuncInfo, diff --git a/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp b/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp index 80479f8edf..7541e19214 100644 --- a/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp +++ b/src/backend/gporca/libnaucrates/src/operators/CDXLOperatorFactory.cpp @@ -1526,6 +1526,10 @@ CDXLOperatorFactory::MakeDXLTableDescr(CDXLMemoryManager *dxl_memory_manager, dxl_memory_manager, attrs, EdxltokenLockMode, EdxltokenTableDescr, true /* is_optional */, -1); + INT acl_mode = ExtractConvertAttrValueToInt( + dxl_memory_manager, attrs, EdxltokenAclMode, EdxltokenTableDescr, + true /* is_optional */, -1); + if (nullptr != execute_as_user_xml) { user_id = ConvertAttrValueToUlong( @@ -1537,8 +1541,9 @@ CDXLOperatorFactory::MakeDXLTableDescr(CDXLMemoryManager *dxl_memory_manager, dxl_memory_manager, attrs, EdxltokenAssignedQueryIdForTargetRel, EdxltokenTableDescr, true /* is_optional */, UNASSIGNED_QUERYID); - return GPOS_NEW(mp) CDXLTableDescr(mp, mdid, mdname, user_id, lockmode, - assigned_query_id_for_target_rel); + return GPOS_NEW(mp) + CDXLTableDescr(mp, mdid, mdname, user_id, lockmode, acl_mode, + assigned_query_id_for_target_rel); } //--------------------------------------------------------------------------- diff --git a/src/backend/gporca/libnaucrates/src/operators/CDXLTableDescr.cpp b/src/backend/gporca/libnaucrates/src/operators/CDXLTableDescr.cpp index 8220ec9ded..55e1fc421d 100644 --- a/src/backend/gporca/libnaucrates/src/operators/CDXLTableDescr.cpp +++ b/src/backend/gporca/libnaucrates/src/operators/CDXLTableDescr.cpp @@ -21,6 +21,7 @@ using namespace gpdxl; #define GPDXL_DEFAULT_USERID 0 #define GPDXL_INVALID_LOCKMODE -1 +#define GPDXL_ACL_NO_RIGHTS 0 //--------------------------------------------------------------------------- // @function: @@ -32,12 +33,14 @@ using namespace gpdxl; //--------------------------------------------------------------------------- CDXLTableDescr::CDXLTableDescr(CMemoryPool *mp, IMDId *mdid, CMDName *mdname, ULONG ulExecuteAsUser, int lockmode, + INT acl_mode, ULONG assigned_query_id_for_target_rel) : m_mdid(mdid), m_mdname(mdname), m_dxl_column_descr_array(nullptr), m_execute_as_user_id(ulExecuteAsUser), m_lockmode(lockmode), + m_acl_mode(acl_mode), m_assigned_query_id_for_target_rel(assigned_query_id_for_target_rel) { GPOS_ASSERT(nullptr != m_mdname); @@ -125,6 +128,12 @@ CDXLTableDescr::LockMode() const return m_lockmode; } +INT +CDXLTableDescr::GetAclMode() const +{ + return m_acl_mode; +} + //--------------------------------------------------------------------------- // @function: // CDXLTableDescr::SetColumnDescriptors @@ -220,6 +229,12 @@ CDXLTableDescr::SerializeToDXL(CXMLSerializer *xml_serializer) const CDXLTokens::GetDXLTokenStr(EdxltokenLockMode), LockMode()); } + if (GPDXL_ACL_NO_RIGHTS <= GetAclMode()) + { + xml_serializer->AddAttribute( + CDXLTokens::GetDXLTokenStr(EdxltokenAclMode), GetAclMode()); + } + if (UNASSIGNED_QUERYID != m_assigned_query_id_for_target_rel) { xml_serializer->AddAttribute( diff --git a/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp b/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp index 7b8677b150..435892d760 100644 --- a/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp +++ b/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp @@ -497,6 +497,7 @@ CDXLTokens::Init(CMemoryPool *mp) {EdxltokenVersion, GPOS_WSZ_LIT("Version")}, {EdxltokenMdid, GPOS_WSZ_LIT("Mdid")}, {EdxltokenLockMode, GPOS_WSZ_LIT("LockMode")}, + {EdxltokenAclMode, GPOS_WSZ_LIT("AclMode")}, {EdxltokenMDTypeRequest, GPOS_WSZ_LIT("TypeRequest")}, {EdxltokenTypeInfo, GPOS_WSZ_LIT("TypeInfo")}, {EdxltokenFuncInfo, GPOS_WSZ_LIT("FuncInfo")}, diff --git a/src/backend/gporca/server/src/unittest/CTestUtils.cpp b/src/backend/gporca/server/src/unittest/CTestUtils.cpp index 483862a01d..b557aeecd5 100644 --- a/src/backend/gporca/server/src/unittest/CTestUtils.cpp +++ b/src/backend/gporca/server/src/unittest/CTestUtils.cpp @@ -223,6 +223,7 @@ CTestUtils::PtabdescPlainWithColNameFormat( IMDRelation::EreldistrRandom, IMDRelation::ErelstorageHeap, 0, // ulExecuteAsUser -1, // lockmode + 2, // aclmode SELECT 0 // UNASSIGNED_QUERYID ); diff --git a/src/backend/gporca/server/src/unittest/dxl/statistics/CStatisticsTest.cpp b/src/backend/gporca/server/src/unittest/dxl/statistics/CStatisticsTest.cpp index 3d2cc15746..5a63e2ad42 100644 --- a/src/backend/gporca/server/src/unittest/dxl/statistics/CStatisticsTest.cpp +++ b/src/backend/gporca/server/src/unittest/dxl/statistics/CStatisticsTest.cpp @@ -319,6 +319,7 @@ CStatisticsTest::PtabdescTwoColumnSource(CMemoryPool *mp, IMDRelation::EreldistrRandom, IMDRelation::ErelstorageHeap, 0, // ulExecuteAsUser -1, // lockmode + 2, // aclmode SELECT 0 // UNASSIGNED_QUERYID ); diff --git a/src/backend/gporca/server/src/unittest/gpopt/minidump/CDMLTest.cpp b/src/backend/gporca/server/src/unittest/gpopt/minidump/CDMLTest.cpp index 8303c060ee..1cf9e54f11 100644 --- a/src/backend/gporca/server/src/unittest/gpopt/minidump/CDMLTest.cpp +++ b/src/backend/gporca/server/src/unittest/gpopt/minidump/CDMLTest.cpp @@ -91,6 +91,7 @@ const CHAR *rgszDMLFileNames[] = { "../data/dxl/minidump/DML-Volatile-Function.mdp", "../data/dxl/minidump/UpdateWindowGatherMerge.mdp", "../data/dxl/minidump/UpdateDistKeyWithNestedJoin.mdp", + "../data/dxl/minidump/RTErequiredPerms.mdp", }; //--------------------------------------------------------------------------- diff --git a/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorDXLToExprTest.cpp b/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorDXLToExprTest.cpp index b0dae88931..c433c6396c 100644 --- a/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorDXLToExprTest.cpp +++ b/src/backend/gporca/server/src/unittest/gpopt/translate/CTranslatorDXLToExprTest.cpp @@ -254,6 +254,7 @@ public: CMDRelationGPDB::EreldistrMasterOnly, CMDRelationGPDB::ErelstorageHeap, ulExecuteAsUser, -1, /* lockmode */ + 2, /* aclmode SELECT */ 0 /* UNASSIGNED_QUERYID */); } diff --git a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h index 4c5b29a093..2491bb0d8d 100644 --- a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h +++ b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h @@ -438,8 +438,7 @@ private: // create range table entry from a table descriptor Index ProcessDXLTblDescr(const CDXLTableDescr *table_descr, - CDXLTranslateContextBaseTable *base_table_context, - AclMode acl_mode); + CDXLTranslateContextBaseTable *base_table_context); // translate DXL projection list into a target list List *TranslateDXLProjList( diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 6a9959b6bf..0044eef86f 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -11,6 +11,7 @@ set optimizer=off; -- prevent flakes when we create multiple views. set optimizer_trace_fallback = on; set enable_nestloop=on; +set optimizer_enable_nljoin = on; -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when users/groups don't exist SET client_min_messages TO 'warning'; @@ -191,6 +192,9 @@ DELETE FROM pg_catalog.pg_class; -- fail ERROR: permission denied: "pg_class" is a system catalog UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail ERROR: permission denied: "pg_toast_1213" is a system catalog +-- test ctas +create table atest2_ctas_ok as select col1 from atest2 +where col1 in (select distinct b from atest1); -- ok SET SESSION AUTHORIZATION regress_priv_user3; SELECT session_user, current_user; session_user | current_user @@ -198,6 +202,9 @@ SELECT session_user, current_user; regress_priv_user3 | regress_priv_user3 (1 row) +create table atest2_ctas_fail as select col1 from atest2 +where col1 in (select distinct b from atest1); -- fail +ERROR: permission denied for table atest2 SELECT * FROM atest1; -- ok a | b ---+----- @@ -250,7 +257,7 @@ SELECT * FROM atest1; -- ok -- regress_priv_user1 will own a table and provide views for it. SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atest12 as - SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x; + SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x distributed by (a); CREATE INDEX ON atest12 (a); CREATE INDEX ON atest12 (abs(a)); -- results below depend on having quite accurate stats for atest12, so... @@ -346,6 +353,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; Optimizer: Postgres query optimizer (10 rows) +-- reset the plan cache, sometimes it would re-plan these prepared statements and log ORCA fallbacks discard plans; EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; QUERY PLAN @@ -2677,6 +2685,7 @@ DROP TABLE atest6; DROP TABLE atestc; DROP TABLE atestp1; DROP TABLE atestp2; +DROP TABLE atest2_ctas_ok; -- start_ignore SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; lo_unlink diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges_optimizer.out similarity index 85% copy from src/test/regress/expected/privileges.out copy to src/test/regress/expected/privileges_optimizer.out index 6a9959b6bf..fd4950599a 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges_optimizer.out @@ -5,12 +5,9 @@ -- m/DETAIL: Failing row contains \(.*\) = \(.*\)/ -- s/DETAIL: Failing row contains \(.*\) = \(.*\)/DETAIL: Failing row contains (#####)/ -- end_matchsubs -set optimizer=off; --- We use "discard plans" to reset the plan cache, to re-plan --- prepared statements and log ORCA fallbacks. This should help --- prevent flakes when we create multiple views. set optimizer_trace_fallback = on; set enable_nestloop=on; +set optimizer_enable_nljoin = on; -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when users/groups don't exist SET client_min_messages TO 'warning'; @@ -22,9 +19,10 @@ DROP ROLE IF EXISTS regress_priv_user3; DROP ROLE IF EXISTS regress_priv_user4; DROP ROLE IF EXISTS regress_priv_user5; DROP ROLE IF EXISTS regress_priv_user6; -DROP ROLE IF EXISTS regress_priv_user7; -- start_ignore SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Queries on coordinator-only tables lo_unlink ----------- (0 rows) @@ -39,11 +37,6 @@ CREATE USER regress_priv_user4; CREATE USER regress_priv_user5; CREATE USER regress_priv_user5; -- duplicate ERROR: role "regress_priv_user5" already exists -CREATE USER regress_priv_user6; -CREATE USER regress_priv_user7; -CREATE ROLE regress_priv_role; -GRANT pg_read_all_data TO regress_priv_user6; -GRANT pg_write_all_data TO regress_priv_user7; CREATE GROUP regress_priv_group1; CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2; ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4; @@ -51,20 +44,11 @@ ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" is already a member of role "regress_priv_group2" ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION; --- prepare non-leakproof function for later -CREATE FUNCTION leak(integer,integer) RETURNS boolean - AS 'int4lt' - LANGUAGE internal IMMUTABLE STRICT; -- but deliberately not LEAKPROOF -ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges -GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; -REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error -REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY regress_priv_user2; -- error -REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_USER; -REVOKE regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_ROLE; -DROP ROLE regress_priv_role; SET SESSION AUTHORIZATION regress_priv_user1; SELECT session_user, current_user; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: {SQLVALUEFUNCTION :op 12 :type 19 :typmod -1 :location 7} not supported in DXL session_user | current_user --------------------+-------------------- regress_priv_user1 | regress_priv_user1 @@ -100,12 +84,12 @@ SELECT * FROM atest1; CREATE TABLE atest2 (col1 varchar(10), col2 boolean); GRANT SELECT ON atest2 TO regress_priv_user2; GRANT UPDATE ON atest2 TO regress_priv_user3; -GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER; -GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE; -GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error -ERROR: grantor must be current user +GRANT INSERT ON atest2 TO regress_priv_user4; +GRANT TRUNCATE ON atest2 TO regress_priv_user5; SET SESSION AUTHORIZATION regress_priv_user2; SELECT session_user, current_user; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: {SQLVALUEFUNCTION :op 12 :type 19 :typmod -1 :location 7} not supported in DXL session_user | current_user --------------------+-------------------- regress_priv_user2 | regress_priv_user2 @@ -161,43 +145,21 @@ SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); ------+------ (0 rows) -SET SESSION AUTHORIZATION regress_priv_user6; -SELECT * FROM atest1; -- ok - a | b ----+----- - 1 | two - 1 | two -(2 rows) - -SELECT * FROM atest2; -- ok - col1 | col2 -------+------ -(0 rows) - -INSERT INTO atest2 VALUES ('foo', true); -- fail -ERROR: permission denied for table atest2 -SET SESSION AUTHORIZATION regress_priv_user7; -SELECT * FROM atest1; -- fail -ERROR: permission denied for table atest1 -SELECT * FROM atest2; -- fail -ERROR: permission denied for table atest2 -INSERT INTO atest2 VALUES ('foo', true); -- ok -UPDATE atest2 SET col2 = true; -- ok -DELETE FROM atest2; -- ok --- Make sure we are not able to modify system catalogs -UPDATE pg_catalog.pg_class SET relname = '123'; -- fail -ERROR: permission denied: "pg_class" is a system catalog -DELETE FROM pg_catalog.pg_class; -- fail -ERROR: permission denied: "pg_class" is a system catalog -UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail -ERROR: permission denied: "pg_toast_1213" is a system catalog +-- test ctas +create table atest2_ctas_ok as select col1 from atest2 +where col1 in (select distinct b from atest1); -- ok SET SESSION AUTHORIZATION regress_priv_user3; SELECT session_user, current_user; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: {SQLVALUEFUNCTION :op 12 :type 19 :typmod -1 :location 7} not supported in DXL session_user | current_user --------------------+-------------------- regress_priv_user3 | regress_priv_user3 (1 row) +create table atest2_ctas_fail as select col1 from atest2 +where col1 in (select distinct b from atest1); -- fail +ERROR: permission denied for table atest2 SELECT * FROM atest1; -- ok a | b ---+----- @@ -250,15 +212,13 @@ SELECT * FROM atest1; -- ok -- regress_priv_user1 will own a table and provide views for it. SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atest12 as - SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x; + SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x distributed by (a); CREATE INDEX ON atest12 (a); CREATE INDEX ON atest12 (abs(a)); --- results below depend on having quite accurate stats for atest12, so... -ALTER TABLE atest12 SET (autovacuum_enabled = off); -WARNING: autovacuum is not supported in Cloudberry -SET default_statistics_target = 10000; VACUUM ANALYZE atest12; -RESET default_statistics_target; +CREATE FUNCTION leak(integer,integer) RETURNS boolean + AS $$begin return $1 < $2; end$$ + LANGUAGE plpgsql immutable; CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer, restrict = scalarltsel); -- views with leaky operator @@ -270,40 +230,45 @@ GRANT SELECT ON atest12v TO PUBLIC; GRANT SELECT ON atest12sbv TO PUBLIC; -- This plan should use nestloop, knowing that few rows will be selected. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: atest12_1.b - -> Seq Scan on atest12 atest12_1 - Filter: (b <<< 5) - -> Index Scan using atest12_a_idx on atest12 - Index Cond: (a = atest12_1.b) + -> Hash Join + Hash Cond: (atest12.a = atest12_1.b) + -> Seq Scan on atest12 Filter: (b <<< 5) - Optimizer: Postgres query optimizer -(10 rows) + -> Hash + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: atest12_1.b + -> Seq Scan on atest12 atest12_1 + Filter: (b <<< 5) + Optimizer: Pivotal Optimizer (GPORCA) +(11 rows) -- And this one. EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y WHERE x.a = y.b and abs(y.a) <<< 5; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: y.b - -> Seq Scan on atest12 y - Filter: (abs(a) <<< 5) - -> Index Scan using atest12_a_idx on atest12 x - Index Cond: (a = y.b) - Optimizer: Postgres query optimizer -(9 rows) + -> Hash Join + Hash Cond: (atest12.a = atest12_1.b) + -> Seq Scan on atest12 + -> Hash + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: atest12_1.b + -> Seq Scan on atest12 atest12_1 + Filter: (abs(a) <<< 5) + Optimizer: Pivotal Optimizer (GPORCA) +(10 rows) -- This should also be a nestloop, but the security barrier forces the inner -- scan to be materialized -discard plans; EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: views with security_barrier ON +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) @@ -332,22 +297,28 @@ ERROR: permission denied for table atest12 -- These plans should continue to use a nestloop, since they execute with the -- privileges of the view owner. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: atest12_1.b - -> Seq Scan on atest12 atest12_1 - Filter: (b <<< 5) - -> Index Scan using atest12_a_idx on atest12 - Index Cond: (a = atest12_1.b) + -> Hash Join + Hash Cond: (atest12.a = atest12_1.b) + -> Seq Scan on atest12 Filter: (b <<< 5) - Optimizer: Postgres query optimizer -(10 rows) + -> Hash + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: atest12_1.b + -> Seq Scan on atest12 atest12_1 + Filter: (b <<< 5) + Optimizer: Pivotal Optimizer (GPORCA) +(11 rows) +-- reset the plan cache, sometimes it would re-plan these prepared statements and log ORCA fallbacks discard plans; EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: views with security_barrier ON +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) @@ -366,24 +337,26 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; -- A non-security barrier view does not guard against information leakage. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b and abs(y.a) <<< 5; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: atest12_1.b - -> Seq Scan on atest12 atest12_1 - Filter: ((b <<< 5) AND (abs(a) <<< 5)) - -> Index Scan using atest12_a_idx on atest12 - Index Cond: (a = atest12_1.b) + -> Hash Join + Hash Cond: (atest12.a = atest12_1.b) + -> Seq Scan on atest12 Filter: (b <<< 5) - Optimizer: Postgres query optimizer -(10 rows) + -> Hash + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: atest12_1.b + -> Seq Scan on atest12 atest12_1 + Filter: ((b <<< 5) AND (abs(a) <<< 5)) + Optimizer: Pivotal Optimizer (GPORCA) +(11 rows) -- But a security barrier view isolates the leaky operator. -discard plans; EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b and abs(y.a) <<< 5; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: views with security_barrier ON QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) @@ -407,19 +380,20 @@ GRANT SELECT (a, b) ON atest12 TO PUBLIC; SET SESSION AUTHORIZATION regress_priv_user2; -- regress_priv_user2 should continue to get a good row estimate. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: atest12_1.b - -> Seq Scan on atest12 atest12_1 - Filter: (b <<< 5) - -> Index Scan using atest12_a_idx on atest12 - Index Cond: (a = atest12_1.b) + -> Hash Join + Hash Cond: (atest12.a = atest12_1.b) + -> Seq Scan on atest12 Filter: (b <<< 5) - Optimizer: Postgres query optimizer -(10 rows) + -> Hash + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: atest12_1.b + -> Seq Scan on atest12 atest12_1 + Filter: (b <<< 5) + Optimizer: Pivotal Optimizer (GPORCA) +(11 rows) -- But not for this, due to lack of table-wide permissions needed -- to make use of the expression index's statistics. @@ -429,14 +403,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y ------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Hash Join - Hash Cond: (x.a = y.b) - -> Seq Scan on atest12 x + Hash Cond: (atest12.a = atest12_1.b) + -> Seq Scan on atest12 -> Hash -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: y.b - -> Seq Scan on atest12 y + Hash Key: atest12_1.b + -> Seq Scan on atest12 atest12_1 Filter: (abs(a) <<< 5) - Optimizer: Postgres query optimizer + Optimizer: Pivotal Optimizer (GPORCA) (10 rows) -- clean up (regress_priv_user1's objects are all dropped later) @@ -574,54 +548,8 @@ SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail ERROR: permission denied for table atest5 SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail ERROR: permission denied for table atest5 -SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail -ERROR: permission denied for table atest5 -SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail -ERROR: permission denied for table atest5 SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail ERROR: permission denied for table atest5 -SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok - one ------ - 1 -(1 row) - -SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok - one ------ - 1 -(1 row) - -SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 -SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 -SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 -SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail -ERROR: permission denied for table atest5 -SELECT * FROM (atest5 a JOIN atest5 b USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT a.* FROM (atest5 a JOIN atest5 b USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 -SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail -ERROR: permission denied for table atest5 SELECT 1 FROM atest5 WHERE two = 2; -- fail ERROR: permission denied for table atest5 SELECT * FROM atest1, atest5; -- fail @@ -674,6 +602,8 @@ ERROR: permission denied for table atest5 INSERT INTO atest5 VALUES (5,5,5); -- fail ERROR: permission denied for table atest5 UPDATE atest5 SET three = 10; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Operator Update on replicated tables not supported UPDATE atest5 SET one = 8; -- fail ERROR: permission denied for table atest5 UPDATE atest5 SET three = 5, one = 2; -- fail @@ -681,11 +611,15 @@ ERROR: permission denied for table atest5 -- Check that column level privs are enforced in RETURNING -- Ok. INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ON CONFLICT clause -- Error. No SELECT on column three. INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three; ERROR: permission denied for table atest5 -- Ok. May SELECT on column "one": INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: RETURNING clause one ----- @@ -694,6 +628,8 @@ INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RE -- Check that column level privileges are enforced for EXCLUDED -- Ok. we may select one INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ON CONFLICT clause -- Error. No select rights on three INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; ERROR: permission denied for table atest5 @@ -716,7 +652,11 @@ SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (four) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ON CONFLICT clause INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: ON CONFLICT clause SET SESSION AUTHORIZATION regress_priv_user1; REVOKE ALL (one) ON atest5 FROM regress_priv_user4; GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4; @@ -726,6 +666,8 @@ ERROR: permission denied for table atest5 UPDATE atest5 SET one = 1; -- fail ERROR: permission denied for table atest5 SELECT atest6 FROM atest6; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Whole-row variable atest6 -------- (0 rows) @@ -749,13 +691,13 @@ ERROR: duplicate key value violates unique constraint "t1_pkey" UPDATE t1 SET c2 = 1; -- fail, but row not shown ERROR: duplicate key value violates unique constraint "t1_pkey" INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted -ERROR: null value in column "c1" of relation "t1" violates not-null constraint +ERROR: null value in column "c1" violates not-null constraint DETAIL: Failing row contains (c1, c2) = (null, null). INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT -ERROR: null value in column "c1" of relation "t1" violates not-null constraint +ERROR: null value in column "c1" violates not-null constraint DETAIL: Failing row contains (c1, c3) = (null, null). INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT -ERROR: null value in column "c2" of relation "t1" violates not-null constraint +ERROR: null value in column "c2" violates not-null constraint DETAIL: Failing row contains (c1) = (5). UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified ERROR: new row for relation "t1" violates check constraint "t1_c3_check" @@ -763,9 +705,9 @@ DETAIL: Failing row contains (c1, c3) = (1, 10). SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE t1; -- check error reporting with column privs on a partitioned table -CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a) DISTRIBUTED BY (c); -CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text) DISTRIBUTED BY (c); -CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL) DISTRIBUTED BY (c); +CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a); +CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text) DISTRIBUTED BY (a); +CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL) DISTRIBUTED BY (a); ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa'); ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa'); GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2; @@ -778,26 +720,26 @@ SET SESSION AUTHORIZATION regress_priv_user2; -- the error messages don't leak the secret fields. -- simple insert. INSERT INTO errtst (a, b) VALUES ('aaa', NULL); -ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint +ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaa, null, null). -- simple update. UPDATE errtst SET b = NULL; -ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint -DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc). +ERROR: null value in column "b" violates not-null constraint +DETAIL: Failing row contains (b) = (null). -- partitioning key is updated, doesn't move the row. UPDATE errtst SET a = 'aaa', b = NULL; -ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint +ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc). -- row is moved to another partition. UPDATE errtst SET a = 'aaaa', b = NULL; -ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint +ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). -- row is moved to another partition. This differs from the previous case in -- that the new partition is excluded by constraint exclusion, so its -- ResultRelInfo is not created at ExecInitModifyTable, but needs to be -- constructed on the fly when the updated tuple is routed to it. UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; -ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint +ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE errtst; @@ -817,6 +759,8 @@ SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 DROP COLUMN three; SET SESSION AUTHORIZATION regress_priv_user4; SELECT atest6 FROM atest6; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Whole-row variable atest6 -------- (0 rows) @@ -838,30 +782,40 @@ SET SESSION AUTHORIZATION regress_priv_user3; DELETE FROM atest5 WHERE one = 1; -- fail ERROR: permission denied for table atest5 DELETE FROM atest5 WHERE two = 2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Operator Delete on replicated tables not supported -- check inheritance cases SET SESSION AUTHORIZATION regress_priv_user1; -CREATE TABLE atestp1 (f1 int, f2 int) DISTRIBUTED RANDOMLY; +CREATE TABLE atestp1 (f1 int, f2 int); CREATE TABLE atestp2 (fx int, fy int); CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2; GRANT SELECT(fx) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables fx ---- (0 rows) SELECT fy FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables fy ---- (0 rows) SELECT atestp2 FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables atestp2 --------- (0 rows) SELECT tableoid FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables tableoid ---------- (0 rows) @@ -872,53 +826,33 @@ SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- still ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables fx ---- (0 rows) SELECT fy FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables fy ---- (0 rows) SELECT atestp2 FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables atestp2 --------- (0 rows) SELECT tableoid FROM atestp2; -- ok +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: Inherited tables tableoid ---------- (0 rows) --- child's permissions do not apply when operating on parent -SET SESSION AUTHORIZATION regress_priv_user1; -REVOKE ALL ON atestc FROM regress_priv_user2; -GRANT ALL ON atestp1 TO regress_priv_user2; -SET SESSION AUTHORIZATION regress_priv_user2; -SELECT f2 FROM atestp1; -- ok - f2 ----- -(0 rows) - -SELECT f2 FROM atestc; -- fail -ERROR: permission denied for table atestc -DELETE FROM atestp1; -- ok -DELETE FROM atestc; -- fail -ERROR: permission denied for table atestc -UPDATE atestp1 SET f1 = 1; -- ok -UPDATE atestc SET f1 = 1; -- fail -ERROR: permission denied for table atestc -TRUNCATE atestp1; -- ok -TRUNCATE atestc; -- fail -ERROR: permission denied for table atestc -BEGIN; -LOCK atestp1; -END; -BEGIN; -LOCK atestc; -ERROR: permission denied for table atestc -END; -- privileges on functions, languages -- switch to superuser \c - @@ -1461,13 +1395,7 @@ select has_column_privilege('mytable','........pg.dropped.2........','select'); select has_column_privilege('mytable',2::int2,'select'); has_column_privilege ---------------------- - -(1 row) - -select has_column_privilege('mytable',99::int2,'select'); - has_column_privilege ----------------------- - + t (1 row) revoke select on table mytable from regress_priv_user3; @@ -1477,12 +1405,6 @@ select has_column_privilege('mytable',2::int2,'select'); (1 row) -select has_column_privilege('mytable',99::int2,'select'); - has_column_privilege ----------------------- - -(1 row) - drop table mytable; -- Grant options SET SESSION AUTHORIZATION regress_priv_user1; @@ -1552,7 +1474,8 @@ CREATE INDEX sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))) -- REINDEX REINDEX TABLE sro_tab; REINDEX INDEX sro_idx; -REINDEX TABLE sro_tab; +REINDEX TABLE CONCURRENTLY sro_tab; +ERROR: REINDEX CONCURRENTLY is not supported DROP INDEX sro_idx; -- CLUSTER CREATE INDEX sro_cluster_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))); @@ -1563,13 +1486,12 @@ CREATE INDEX sro_brin ON sro_tab USING brin ((sro_ifun(a) + sro_ifun(0))); SELECT brin_desummarize_range('sro_brin', 0); brin_desummarize_range ------------------------ - -(1 row) +(0 rows) SELECT brin_summarize_range('sro_brin', 0); brin_summarize_range ---------------------- - 0 + 2 (1 row) DROP TABLE sro_tab; @@ -1582,7 +1504,8 @@ INSERT INTO sro_ptab VALUES (1), (2), (3); CREATE INDEX sro_pidx ON sro_ptab ((sro_ifun(a) + sro_ifun(0))) WHERE sro_ifun(a + 10) > sro_ifun(10); REINDEX TABLE sro_ptab; -REINDEX INDEX sro_pidx; +REINDEX INDEX CONCURRENTLY sro_pidx; +ERROR: REINDEX CONCURRENTLY is not supported SET SESSION AUTHORIZATION regress_sro_user; CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 'GRANT regress_priv_group2 TO regress_sro_user'; @@ -1609,14 +1532,17 @@ CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation +CONTEXT: SQL function "mv_action" statement 1 \c - REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation -BEGIN; SET allow_segment_DML = ON; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: must have admin option on role "regress_priv_group2" -CONTEXT: SQL function "unwanted_grant" statement 1 +CONTEXT: SQL function "mv_action" statement 1 +BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; +ERROR: function cannot execute on a QE slice because it issues a non-SELECT statement +CONTEXT: SQL function "unwanted_grant" during startup SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM +SQL function "mv_action" statement 1 -- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions() SET SESSION AUTHORIZATION regress_sro_user; CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int @@ -1628,17 +1554,10 @@ BEGIN EXCEPTION WHEN OTHERS THEN RETURN 2; END$$; -CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c; --- start_ignore --- GPDB_14_MERGE_FIXME: the following command will abort the sub-transaction --- in a DDL. The problem is that aborting the sub-transaction will also erase --- the `dispatch_oids` needed by the QEs. It's a rare case. --- We don't support this case now +CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c DISTRIBUTED BY (c); CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0; -ERROR: no pre-assigned OID for pg_class tuple "sro_index_mv_c_idx" (namespace:2200 keyOid1:0 keyOid2:0) (oid_dispatch.c:371) --- end_ignore \c - --- REFRESH MATERIALIZED VIEW CONCURRENTLY sro_index_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY sro_index_mv; REFRESH MATERIALIZED VIEW sro_index_mv; DROP OWNED BY regress_sro_user; DROP ROLE regress_sro_user; @@ -1918,67 +1837,6 @@ SELECT * FROM pg_largeobject LIMIT 0; SET SESSION AUTHORIZATION regress_priv_user1; SELECT * FROM pg_largeobject LIMIT 0; -- to be denied ERROR: permission denied for table pg_largeobject --- test pg_database_owner -RESET SESSION AUTHORIZATION; -GRANT pg_database_owner TO regress_priv_user1; -ERROR: role "pg_database_owner" cannot have explicit members -GRANT regress_priv_user1 TO pg_database_owner; -ERROR: role "pg_database_owner" cannot be a member of any role -CREATE TABLE datdba_only (); -ALTER TABLE datdba_only OWNER TO pg_database_owner; -REVOKE DELETE ON datdba_only FROM pg_database_owner; -SELECT - pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv, - pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem, - pg_has_role('regress_priv_user1', 'pg_database_owner', - 'MEMBER WITH ADMIN OPTION') as admin; - priv | mem | admin -------+-----+------- - f | f | f -(1 row) - -BEGIN; -DO $$BEGIN EXECUTE format( - 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$; -SELECT - pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv, - pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem, - pg_has_role('regress_priv_user1', 'pg_database_owner', - 'MEMBER WITH ADMIN OPTION') as admin; - priv | mem | admin -------+-----+------- - t | t | f -(1 row) - -SET SESSION AUTHORIZATION regress_priv_user1; -TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C"; - role_name ---------------------- - pg_database_owner - regress_priv_group2 - regress_priv_user1 -(3 rows) - -TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C"; - grantee | role_name | is_grantable ----------------------+---------------------+-------------- - regress_priv_group2 | pg_database_owner | NO - regress_priv_user1 | regress_priv_group2 | NO -(2 rows) - -INSERT INTO datdba_only DEFAULT VALUES; -SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q; -ERROR: permission denied for table datdba_only -SET SESSION AUTHORIZATION regress_priv_user2; -TABLE information_schema.enabled_roles; - role_name --------------------- - regress_priv_user2 -(1 row) - -INSERT INTO datdba_only DEFAULT VALUES; -ERROR: permission denied for table datdba_only -ROLLBACK; -- test default ACLs \c - CREATE SCHEMA testns; @@ -2071,12 +1929,6 @@ SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes t (1 row) -SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes - has_schema_privilege ----------------------- - t -(1 row) - SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no has_schema_privilege ---------------------- @@ -2113,35 +1965,6 @@ SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2; COMMIT; --- Test for DROP OWNED BY with shared dependencies. This is done in a --- separate, rollbacked, transaction to avoid any trouble with other --- regression sessions. -BEGIN; -ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON TYPES TO regress_priv_user2; -SELECT count(*) FROM pg_shdepend - WHERE deptype = 'a' AND - refobjid = 'regress_priv_user2'::regrole AND - classid = 'pg_default_acl'::regclass; - count -------- - 5 -(1 row) - -DROP OWNED BY regress_priv_user2, regress_priv_user2; -SELECT count(*) FROM pg_shdepend - WHERE deptype = 'a' AND - refobjid = 'regress_priv_user2'::regrole AND - classid = 'pg_default_acl'::regclass; - count -------- - 0 -(1 row) - -ROLLBACK; CREATE SCHEMA testns5; SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no has_schema_privilege @@ -2568,6 +2391,7 @@ CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation +CONTEXT: SQL function "mv_action" statement 1 \c - REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation @@ -2577,9 +2401,10 @@ ERROR: must have admin option on role "regress_priv_group2" CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM +SQL function "mv_action" statement 1 DROP OWNED BY regress_sro_user; DROP ROLE regress_sro_user; --- Test sandbox escape with CVE-2020-25695 on Cloudberry. +-- Test sandbox escape with CVE-2020-25695 on Greenplum. -- -- The idea of CVE-2020-25695 is to make superuser fire a defered -- trigger with security invoker function that executes some malicious @@ -2677,6 +2502,7 @@ DROP TABLE atest6; DROP TABLE atestc; DROP TABLE atestp1; DROP TABLE atestp2; +DROP TABLE atest2_ctas_ok; -- start_ignore SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; lo_unlink @@ -2700,9 +2526,7 @@ DROP USER regress_priv_user3; DROP USER regress_priv_user4; DROP USER regress_priv_user5; DROP USER regress_priv_user6; -DROP USER regress_priv_user7; -DROP USER regress_priv_user8; -- does not exist -ERROR: role "regress_priv_user8" does not exist +ERROR: role "regress_priv_user6" does not exist -- permissions with LOCK TABLE CREATE USER regress_locktable_user; CREATE TABLE lock_table (a int); @@ -2783,10 +2607,6 @@ LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass COMMIT; \c REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; --- regression test: superuser create a schema and authorize it to a non-superuser -DROP ROLE IF EXISTS "non_superuser_schema"; -CREATE ROLE "non_superuser_schema"; -CREATE SCHEMA test_non_superuser_schema AUTHORIZATION "non_superuser_schema"; -- clean up DROP TABLE lock_table; DROP USER regress_locktable_user; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 95f9188b15..4e45d44754 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -13,6 +13,8 @@ set optimizer=off; set optimizer_trace_fallback = on; set enable_nestloop=on; +set optimizer_enable_nljoin = on; + -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when users/groups don't exist @@ -145,10 +147,16 @@ DELETE FROM atest2; -- ok UPDATE pg_catalog.pg_class SET relname = '123'; -- fail DELETE FROM pg_catalog.pg_class; -- fail UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail +-- test ctas +create table atest2_ctas_ok as select col1 from atest2 +where col1 in (select distinct b from atest1); -- ok SET SESSION AUTHORIZATION regress_priv_user3; SELECT session_user, current_user; +create table atest2_ctas_fail as select col1 from atest2 +where col1 in (select distinct b from atest1); -- fail + SELECT * FROM atest1; -- ok SELECT * FROM atest2; -- fail INSERT INTO atest1 VALUES (2, 'two'); -- fail @@ -184,7 +192,7 @@ SELECT * FROM atest1; -- ok SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atest12 as - SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x; + SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x distributed by (a); CREATE INDEX ON atest12 (a); CREATE INDEX ON atest12 (abs(a)); -- results below depend on having quite accurate stats for atest12, so... @@ -232,6 +240,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0; -- privileges of the view owner. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; +-- reset the plan cache, sometimes it would re-plan these prepared statements and log ORCA fallbacks discard plans; EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; @@ -1622,6 +1631,7 @@ DROP TABLE atest6; DROP TABLE atestc; DROP TABLE atestp1; DROP TABLE atestp2; +DROP TABLE atest2_ctas_ok; -- start_ignore SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
