Postgres-XL you can only see databases with CREATE privilege

3 minute read

背景

Postgres-XL改写了SQL parser, 并新增了storm_catalog这个catalog.

目的可能是提高安全性, 例如pg_database这个表, 在storm_catalog中创建一个同名视图, 正常情况下会先访问pg_catalog.pg_database, 但是因为改了parser代码, 所以直接访问的是storm_catalog.pg_database.

这个视图的定义说明了这个问题 :

postgres=# \dn *.*  
        List of schemas  
        Name        |  Owner     
--------------------+----------  
 information_schema | postgres  
 pg_catalog         | postgres  
 pg_temp_1          | postgres  
 pg_toast           | postgres  
 pg_toast_temp_1    | postgres  
 public             | postgres  
 storm_catalog      | postgres  
(7 rows)  
  
postgres=# \d pg_database  
   View "storm_catalog.pg_database"  
    Column     |   Type    | Modifiers   
---------------+-----------+-----------  
 tableoid      | oid       |   
 oid           | oid       |   
 datname       | name      |   
 datdba        | oid       |   
 encoding      | integer   |   
 datcollate    | name      |   
 datctype      | name      |   
 datistemplate | boolean   |   
 datallowconn  | boolean   |   
 datconnlimit  | integer   |   
 datlastsysoid | oid       |   
 datfrozenxid  | xid       |   
 dattablespace | oid       |   
 datacl        | aclitem[] |   
  
postgres=# \d+ pg_database  
                View "storm_catalog.pg_database"  
    Column     |   Type    | Modifiers | Storage  | Description   
---------------+-----------+-----------+----------+-------------  
 tableoid      | oid       |           | plain    |   
 oid           | oid       |           | plain    |   
 datname       | name      |           | plain    |   
 datdba        | oid       |           | plain    |   
 encoding      | integer   |           | plain    |   
 datcollate    | name      |           | plain    |   
 datctype      | name      |           | plain    |   
 datistemplate | boolean   |           | plain    |   
 datallowconn  | boolean   |           | plain    |   
 datconnlimit  | integer   |           | plain    |   
 datlastsysoid | oid       |           | plain    |   
 datfrozenxid  | xid       |           | plain    |   
 dattablespace | oid       |           | plain    |   
 datacl        | aclitem[] |           | extended |   
View definition:  
 SELECT pg_database.tableoid, pg_database.oid, pg_database.datname,   
    pg_database.datdba, pg_database.encoding, pg_database.datcollate,   
    pg_database.datctype, pg_database.datistemplate, pg_database.datallowconn,   
    pg_database.datconnlimit, pg_database.datlastsysoid,   
    pg_database.datfrozenxid, pg_database.dattablespace, pg_database.datacl  
   FROM pg_catalog.pg_database  
  WHERE pg_database.datallowconn AND (has_database_privilege(pg_database.datname::text, 'CREATE'::text) OR split_part("current_user"()::text, '@'::text, 2) = pg_database.datname::text);  

这个视图只允许你查看拥有CREATE权限的数据库, 所以一个普通用户可能只能看到部分数据库.

postgres=# \c postgres digoal  
You are now connected to database "postgres" as user "digoal".  
postgres=> \l  
                       List of databases  
 Name | Owner  | Encoding | Collate | Ctype | Access privileges   
------+--------+----------+---------+-------+-------------------  
 test | digoal | UTF8     | C       | C     | =Tc/digoal       +  
      |        |          |         |       | digoal=CTc/digoal  
(1 row)  

连接到超级用户, 可以查看到全部的数据库.

postgres=> \c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=# \l  
                             List of databases  
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges     
-----------+----------+----------+---------+-------+-----------------------  
 postgres  | postgres | UTF8     | C       | C     | =Tc/postgres         +  
           |          |          |         |       | postgres=CTc/postgres  
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +  
           |          |          |         |       | postgres=CTc/postgres  
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +  
           |          |          |         |       | postgres=CTc/postgres  
 test      | digoal   | UTF8     | C       | C     | =Tc/digoal           +  
           |          |          |         |       | digoal=CTc/digoal  
(4 rows)  

不仅如此, Postgres-XL还改了pg_catalog.pg_database_size函数的权限, 普通用户没有执行权限.

而且会先执行storm_catalog.pg_databas_size() , 这个函数返回的是当前连接的数据库的SIZE, 当前没有连接的数据库显示SIZE=0.

具体看它的代码.

postgres=> \df+ pg_database_size  
List of functions  
-[ RECORD 1 ]-------+--------------------------------------------  
Schema              | storm_catalog  
Name                | pg_database_size  
Result data type    | bigint  
Argument data types | name  
Type                | normal  
Volatility          | volatile  
Owner               | postgres  
Language            | plpgsql  
Source code         |   
                    | BEGIN  
                    |   IF $1 = current_database() THEN  
                    |     return pg_catalog.pg_database_size($1);  
                    |   END IF;  
                    |   
                    |   return 0;  
                    | END  
                    |   
Description         |   
-[ RECORD 2 ]-------+--------------------------------------------  
Schema              | storm_catalog  
Name                | pg_database_size  
Result data type    | bigint  
Argument data types | oid  
Type                | normal  
Volatility          | volatile  
Owner               | postgres  
Language            | plpgsql  
Source code         |   
                    | DECLARE  
                    |   is_current_db boolean;  
                    | BEGIN  
                    |   SELECT $1 = oid  
                    |     INTO is_current_db  
                    |     FROM pg_catalog.pg_database  
                    |    WHERE datname = current_database();  
                    |   
                    |   IF is_current_db THEN  
                    |     return pg_catalog.pg_database_size($1);  
                    |   END IF;  
                    |   
                    |   return 0;  
                    | END  
                    |   
Description         |   

要查看真实的SIZE, 建议使用超级用户查询pg_catalog.pg_database_size(oid)或者(name).

也可以将权限赋予给普通用户.

postgres=# \c postgres postgres  
grant execute on funciton pg_catalog.pg_database_size(oid) to digoal;  
grant execute on funciton pg_catalog.pg_database_size(name) to digoal;  

而且这个权限需要每个数据库赋予.

postgres=# \c postgres digoal  
You are now connected to database "postgres" as user "digoal".  
postgres=> select * from pg_catalog.pg_database_size('postgres');  
 pg_database_size   
------------------  
         61245920  
(1 row)  
  
postgres=> select * from pg_catalog.pg_database_size('test');  
 pg_database_size   
------------------  
                0  
(1 row)  
postgres=> \c test digoal  
You are now connected to database "test" as user "digoal".  
test=> select * from pg_catalog.pg_database_size('test');  
ERROR:  permission denied for function pg_database_size  
CONTEXT:  PL/pgSQL function pg_database_size(name) line 4 at RETURN  

test库又需要重新赋予权限才行,

test=> \c test postgres  
You are now connected to database "test" as user "postgres".  
test=# grant execute on function pg_catalog.pg_database_size(oid) to digoal;  
GRANT  
test=# grant execute on function pg_catalog.pg_database_size(name) to digoal;  
GRANT  
test=# \c test digoal  
You are now connected to database "test" as user "digoal".  
test=> select * from pg_catalog.pg_database_size('test');  
 pg_database_size   
------------------  
         59181536  
(1 row)  

如果普通用户经常要查询这个的话, 建议在模板库(template0, template1)赋予权限, 以后新建的库就不需要赋予了.

参考

1. src/backend/parser/parse_relation.c

2. src/backend/parser/analyze.c

Flag Counter

digoal’s 大量PostgreSQL文章入口