PostgreSQL数据库用户权限管理

IT小Chen 2023-01-28

869

启动数据库

[postgres@cjc-db-01 ~]$ pg_ctl -D pg/data -l pg/log/pg.log start
waiting for server to start.... done
server started
复制

登录数据库

[postgres@cjc-db-01 ~]$ psql
复制

查看pg_roles表字段

postgres=# \d pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          | 
 rolsuper       | boolean                  |           |          | 
 rolinherit     | boolean                  |           |          | 
 rolcreaterole  | boolean                  |           |          | 
 rolcreatedb    | boolean                  |           |          | 
 rolcanlogin    | boolean                  |           |          | 
 rolreplication | boolean                  |           |          | 
 rolconnlimit   | integer                  |           |          | 
 rolpassword    | text                     |           |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
 rolbypassrls   | boolean                  |           |          | 
 rolconfig      | text[]                   |           |          | 
 oid            | oid                      |           |          |
复制

或

postgres=#
SELECT
   table_name,
   column_name,
   data_type,
   column_default
FROM
   information_schema.columns
WHERE
   table_name = 'pg_roles';
 table_name |  column_name   |        data_type         | column_default 
------------+----------------+--------------------------+----------------
 pg_roles   | rolname        | name                     | 
 pg_roles   | rolsuper       | boolean                  | 
 pg_roles   | rolinherit     | boolean                  | 
 pg_roles   | rolcreaterole  | boolean                  | 
 pg_roles   | rolcreatedb    | boolean                  | 
 pg_roles   | rolcanlogin    | boolean                  | 
 pg_roles   | rolreplication | boolean                  | 
 pg_roles   | rolconnlimit   | integer                  | 
 pg_roles   | rolpassword    | text                     | 
 pg_roles   | rolvaliduntil  | timestamp with time zone | 
 pg_roles   | rolbypassrls   | boolean                  | 
 pg_roles   | rolconfig      | ARRAY                    | 
 pg_roles   | oid            | oid                      | 
复制

查看角色信息

postgres=# select rolname,rolsuper,rolcreatedb from pg_roles;
       rolname        | rolsuper | rolcreatedb 
----------------------+----------+-------------
 postgres             | t        | t
 pg_monitor           | f        | f
 pg_read_all_settings | f        | f
 pg_read_all_stats    | f        | f
 pg_stat_scan_tables  | f        | f
 pg_signal_backend    | f        | f
(6 rows)
复制

在PostgreSQL数据库中，角色和用户没有区别，一个角色就是一用户，这块和Oracle数据库有一定差异。

相关描述如下：

CREATE USER is now an alias for CREATE ROLE. 
The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default,
whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.
复制

查看create user帮助信息

postgres=# \h create user;
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]


where option can be:


      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
复制

查看create role帮助信息，和create user完全一样。

postgres=# \h create role;
Command:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]


where option can be:


      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
复制

参数说明：

1.SUPERUSER |NOSUPERUSER
创建出来的用户是否为超级用户
2.CREATEDB |NOCREATEDB
指定创建出来的用户是否有创建数据库的权限
3.CREATEROLE |NOCREATEROLE
指定创建出来的用户是否有创建其他角色权限
4.CREATEUSER |NOCREATEUSER
指定创建出来的用户是否有创建其它用户的权限
5.INHERIT | NOINHERIT
创建的用户拥有某个或几个角色的权限
6.LOGIN | NOLOGIN
创建出来的用户是否有连接数据库的权限
7.CONNECTION LIMIT connlimit
户可以使用的并发连接的数量，默认为 "-1"，表示没有限制
8.[ENCRYPTED | UNENCRYPTED] PASSWORD 'password'
存储的用户口令是否加密
9.VALID UNTIL 'timestamp'
密码失效时间，不指定的话永久有效
10.IN ROLE role_name [...]
指定用户成为哪些角色的成员
11.IN GROUP role_name [...]
等同于IN ROLE rome_name，不过已过时
12.ROLE role_name [...]role_name
将成为这个新建的角色的成员
13.ADMIN role_name [...]role_name
将有这个新建角色 WITH ADMIN OPTION权限
14.USER role_name
与ROLE相同，不过已过时
15.SYSID uid
用于SQL兼容，实际没什么用
复制

创建用户

create user chen LOGIN;
复制

创建角色

create role cjc SUPERUSER PASSWORD '1';
复制

查看用户已经创建

postgres=# select rolname,rolsuper,rolcreatedb from pg_roles;
       rolname        | rolsuper | rolcreatedb 
----------------------+----------+-------------
 postgres             | t        | t
 pg_monitor           | f        | f
 pg_read_all_settings | f        | f
 pg_read_all_stats    | f        | f
 pg_stat_scan_tables  | f        | f
 pg_signal_backend    | f        | f
 chen                 | f        | f
 cjc                  | t        | f
(8 rows)
复制

指定用户登录

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U chen -W postgres
Password for user chen: 
psql.bin (10.23)
Type "help" for help.
复制

postgres=> select current_user;
 current_user 
--------------
 chen
(1 row)
复制

注意：创建用户方式创建出来的用户默认有 LOGIN 权限，而创建角色创建出来的用户没有 LOGIN 权限。

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W postgres
Password for user cjc: 
psql.bin: FATAL:  role "cjc" is not permitted to log in
复制

授予登录权限

postgres=# grant connect on database postgres to cjc;
postgres=# \q
复制

授权语句不对，还是不能登录

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W postgres
Password for user cjc: 
psql.bin: FATAL:  role "cjc" is not permitted to log in
复制

修改角色

postgres=# alter role cjc login superuser;
复制

可以登录了

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W postgres
Password for user cjc: 
psql.bin (10.23)
Type "help" for help.
复制

postgres=# select current_user;
 current_user 
--------------
 cjc
(1 row)
复制

为用户指定表空间

查看表空间

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)
复制

创建表空间

postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]
复制

注意：

1.表空间的名称不能以 'pg_' 开头，它们是系统表空间的保留名称；
2.LOCATION 参数必须指定绝对路径名，
3.指定的目录必须是一个已经存在的空目录，
4.PostgreSQL 操作系统用户（postgres）必须是该目录的拥有者，以便能够进行文件的读写。
复制

创建空目录

[postgres@cjc-db-01 data]$ pwd
/pg/data
[postgres@cjc-db-01 data]$ mkdir cjctbs
复制

创建表空间，有WARNING，新增的表空间不能在数据路径里面

postgres=# CREATE TABLESPACE cjctbs OWNER cjc LOCATION '/pg/data/cjctbs';
WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE
复制

自动生成的文件

[postgres@cjc-db-01 PG_10_201707211]$ pwd
/pg/data/cjctbs/PG_10_201707211
[postgres@cjc-db-01 PG_10_201707211]$ ls
复制

删除表空间

postgres=# drop tablespace cjctbs;
DROP TABLESPACE
复制

创建表空间,重新指定路径，没有WARNING

[postgres@cjc-db-01 pg]$ mkdir pg/tbs/cjctbs -p
postgres=# CREATE TABLESPACE cjctbs OWNER cjc LOCATION '/pg/tbs/cjctbs';
CREATE TABLESPACE
复制

自动生成目录

[postgres@cjc-db-01 PG_10_201707211]$ pwd
/pg/tbs/cjctbs/PG_10_201707211
复制

创建数据库

指定用户和表空间

postgres=# create database cjcdb owner cjc tablespace cjctbs;
CREATE DATABASE
复制

查看新生成的文件

[postgres@cjc-db-01 16396]$ pwd
/pg/tbs/cjctbs/PG_10_201707211/16396
[postgres@cjc-db-01 16396]$ ls
112        13696_vm   13713      1417_vm   2601      2606_vm   2612_fsm  2619      2661  2681  2702      2834      2996      3350_vm   3456_vm   3576      3602_vm   3997     826_vm
113        13698      13715      1418      2601_fsm  2607      2612_vm   2619_fsm  2662  2682  2703      2834_vm   3079      3351      3466      3576_vm   3603      5002     827
1247       13700      13716      1418_vm   2601_vm   2607_fsm  2613      2619_vm   2663  2683  2704      2835      3079_fsm  3379      3466_vm   3596      3603_fsm  548      828
1247_fsm   13701      13716_fsm  174       2602      2607_vm   2613_vm   2620      2664  2684  2753      2836      3079_vm   3380      3467      3596_vm   3603_vm   549      pg_filenode.map
1247_vm    13701_fsm  13716_vm   175       2602_fsm  2608      2615      2620_vm   2665  2685  2753_fsm  2836_vm   3080      3381      3468      3597      3604      6102     PG_VERSION
1249       13701_vm   13718      2187      2602_vm   2608_fsm  2615_fsm  2650      2666  2686  2753_vm   2837      3081      3381_vm   3501      3598      3605      6102_vm
1249_fsm   13703      13720      2224      2603      2608_vm   2615_vm   2651      2667  2687  2754      2838      3085      3394      3501_vm   3598_vm   3606      6104
1249_vm    13705      13721      2224_vm   2603_fsm  2609      2616      2652      2668  2688  2755      2838_fsm  3118      3394_fsm  3502      3599      3607      6104_vm
1255       13706      13721_fsm  2328      2603_vm   2609_fsm  2616_fsm  2653      2669  2689  2756      2838_vm   3118_vm   3394_vm   3503      3600      3608      6106
1255_fsm   13706_fsm  13721_vm   2328_vm   2604      2609_vm   2616_vm   2654      2670  2690  2757      2839      3119      3395      3534      3600_fsm  3609      6106_vm
1255_vm    13706_vm   13723      2336      2604_vm   2610      2617      2655      2673  2691  2830      2840      3164      3439      3541      3600_vm   3712      6110
1259       13708      13725      2336_vm   2605      2610_fsm  2617_fsm  2656      2674  2692  2830_vm   2840_fsm  3256      3439_vm   3541_fsm  3601      3764      6111
1259_fsm   13710      13726      2337      2605_fsm  2610_vm   2617_vm   2657      2675  2693  2831      2840_vm   3256_vm   3440      3541_vm   3601_fsm  3764_fsm  6112
1259_vm    13711      13728      2600      2605_vm   2611      2618      2658      2678  2696  2832      2841      3257      3455      3542      3601_vm   3764_vm   6113
13696      13711_fsm  13730      2600_fsm  2606      2611_vm   2618_fsm  2659      2679  2699  2832_vm   2995      3258      3456      3574      3602      3766      6117
13696_fsm  13711_vm   1417       2600_vm   2606_fsm  2612      2618_vm   2660      2680  2701  2833      2995_vm   3350      3456_fsm  3575      3602_fsm  3767      826
复制

查询数据库

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 cjcdb     | cjc      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7745 kB | cjctbs     | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +| 7879 kB | pg_default | default administrative connection database
           |          |          |             |             | postgres=CTc/postgres+|         |            | 
           |          |          |             |             | cjc=c/postgres        |         |            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7745 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7745 kB | pg_default | default template for new databases
复制

查询数据库物理存储位置

cjcdb=# select oid,datname from pg_database where datname = 'cjcdb';
  oid  | datname 
-------+---------
 16396 | cjcdb
(1 row)
复制

根据oid进行查找

[postgres@cjc-db-01 16396]$ pwd
/pg/tbs/cjctbs/PG_10_201707211/16396
[postgres@cjc-db-01 16396]$ ls |wc -l
296
复制

查看指定数据库的大小

postgres=# select pg_database_size('cjcdb');
 pg_database_size 
------------------
          8076423
(1 row)
复制

查看指定数据库的大小

postgres=# select pg_size_pretty(pg_database_size('cjcdb'));
 pg_size_pretty 
----------------
 7887 kB
(1 row)
复制

查看数据库的大小

postgres=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; 
  datname  |  size   
-----------+---------
 template1 | 7930371
 template0 | 7930371
 postgres  | 8068231
 cjcdb     | 8076423
(4 rows)
复制

查询数据库默认表空间

cjcdb=# select datname,dattablespace from pg_database where datname='cjcdb'; 
 datname | dattablespace 
---------+---------------
 cjcdb   |         16395
(1 row)
复制

登录数据库

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb
复制

建表

cjcdb=# create table t1(id int,name char(10));
复制

查询表物理存储位置

cjcdb=# select oid,relfilenode from pg_class where relname = 't1';
  oid  | relfilenode 
-------+-------------
 16397 |       16397
(1 row)
复制

根据relfilenode进行查找

[postgres@cjc-db-01 16396]$ pwd
/pg/tbs/cjctbs/PG_10_201707211/16396
[postgres@cjc-db-01 16396]$ ls -lrth 16397*
-rw------- 1 postgres postgres 8.0K Jan 17 17:23 16397
复制

查询表所在表空间

新增表

create table t2(id int,name char(10));
create table t3(id int,name char(10)) tablespace cjctbs;
复制

没表空间信息？

cjcdb=# \d+ t3
                                        Table "public.t3"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer       |           |          |         | plain    |              | 
 name   | character(10) |           |          |         | extended |              | 
---
cjcdb=# select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='cjc';
 tableowner | schemaname | tablename | tablespace 
------------+------------+-----------+------------
 cjc        | public     | t1        | 
 cjc        | public     | t5        | 
 cjc        | public     | t6        | 
(3 rows)
---
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname  
 from pg_class a, pg_tablespace tb  
where a.reltablespace = tb.oid  
 and a.relkind in ('r', 'i')  
order by a.relpages desc;
---
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
from pg_class a, pg_tablespace tb  
where a.relkind in ('r', 'i')  
and a.reltablespace=tb.oid  
and tb.spcname='cjctbs'  
order by a.relpages desc;
复制

修改表所在表空间

alter table t1 set tablespace cjctbs;
复制

查询表结构

cjcdb=# \d t1
                    Table "public.t1"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 id     | integer       |           |          | 
 name   | character(10) |           |          | 
复制

插入数据

cjcdb=# insert into t1 values(1,'a'),(2,'aaa');
INSERT 0 2
cjcdb=# select * from t1;
 id |    name    
----+------------
  1 | a         
  2 | aaa       
(2 rows)
复制

查看当前数据库下所有表

postgres=# \c cjcdb
You are now connected to database "cjcdb" as user "postgres".
cjcdb=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t1   | table | cjc
(1 row)
复制

查看表大小

cjcdb=# select pg_relation_size('t1'); 
 pg_relation_size 
------------------
             8192
(1 row)
复制

查看表大小

cjcdb=# select pg_size_pretty(pg_relation_size('t1')); 
 pg_size_pretty 
----------------
 8192 bytes
(1 row)
复制

创建索引

cjcdb=# create index i_t1_id on t1(id);
复制

查看表的总大小，包括索引大小

cjcdb=# select pg_size_pretty(pg_total_relation_size('t1'));
 pg_size_pretty 
----------------
 24 kB
(1 row)
复制

查看当前数据库下所有索引

cjcdb=# \di
            List of relations
 Schema |  Name   | Type  | Owner | Table 
--------+---------+-------+-------+-------
 public | i_t1_id | index | cjc   | t1
(1 row)
复制

查看单个索引大小

cjcdb=# select pg_size_pretty(pg_relation_size('i_t1_id'));
 pg_size_pretty 
----------------
 16 kB
(1 row)
复制

查询表空间大小

cjcdb=# select pg_size_pretty(pg_tablespace_size('cjctbs'));
 pg_size_pretty 
----------------
 7911 kB
(1 row)
复制

当前chen只有登录权限

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U chen -W cjcdb
Password for user chen: 
psql.bin (10.23)
Type "help" for help.
复制

没有查询权限

cjcdb=> select * from t1;
ERROR:  permission denied for relation t1
复制

查看授权语句

postgres=# \h grant;
Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]


GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]


where role_specification can be:


    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER
复制

表级别授权

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb
cjcdb=# GRANT SELECT ON TABLE t1  TO chen;
复制

可以查询

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U chen -W cjcdb
Password for user chen: 
psql.bin (10.23)
Type "help" for help.


cjcdb=> select * from t1;
 id |    name    
----+------------
  1 | a         
  2 | aaa       
(2 rows)
复制

不能执行其他操作

cjcdb=> insert into t1 values(3,'xxx');
ERROR:  permission denied for relation t1
cjcdb=> delete from t1;
ERROR:  permission denied for relation t1
复制

回收权限

[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb
cjcdb=# revoke select on t1 from chen;
复制

库级别授权

cjcdb=# GRANT ALL ON DATABASE cjcdb to chen;
复制

查询权限

查看用户表权限

cjcdb=# select * from information_schema.table_privileges where grantee='cjc';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 cjc     | cjc     | cjcdb         | public       | t1         | INSERT         | YES          | NO
 cjc     | cjc     | cjcdb         | public       | t1         | SELECT         | YES          | YES
 cjc     | cjc     | cjcdb         | public       | t1         | UPDATE         | YES          | NO
 cjc     | cjc     | cjcdb         | public       | t1         | DELETE         | YES          | NO
 cjc     | cjc     | cjcdb         | public       | t1         | TRUNCATE       | YES          | NO
 cjc     | cjc     | cjcdb         | public       | t1         | REFERENCES     | YES          | NO
 cjc     | cjc     | cjcdb         | public       | t1         | TRIGGER        | YES          | NO
(7 rows)
复制

查看usage权限表

select * from information_schema.usage_privileges where grantee='cjc';
复制

查看存储过程函数相关权限表

select * from information_schema.routine_privileges where grantee='cjc';
复制

###chenjuchao 20230118 10:20###

postgresql 虚拟机表空间

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL数据库用户权限管理

评论

相关阅读