text: ## Postgres-XL you can only see databases with CREATE privilege  
                             
  ### 作者          
  digoal          
            
  ### 日期           
  2014-07-25         
              
  ### 标签          
  PostgreSQL , Postgres-XL , 权限管理     
                        
  ----                      
                         
  ## 背景    
  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  
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  #### [PostgreSQL 许愿链接](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216")
  您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。[开不开森](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216").  
    
    
  #### [9.9元购买3个月阿里云RDS PostgreSQL实例](https://www.aliyun.com/database/postgresqlactivity "57258f76c37864c6e6d23383d05714ea")
    
    
  #### [PostgreSQL 解决方案集合](https://yq.aliyun.com/topic/118 "40cff096e9ed7122c512b35d8561d9c8")
    
    
  #### [德哥 / digoal's github - 公益是一辈子的事.](https://github.com/digoal/blog/blob/master/README.md "22709685feb7cab07d30f30387f0a9ae")
    
    
  ![digoal's wechat](../pic/digoal_weixin.jpg "f7ad92eeba24523fd47a6e1a0e691b59")
    
  