Postgres-XL you can only see databases with CREATE privilege
背景
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