postgresql用户模式权限介绍_postgresql usage_postgresql 用户权限

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化的资料的朋友，可以戳这里获取

2.创建一个普通用户

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

CREATE ROLE
1

3.为创建一个超级用户

test=# create user dsg superuser;
1

CREATE ROLE
1

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

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

CREATE ROLE
1

3.修改用户

1.查看修改用户语句

test=# \h alter user
1

Command:     ALTER USER
1

Description: change a database role
1

Syntax:
1

ALTER USER role\_specification [ WITH ] option [ ... ]
1

where option can be:
1

     SUPERUSER | NOSUPERUSER
1

   | CREATEDB | NOCREATEDB
1

   | CREATEROLE | NOCREATEROLE
1

   | INHERIT | NOINHERIT
1

   | LOGIN | NOLOGIN
1

   | REPLICATION | NOREPLICATION
1

   | BYPASSRLS | NOBYPASSRLS
1

   | CONNECTION LIMIT connlimit
1

   | [ ENCRYPTED ] PASSWORD 'password'
1

   | VALID UNTIL 'timestamp'
1

ALTER USER name RENAME TO new\_name
1

ALTER USER { role\_specification | ALL } [ IN DATABASE database\_name ] SET configuration\_parameter { TO | = } { value | DEFAULT }
1

ALTER USER { role\_specification | ALL } [ IN DATABASE database\_name ] SET configuration\_parameter FROM CURRENT
1

ALTER USER { role\_specification | ALL } [ IN DATABASE database\_name ] RESET configuration\_parameter
1

ALTER USER { role\_specification | ALL } [ IN DATABASE database\_name ] RESET ALL
1

where role\_specification can be:
1

   role\_name
1

 | CURRENT\_USER
1

 | SESSION\_USER
1

2.修改用户为超级用户

postgres=# alter user test superuser;
1

ALTER ROLE
1

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

postgres=# alter user test nosuperuser;
1

ALTER ROLE
1

4.修改用户密码

test=# alter user dsg password 'test';
1

ALTER ROLE
1

5.修改用户名

test=# alter user dsg rename to dds;
1

NOTICE: MD5 password cleared because of role rename
1

ALTER ROLE
1

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

test=# alter user test nologin;
1

ALTER ROLE
1

test=# alter user test login;
1

ALTER ROLE
1

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

test=# alter user test connection limit 10;
1

ALTER ROLE
1

4.删除用户

1.直接删除用户

test=# drop user dds;
1

DROP ROLE
1

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

test=# drop user dsg;
1

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

DETAIL: owner of table zzz.kkk
1

privileges for schema zzz
1

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

test=# reassign owned by dsg to test;
1

REASSIGN OWNED
1

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

test=# revoke all on schema zzz from dsg;
1

REVOKE
1

test=# drop user dsg;
1

DROP ROLE
1

二.schema模式管理

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

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

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

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

test=# \dn
1

 List of schemas
1

 Name | Owner   
1

--------+----------
1

 brent | brent
1

 public | postgres
1

 suq   | suq
1

 zzz   | test
1

(4 rows)
1

2.创建schema

1.查看创建schema语法

test=# \h create schema
1

Command:     CREATE SCHEMA
1

Description: define a new schema
1

Syntax:
1

CREATE SCHEMA schema\_name [ AUTHORIZATION role\_specification ] [ schema\_element [ ... ] ]
1

CREATE SCHEMA AUTHORIZATION role\_specification [ schema\_element [ ... ] ]
1

CREATE SCHEMA IF NOT EXISTS schema\_name [ AUTHORIZATION role\_specification ]
1

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role\_specification
1

where role\_specification can be:
1

   user\_name
1

 | CURRENT\_USER
1

 | SESSION\_USER
1

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

test=# create schema zzz authorization test;
1

CREATE SCHEMA
1

3.删除schema

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

test=# drop schema zzz;
1

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

DETAIL:  table zzz.test depends on schema zzz
1

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

test=# drop schema zzz cascade;
1

NOTICE:  drop cascades to table zzz.test
1

DROP SCHEMA
1

三.权限管理

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;
1

GRANT
1

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

test=> grant usage on schema zzz to brent;
1

GRANT
1

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

test=> grant all on schema zzz to brent;
1

GRANT
1

2.表权限管理

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

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

test=> select user;
1

 user  
1

-------
1

 brent
1

 test=> select \* from zzz.abc;
1

ERROR: permission denied for relation abc                
1

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

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

GRANT
1

那么就可以进行查询了:

(1 row)
1

test=> \c test brent
1

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

test=> select \* from zzz.abc;
1

 id 
1

----
1

(0 rows)
1

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

test=# set search\_path=zzz;
1

SET
1

test=# \dt
1

       List of relations
1

 Schema | Name | Type | Owner 
1

--------+------+-------+-------
1

 zzz   | abc | table | test
1

 zzz   | kkk | table | test
1

(2 rows)
1

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

REVOKE
1

3.角色管理

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

test=# grant test to brent;
1

GRANT ROLE
1

test=# revoke test from brent;
1

REVOKE ROLE
1

4.查询表权限角色列表

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

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化的资料的朋友，可以戳这里获取

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














   
 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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

1
2
3
4

GRANT

 那么就可以进行查询了: 














   
 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

(1 row)

1
2
3
4
5
6
7

test=> \c test brent

1
2
3
4

You are now connected to database “test” as user “brent”.

1
2
3
4

test=> select * from zzz.abc;

1
2
3
4

id

1
2
3
4

---

1
2
3
4

(0 rows)

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














   
 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

test=# set search_path=zzz;

1
2
3
4

SET

1
2
3
4

test=# \dt

1
2
3
4

List of relations

1
2
3
4

Schema | Name | Type | Owner

1
2
3
4

--------±-----±------±------

1
2
3
4

zzz   | abc | table | test

1
2
3
4

zzz   | kkk | table | test

1
2
3
4

(2 rows)

1
2
3
4
5
6
7

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

1
2
3
4

REVOKE

  


### 3.角色管理



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














   
 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

test=# grant test to brent;

1
2
3
4

GRANT ROLE

1
2
3
4

test=# revoke test from brent;

1
2
3
4

REVOKE ROLE

  


### 4.查询表权限角色列表



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














   
 





[外链图片转存中...(img-jUgsC4QN-1715816571454)]
[外链图片转存中...(img-jLchl5MJ-1715816571454)]
[外链图片转存中...(img-6zh1WRkj-1715816571454)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化的资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618631832)**

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59