postgresql普通用户禁用分区表触发器时失败--后续（原理分析）

postgresql普通用户禁用分区表触发器时失败–后续（原理分析）

上一篇只是描述了普通用户禁用分区表触发器时失败的现象，和处理方法，本篇再介绍一下禁用失败的原理。另外之前测试的pg12和pg14小版本也不是最新的，本次用最新小版本再次进行验证。

验证pg12和pg14最新小版本同样存在该现象

pg12.14

postgres=> \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)
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=> select * from pg_trigger ;
  oid  | tgrelid |  tgname  | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnar
gs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+----------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+------
---+--------+--------+--------+------------+------------
 16415 |   16402 | test_ptt |  16414 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
 16416 |   16405 | test_ptt |  16414 |     17 | O         | t            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
 16417 |   16408 | test_ptt |  16414 |     17 | O         | t            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
 16418 |   16411 | test_ptt |  16414 |     17 | O         | t            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
(4 rows)

postgres=> alter table test_p_t disable trigger test_ptt ;
ERROR:  permission denied: "test_ptt" is a system trigger
postgres=> select version();
                                   version
------------------------------------------------------------------------------
 PostgreSQL 12.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1, 64-bit
(1 row)

postgres=> \c
psql (15.1, server 12.14)
You are now connected to database "postgres" as user "yhru".
复制

pg14.7

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=> select * from pg_trigger ;
  oid  | tgrelid | tgparentid |  tgname  | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdef
erred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+------------+----------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------
------+---------+--------+--------+--------+------------+------------
 16399 |   16385 |          0 | test_ptt |  16398 |     17 | O         | f            |             0 |             0 |            0 | f            | f
      |       0 |        | \x     |        |            |
 16400 |   16388 |      16399 | test_ptt |  16398 |     17 | O         | t            |             0 |             0 |            0 | f            | f
      |       0 |        | \x     |        |            |
 16401 |   16391 |      16399 | test_ptt |  16398 |     17 | O         | t            |             0 |             0 |            0 | f            | f
      |       0 |        | \x     |        |            |
 16402 |   16394 |      16399 | test_ptt |  16398 |     17 | O         | t            |             0 |             0 |            0 | f            | f
      |       0 |        | \x     |        |            |
(4 rows)

postgres=> alter table test_p_t disable trigger test_ptt ;
ERROR:  permission denied: "test_ptt" is a system trigger
postgres=> select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1, 64-bit
(1 row)

postgres=> \c
psql (15.1, server 14.7)
You are now connected to database "postgres" as user "yhru".
复制

原理分析

可以看到禁用失败的报错是“ERROR: permission denied: “test_ptt” is a system trigger”，查看pg_trigger表可以发现分区上触发器的tgisinternal属性为true

postgres=# select tgrelid::regclass,tgname,tgisinternal from pg_trigger ;
  tgrelid   |  tgname  | tgisinternal
------------+----------+--------------
 test_p_t   | test_ptt | f
 test_p_t_1 | test_ptt | t
 test_p_t_2 | test_ptt | t
 test_p_t_3 | test_ptt | t
(4 rows)
复制

查看源码发现，当tgisinternal为true时，会判断是不是superuser，正好可以和之前报错对应

src/backend/commands/tablecmds.c

case AT_DisableTrig:    /* DISABLE TRIGGER name */
        ATExecEnableDisableTrigger(rel, cmd->name,
                                                           TRIGGER_DISABLED, false,
                                                           cmd->recurse,
                                                           lockmode);
        break;
......
ATExecEnableDisableTrigger(Relation rel, const char *trigname,
                                                   char fires_when, bool skip_system, bool recurse,
                                                   LOCKMODE lockmode)
{
        EnableDisableTriggerNew(rel, trigname, fires_when, skip_system, recurse,
                                                        lockmode);
}


复制

src/backend/commands/trigger.c

EnableDisableTriggerNew(Relation rel, const char *tgname,
                                                char fires_when, bool skip_system, bool recurse,
                                                LOCKMODE lockmode)
{
......
if (oldtrig->tgisinternal)
                {
                        /* system trigger ... ok to process? */
                        if (skip_system)
                                continue;
                        if (!superuser())
                                ereport(ERROR,
                                                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                                                 errmsg("permission denied: \"%s\" is a system trigger",
                                                                NameStr(oldtrig->tgname))));
                }
复制

既然报错是因为tgisinternal为true，那么继续查找tgisinternal的true是怎么来的，对比pg15.1和pg12.14源码可以发现，在给tgisinternal赋值时是有差异的，pg15.1是“values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal)”，而pg12.14是”values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition)“，其中isInternal和in_partition在调用时传入的值分别是（false, true），两个版本传入值一致。

src/backend/commands/tablecmds.c

/*
 * CloneRowTriggersToPartition
 *              subroutine for ATExecAttachPartition/DefineRelation to create row
 *              triggers on partitions
 */
static void
CloneRowTriggersToPartition(Relation parent, Relation partition)
{
......
    CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
                                             trigForm->tgconstrrelid, InvalidOid, InvalidOid,
                                             trigForm->tgfoid, trigForm->oid, qual,
                                             false, true, trigForm->tgenabled);
复制

pg15.1,src/backend/commands/trigger.c

CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
                                          Oid relOid, Oid refRelOid, Oid constraintOid,
                                          Oid indexOid, Oid funcoid, Oid parentTriggerOid,
                                          Node *whenClause, bool isInternal, bool in_partition,
                                          char trigger_fires_when)
{
    ......
values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);
    ......
复制

pg12.14,src/backend/commands/trigger.c

CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
                                          Oid relOid, Oid refRelOid, Oid constraintOid,
                                          Oid indexOid, Oid funcoid, Oid parentTriggerOid,
                                          Node *whenClause, bool isInternal, bool in_partition,
                                          char trigger_fires_when)
{
    ......
values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
    ......
复制

修改源码重新验证

尝试修改pg12.14源码为“values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal)“，并重新编译后再次验证，发现pg_trigger中分区触发器的tgisinternal值为false,并且可以禁用成功。

注意：此修改只为验证该现象，不保证是否会产生其他不可估计的影响。

[postgres@yj3ezdsup8s5jqx3 commands]$ diff trigger.c trigger.c.old
864c864
<       values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);
---
>       values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
[postgres@yj3ezdsup8s5jqx3 commands]$ pwd
/app/postgresql-12.14/src/backend/commands
复制

postgres=> \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)
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=> select * from pg_trigger ;
  oid  | tgrelid |  tgname  | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnar
gs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+----------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+------
---+--------+--------+--------+------------+------------
 16410 |   16397 | test_ptt |  16409 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
 16411 |   16400 | test_ptt |  16409 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
 16412 |   16403 | test_ptt |  16409 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
 16413 |   16406 | test_ptt |  16409 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |
 0 |        | \x     |        |            |
(4 rows)

postgres=> alter table test_p_t disable trigger test_ptt ;
ALTER TABLE
postgres=> select version();
                                   version
------------------------------------------------------------------------------
 PostgreSQL 12.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1, 64-bit
(1 row)

postgres=> \c
psql (15.1, server 12.14)
You are now connected to database "postgres" as user "yhru".
复制