暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / pg 权限

pg 权限

原创 苏佳乐 2022-10-09
546

# postgres 权限

刚接触pg的时候被权限搞的很晕，通过测试简单的说明一下。
## 已有表授权
--首先创建用户和schema，sjl用户

```
postgres=# create user sjl;
CREATE ROLE
test=# create schema sjl  AUTHORIZATION sjl;
CREATE SCHEMA
```

--此时用户没有schema的任何权限
``` language
test=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
sjl | sjl | | 《《《《《《《《《
ywjh | app3 | app3=UC/app3 +|
| | app=UC/app3 |
(3 rows)
```

--对用户授权，all on schema包含了usage和create权限。
``` language
test=# grant all on schema sjl to sjl;
GRANT
test=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
sjl | sjl | sjl=UC/sjl | 《《《《《《《《《
ywjh | app3 | app3=UC/app3 +|
| | app=UC/app3 |
(3 rows)
```

--切换到用户下建表，sjl用户你可以理解为业务用户，为了便于管理创建对象都要用sjl用户。

``` language
test=# \c - sjl
You are now connected to database "test" as user "sjl".
test=> create table sjl.t1(id int);
CREATE TABLE
test=> insert into t1 values(1);
INSERT 0 1
test=> \dt+ sjl.*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+-------+-------------
sjl | t1 | table | sjl | 16 kB |
(1 row)
--\dp说明 list table, view, and sequence access privileges
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | | |
(1 row)

```
--创建表
```
test=> create table sjl.t2(id int);
CREATE TABLE
test=> create table sjl.t3(id int);
CREATE TABLE
test=> create table sjl.t4(id int);
CREATE TABLE
test=> create table sjl.t5(id int);
CREATE TABLE
```


--查看表权限，schema:sjl的owner是user:sjl默认有表的所有权限，这里不显示
```
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | | |
sjl | t2 | table | | |
sjl | t3 | table | | |
sjl | t4 | table | | |
sjl | t5 | table | | |
(5 rows)
```

--你可以授权并显示
```
test=> grant all on all tables in schema sjl to sjl;
GRANT
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | sjl=arwdDxt/sjl | |
sjl | t2 | table | sjl=arwdDxt/sjl | |
sjl | t3 | table | sjl=arwdDxt/sjl | |
sjl | t4 | table | sjl=arwdDxt/sjl | |
sjl | t5 | table | sjl=arwdDxt/sjl | |
(5 rows)

```

--字母代表的权限的意思如下：
            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
            arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)


            
--此时我们切换到app用户下，你会发现app用户并不能访问sjl.t1表
```
test=> \c - app
You are now connected to database "test" as user "app".
test=> select * from sjl.t1;
ERROR: permission denied for schema sjl 《《提示没有schema权限。
```


--查看schema的权限
```
test=> \dn+ sjl
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
sjl | sjl | sjl=UC/sjl | 《《《app没有schema:sjl的权限
(1 row)
```


--授权,为了便于管理我们只给usage权限
```
\c - sjl
test=> grant USAGE on SCHEMA sjl to app;
test-> ;
GRANT
test=> \dn+ sjl
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
sjl | sjl | sjl=UC/sjl +|
| | app=U/sjl | 《《《权限加好了
(1 row)
```


--此时就可以操作sjl.t1表了吗？
```
test=> \c - app
You are now connected to database "test" as user "app".
test=> select * from sjl.t1;
ERROR: permission denied for table t1 《《《不可以，因为没有表的权限。
```


--授予表权限，我们只给了select权限，你可以按需给权限。
```
\c - sjl
test=> grant SELECT ON sjl.t1 TO app;
GRANT
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | | 《《《对应权限只有r代表read
```


--此时可以查询了
```
\c - app
test=> select * from sjl.t1;
id
----
1
2
3
(3 rows)
```


--我们把所有表的权限都给app用户
```
test=> grant SELECT ON ALL tables in schema sjl TO app；
GRANT
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t2 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t3 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t4 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t5 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t6 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t7 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t8 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
```
## 新增表自动授权        
--我们再切换至sjl用户创建t9表，你会发现t9表的权限是空的，上述的操作只能对现有表生效。
```
test=> create table sjl.t9(id int);
CREATE TABLE
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t2 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t3 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t4 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t5 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t6 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t7 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t8 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t9 | table | | |
(9 rows)
```




--先添加新增表的权限，再创建t10表。可以看到t9表的权限还是空的，因为他不属于新增表，而t10表的权限自动加好了。
```
test=> alter default privileges in schema sjl grant all on tables to app;
ALTER DEFAULT PRIVILEGES
test=> create table sjl.t10(id int);
CREATE TABLE
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
sjl | t1 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t10 | table | sjl=arwdDxt/sjl +| | 《《《《
| | | app=arwdDxt/sjl | |
sjl | t2 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t3 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t4 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t5 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t6 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t7 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t8 | table | sjl=arwdDxt/sjl +| |
| | | app=r/sjl | |
sjl | t9 | table | | |
(7 rows)
```


# 总结：
pg的权限划分很严很细，层层权限缺一不可。要访问一张表，首先通过认证(pg_hba.conf和listener_address)，用户要有LOGIN权限，用户要有表所在数据库的connect权限，用户还要有schema的usage权限和表的select权限，还有相应的列的查询权限。

usage权限让我很费解，使用权怎么理解，它属于schema的权限而不是表。
打个比方：有一间房子就好比schema，而表，函数，序列等相当于房子里的东西。当你只有usage的权限时，你只能进入这个房子，但不能看，也不能碰房子里东西，再授予select权限后你才可以看他们，授予update等权限后你才可以碰他们。同理你只有表的权限，而没有usage权限时，因为你不能进入这个房子，即使你有表的权限你也看不到，碰不到他们。


--此时就是没有schema的usage权限。schema权限\dn+
```
test=> select * from sjl.t1;
ERROR:  permission denied for schema sjl
```




--此时就是没有sjl.t1的表权限。查看表权限\dp+
```
test=> select * from sjl.t1;
ERROR:  permission denied for table t1
```

pg
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论