postgresql普通用户禁用分区表触发器时失败

postgresql普通用户禁用分区表触发器时失败

问题概述

禁用分区表触发器时报错无权限，普通用户权限一致时，在pg12和pg14测试都存在该问题，pg15可以禁用成功

pg12

test_db=> \d+ test_p_t
                           Partitioned table "public.test_p_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
 num    | integer |           |          |         | plain   |              |
Partition key: RANGE (id)
Indexes:
    "test_p_t_id_idx" btree (id)
    "test_p_t_num_idx" btree (num)
Triggers:
    test_ptt AFTER UPDATE ON test_p_t FOR EACH ROW EXECUTE FUNCTION test_ptt()
Partitions: test_p_t_1 FOR VALUES FROM (1) TO (10),
            test_p_t_2 FOR VALUES FROM (11) TO (20),
            test_p_t_3 FOR VALUES FROM (21) TO (30)

test_db=> alter table test_p_t disable trigger test_ptt ;
ERROR:  permission denied: "test_ptt" is a system trigger
test_db=> \c
You are now connected to database "test_db" as user "test".
test_db=> \du+ test
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 test      |            | {}        |

test_db=> select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)

pg14

postgres=> \d+ test_p_t
                                  Partitioned table "public.test_p_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |              |
 num    | integer |           |          |         | plain   |             |              |
Partition key: RANGE (id)
Triggers:
    test_ptt AFTER UPDATE ON test_p_t FOR EACH ROW EXECUTE FUNCTION test_ptt()
Partitions: test_p_t_1 FOR VALUES FROM (1) TO (10),
            test_p_t_2 FOR VALUES FROM (11) TO (20),
            test_p_t_3 FOR VALUES FROM (21) TO (30)

postgres=> alter table test_p_t disable trigger test_ptt ;
ERROR:  permission denied: "test_ptt" is a system trigger
postgres=> \c
You are now connected to database "postgres" as user "yhru".
postgres=> \du+ yhru
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 yhru      |            | {}        |

postgres=> select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)

pg15

postgres=> \d+ test_p_t
                                  Partitioned table "public.test_p_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |              |
 num    | integer |           |          |         | plain   |             |              |
Partition key: RANGE (id)
Triggers:
    test_ptt AFTER UPDATE ON test_p_t FOR EACH ROW EXECUTE FUNCTION test_ptt()
Partitions: test_p_t_1 FOR VALUES FROM (1) TO (10),
            test_p_t_2 FOR VALUES FROM (11) TO (20),
            test_p_t_3 FOR VALUES FROM (21) TO (30)

postgres=> \c
You are now connected to database "postgres" as user "yhru".
postgres=> alter table test_p_t disable trigger test_ptt ;
ALTER TABLE
postgres=> \c
You are now connected to database "postgres" as user "yhru".
postgres=> \du+ yhru
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 yhru      |            | {}        |

postgres=> select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 15.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1, 64-bit
(1 row)

postgres=> \d+ test_p_t
                                  Partitioned table "public.test_p_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |              |
 num    | integer |           |          |         | plain   |             |              |
Partition key: RANGE (id)
Disabled user triggers:
    test_ptt AFTER UPDATE ON test_p_t FOR EACH ROW EXECUTE FUNCTION test_ptt()
Partitions: test_p_t_1 FOR VALUES FROM (1) TO (10),
            test_p_t_2 FOR VALUES FROM (11) TO (20),
            test_p_t_3 FOR VALUES FROM (21) TO (30)

解决方案

如果使用的是pg15以前的版本，要禁用分区表触发器有两种方式

1）使用superuser禁用

test_db=> \c - postgres
You are now connected to database "test_db" as user "postgres".
test_db=# alter table test_p_t disable trigger test_ptt ;
ALTER TABLE
test_db=# \d+ test_p_t
                           Partitioned table "public.test_p_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
 num    | integer |           |          |         | plain   |              |
Partition key: RANGE (id)
Indexes:
    "test_p_t_id_idx" btree (id)
    "test_p_t_num_idx" btree (num)
Disabled user triggers:
    test_ptt AFTER UPDATE ON test_p_t FOR EACH ROW EXECUTE FUNCTION test_ptt()
Partitions: test_p_t_1 FOR VALUES FROM (1) TO (10),
            test_p_t_2 FOR VALUES FROM (11) TO (20),
            test_p_t_3 FOR VALUES FROM (21) TO (30)

2）普通用户drop trigger，需要使用时再次创建

test_db=> drop trigger test_ptt on test_p_t;
DROP TRIGGER
test_db=> \d+ test_p_t
                           Partitioned table "public.test_p_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
 num    | integer |           |          |         | plain   |              |
Partition key: RANGE (id)
Indexes:
    "test_p_t_id_idx" btree (id)
    "test_p_t_num_idx" btree (num)
Partitions: test_p_t_1 FOR VALUES FROM (1) TO (10),
            test_p_t_2 FOR VALUES FROM (11) TO (20),
            test_p_t_3 FOR VALUES FROM (21) TO (30)

test_db=> \c
You are now connected to database "test_db" as user "test".