postgres.git / summary / log / commit / refs

commit    d9abd9e1050db4c2fcdce6fcd8dc0ede6769db16
Author:   Michael Paquier <michael@paquier.xyz>
Date:     Mon Jan 26 07:32:33 2026 +0000

    Add test for MAINTAIN permission with pg_restore_extended_stats()
    
    Like its cousin functions for the restore of relation and attribute
    stats, pg_restore_extended_stats() needs to be run by a user that is the
    database owner or has MAINTAIN privileges on the table whose stats are
    restored.  This commit adds a regression test ensuring that MAINTAIN is
    required when calling the function.  This test also checks that a
    ShareUpdateExclusive lock is taken on the table whose stats are
    restored.
    
    This has been split from the commit that has introduced
    pg_restore_extended_stats(), for clarity.
    
    Author: Corey Huinker <corey.huinker@gmail.com>
    Reviewed-by: Chao Li <li.evan.chao@gmail.com>
    Reviewed-by: Michael Paquier <michael@paquier.xyz>
    Discussion: https://postgr.es/m/CADkLM=dpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqho=qN3kX0Zg@mail.gmail.com


 src/test/regress/expected/stats_import.out | 42 ++++++++++++++++++++++++++++++
 src/test/regress/sql/stats_import.sql      | 32 +++++++++++++++++++++++
 2 files changed, 74 insertions(+)

diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index acab1367855..9c2362e70c6 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -1685,6 +1685,48 @@ WARNING:  could not restore extended statistics object "stats_import"."test_stat
  f
 (1 row)
 
+-- Check that MAINTAIN is required when restoring statistics.
+CREATE ROLE regress_test_extstat_restore;
+GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_restore;
+SET ROLE regress_test_extstat_restore;
+-- No data to restore; this fails on a permission failure.
+SELECT pg_catalog.pg_restore_extended_stats(
+  'schemaname', 'stats_import',
+  'relname', 'test_clone',
+  'statistics_schemaname', 'stats_import',
+  'statistics_name', 'test_stat_clone',
+  'inherited', false);
+ERROR:  permission denied for table test_clone
+RESET ROLE;
+GRANT MAINTAIN ON stats_import.test_clone TO regress_test_extstat_restore;
+SET ROLE regress_test_extstat_restore;
+-- This works, check the lock on the relation while on it.
+BEGIN;
+SELECT pg_catalog.pg_restore_extended_stats(
+  'schemaname', 'stats_import',
+  'relname', 'test_clone',
+  'statistics_schemaname', 'stats_import',
+  'statistics_name', 'test_stat_clone',
+  'inherited', false,
+  'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}]'::pg_ndistinct);
+ pg_restore_extended_stats 
+---------------------------
+ t
+(1 row)
+
+SELECT mode FROM pg_locks WHERE locktype = 'relation' AND
+  relation = 'stats_import.test_clone'::regclass AND
+  pid = pg_backend_pid();
+           mode           
+--------------------------
+ ShareUpdateExclusiveLock
+(1 row)
+
+COMMIT;
+RESET ROLE;
+REVOKE MAINTAIN ON stats_import.test_clone FROM regress_test_extstat_restore;
+REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_restore;
+DROP ROLE regress_test_extstat_restore;
 -- ndistinct value doesn't match object definition
 SELECT pg_catalog.pg_restore_extended_stats(
   'schemaname', 'stats_import',
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 5d35de1bc88..c9b1d9da2f7 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -1210,6 +1210,38 @@ SELECT pg_catalog.pg_restore_extended_stats(
   'statistics_name', 'test_stat_clone',
   'inherited', false);
 
+-- Check that MAINTAIN is required when restoring statistics.
+CREATE ROLE regress_test_extstat_restore;
+GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_restore;
+SET ROLE regress_test_extstat_restore;
+-- No data to restore; this fails on a permission failure.
+SELECT pg_catalog.pg_restore_extended_stats(
+  'schemaname', 'stats_import',
+  'relname', 'test_clone',
+  'statistics_schemaname', 'stats_import',
+  'statistics_name', 'test_stat_clone',
+  'inherited', false);
+RESET ROLE;
+GRANT MAINTAIN ON stats_import.test_clone TO regress_test_extstat_restore;
+SET ROLE regress_test_extstat_restore;
+-- This works, check the lock on the relation while on it.
+BEGIN;
+SELECT pg_catalog.pg_restore_extended_stats(
+  'schemaname', 'stats_import',
+  'relname', 'test_clone',
+  'statistics_schemaname', 'stats_import',
+  'statistics_name', 'test_stat_clone',
+  'inherited', false,
+  'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}]'::pg_ndistinct);
+SELECT mode FROM pg_locks WHERE locktype = 'relation' AND
+  relation = 'stats_import.test_clone'::regclass AND
+  pid = pg_backend_pid();
+COMMIT;
+RESET ROLE;
+REVOKE MAINTAIN ON stats_import.test_clone FROM regress_test_extstat_restore;
+REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_restore;
+DROP ROLE regress_test_extstat_restore;
+
 -- ndistinct value doesn't match object definition
 SELECT pg_catalog.pg_restore_extended_stats(
   'schemaname', 'stats_import',


[parent: 114e84c532d1]