postgresql用户模式权限介绍

一.USER用户管理

1.查看用户

pg中的role,user,group基本是一样的,只是默认创建的role,group没有登录数据库的权限.用户分为普通用户和超级用户

1.使用\du查看数据库中的用户,其中role name是用户名,第二列是用户的属性,第三列表示用户具有哪些成员,例如将suq赋予给brent

 

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 brent     |                                                            | {suq}

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 suq       | 1 connection                                               | {}

 zdry      | Superuser                                                 +| {}

           | Password valid until infinity                              | 

2.创建用户

1.查看创建用户的语法

 

test=# \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

2.创建一个普通用户

 

postgres=# create user test ENCRYPTED password 'test';

CREATE ROLE

3.为创建一个超级用户

 

test=# create user dsg superuser;

CREATE ROLE

4.创建一个普通用户,并且赋予相关权限

 

test=# create user dsg createdb createrole inherit password 'dsg';

CREATE ROLE

3.修改用户

1.查看修改用户语句

 

test=# \h alter user

Command:     ALTER USER

Description: change a database role

Syntax:

ALTER USER role_specification [ 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'

ALTER USER name RENAME TO new_name

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name

  | CURRENT_USER

  | SESSION_USER

2.修改用户为超级用户

 

postgres=# alter user test superuser;

ALTER ROLE

3.将超级用户修改为普通用户

 

postgres=# alter user test nosuperuser;

ALTER ROLE

4.修改用户密码

 

test=# alter user dsg password 'test';

ALTER ROLE

5.修改用户名

 

test=# alter user dsg rename to dds;

NOTICE:  MD5 password cleared because of role rename

ALTER ROLE

6.锁定/解锁用户,不允许/允许其登录

 

test=# alter user test nologin;

ALTER ROLE

test=# alter user test login;

ALTER ROLE

7.设置用户的连接数,其中0表示不允许登录,-1表示无限制

 

test=# alter user test connection limit 10;

ALTER ROLE

4.删除用户

1.直接删除用户

 

test=# drop user dds;

DROP ROLE

如果用户在数据库中有相关对象,不能直接删除,需要将相关对象所属修改到其它用户中

 

test=# drop user dsg;

ERROR:  role "dsg" cannot be dropped because some objects depend on it

DETAIL:  owner of table zzz.kkk

privileges for schema zzz

将dsg的所属用户修改为test:

 

test=# reassign owned by dsg to test;

REASSIGN OWNED

还需要把权限进行收回,再进行删除:

 

test=# revoke all on schema zzz from dsg;

REVOKE

test=# drop user dsg;

DROP ROLE

二.schema模式管理

首先介绍一下postgresql中的schema,postgresql中的schema和其它关系型数据库中的schema含义是一致的,在oracle中叫schema或者用户,只是oracle中schema和用户是始终一一对应.

在mysql中database和schema是一一对应的.postgresql中user和schema是可以不一致的,相对比其它数据库复杂一点.

在创建schema的时候,可以指定schema的所属用户, 默认的只有所属用户和超级用户才能在此schema进行对象操作,否则就需要授权.

1.使用\dn查看数据库的schema

 

test=# \dn

  List of schemas

  Name  |  Owner   

--------+----------

 brent  | brent

 public | postgres

 suq    | suq

 zzz    | test

(4 rows)

2.创建schema

1.查看创建schema语法

 

test=# \h create schema

Command:     CREATE SCHEMA

Description: define a new schema

Syntax:

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]

CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]

CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name

  | CURRENT_USER

  | SESSION_USER

2.创建一个schema,并且设置所属用户为test:

 

test=# create schema zzz authorization test;

CREATE SCHEMA

3.删除schema

1.删除schema,如果schema中存在对象,则需要使用cascade选项:

 

test=# drop schema zzz;

ERROR:  cannot drop schema zzz because other objects depend on it

DETAIL:  table zzz.test depends on schema zzz

HINT:  Use DROP ... CASCADE to drop the dependent objects too.

test=# drop schema zzz cascade;

NOTICE:  drop cascades to table zzz.test

DROP SCHEMA

三.权限管理

postgresql中的权限可以大概分为以下几种:

SELECT：该权限用来查询表或是表上的某些列，或是视图，序列。

INSERT：该权限允许对表或是视图进行插入数据操作，也可以使用COPY FROM进行数据的插入。

UPDATE：该权限允许对表或是或是表上特定的列或是视图进行更新操作。

DELETE：该权限允许对表或是视图进行删除数据的操作。

TRUNCATE：允许对表进行清空操作。

REFERENCES：允许给参照列和被参照列上创建外键约束。

TRIGGER：允许在表上创建触发器。

CREATE：对于数据库，允许在数据库上创建Schema；对于Schema，允许对Schema上创建数据库对象；对于表空间，允许把表或是索引指定到对应的表空间上。

CONNECT：允许用户连接到指定的数据库上。

TEMPORARY或是TEMP：允许在指定数据库的时候创建临时表。

EXECUTE：允许执行某个函数。

USAGE：对于程序语言来说，允许使用指定的程序语言创建函数；对于Schema来说，允许查找该Schema下的对象；对于序列来说，允许使用currval和nextval函数；对于外部封装器来说，允许使用外部封装器来创建外部服务器；对于外部服务器来说，允许创建外部表。

ALL PRIVILEGES：表示一次性给予可以授予的权限。

1.schema权限管理

首先,如果某个用户需要访问某张表, 那么用户首先需要有访问该表所在schema的权限.默认只有schema的所属可以直接操作该schema,其它用户需要授权(public schma除外)

1.将schema的权限赋予给指定用户

例如,将创建对象权限赋予给brent用户:

 

test=# grant create on schema zzz to brent;

GRANT

例如,将schema中usage权限赋予给brent用户:

 

test=> grant usage on schema zzz to brent;

GRANT

例如,将schema中all权限赋予给brent用户,all表示一次性给予可以授予的所有权限

 

test=> grant all on schema zzz to brent;

GRANT

2.表权限管理

默认的,如果没有特别的授权,普通用户只能访问表所属为自己的表.超级用户可以访问任何表.如果要访问非自己的表,那么就需要对表进行授权.

当我们以brent用户想访问zzz模式下所属用户为test的abc表的时候就会报错:

 

test=> select user;

 user  

-------

 brent

 test=> select * from zzz.abc;

ERROR:  permission denied for relation abc                

1.grant,将表的查询和插入权限赋予给brent:

 

test=# grant select,insert on zzz.abc to brent;

GRANT

那么就可以进行查询了:

 

(1 row)

test=> \c test brent

You are now connected to database "test" as user "brent".

test=> select * from zzz.abc;

 id 

----

(0 rows)

2.revoke,将表的查询权限收回:

 

test=# set search_path=zzz;

SET

test=# \dt

       List of relations

 Schema | Name | Type  | Owner 

--------+------+-------+-------

 zzz    | abc  | table | test

 zzz    | kkk  | table | test

(2 rows)

test=# revoke select on zzz.abc from brent;

REVOKE

3.角色管理

我们除了可以将表的权限赋予给用户,我们还可以将角色赋予给用户,那么用户就会拥有赋予角色的相关权限:

 

test=# grant test to brent;

GRANT ROLE

test=# revoke test from brent;

REVOKE ROLE

4.查询表权限角色列表

使用\dp或者\z命令,可以查看表对象上已经分配的权限列表,如下:

 

test=# \dp abc

                            Access privileges

 Schema | Name | Type  | Access privileges | Column privileges | Policies 

--------+------+-------+-------------------+-------------------+----------

 zzz    | abc  | table | test=arwdDxt/test+|                   | 

        |      |       | brent=a/test     +|                   | 

        |      |       | uuu=arwdDxt/test  |                   | 

(1 row)

详细的权限说明如下:

            r -- SELECT ("读")

            w -- UPDATE ("写")

            a -- INSERT ("追加")

            d -- DELETE

            D -- TRUNCATE

            x -- REFERENCES

            t -- TRIGGER

            X -- EXECUTE

            U -- USAGE

            C -- CREATE

            c -- CONNECT

            T -- TEMPORARY

      arwdDxt -- ALL PRIVILEGES (对于表，对其他对象会变化)

            * -- 用于前述特权的授权选项

        /yyyy -- 授予该特权的角色

使用\du可以查看角色之间的成员关系:

 

test=# \du brent

            List of roles

 Role name | Attributes | Member of  

-----------+------------+------------

 brent     |            | {uuu,test}

其中uuu,test是brent的成员,也就是说uuu,test角色被赋予给了brent用户.\du类似与查看oracle中dba_role_privs

当我们决定收回某个表给予某个用户的权限的时候,除了需要收回表的权限,还需要检查用户的角色信息,保证用户的角色也没有相关的权限.

还可以通过查询 information_schema. role_table_grants 来了解某个用户具有的权限,类似于oralce中dba_tab[sys]_privs

test=# select * from information_schema.role_table_grants where grantee='brent';

 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 

---------+---------+---------------+--------------+------------+----------------+--------------+----------------

 brent   | brent   | test          | brent        | x          | INSERT         | YES          | NO

 brent   | brent   | test          | brent        | x          | SELECT         | YES          | YES

 brent   | brent   | test          | brent        | x          | UPDATE         | YES          | NO

 brent   | brent   | test          | brent        | x          | DELETE         | YES          | NO

 brent   | brent   | test          | brent        | x          | TRUNCATE       | YES          | NO

 brent   | brent   | test          | brent        | x          | REFERENCES     | YES          | NO

 brent   | brent   | test          | brent        | x          | TRIGGER        | YES          | NO

 brent   | brent   | test          | brent        | tt         | INSERT         | YES          | NO

 brent   | brent   | test          | brent        | tt         | SELECT         | YES          | YES

 brent   | brent   | test          | brent        | tt         | UPDATE         | YES          | NO

 brent   | brent   | test          | brent        | tt         | DELETE         | YES          | NO

 brent   | brent   | test          | brent        | tt         | TRUNCATE       | YES          | NO

 brent   | brent   | test          | brent        | tt         | REFERENCES     | YES          | NO

 brent   | brent   | test          | brent        | tt         | TRIGGER        | YES          | NO

 test    | brent   | test          | zzz          | abc        | INSERT         | YES          | NO

(15 rows)