postgres用户管理及权限控制--赋予某账号只读权限

最新推荐文章于 2023-03-09 10:36:41 发布

congjiong8459

最新推荐文章于 2023-03-09 10:36:41 发布

阅读量1.3k

点赞数

文章标签：数据库

一、创建一个不带有权限的基本用户 pg_client

点击(此处)折叠或打开

[postgres@v_22 bin]$ ./createuser --interactive pg_client;
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
[postgres@v_22 bin]$ ./psql
psql (9.3.6)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
------------------+------------------------------------------------+-----------
ctsi_lbs | Superuser, No inheritance | {}
ctsi_lbs_loc | Superuser, No inheritance | {}
ctsi_lbs_monitor | | {}
ctsi_lbs_sms | Superuser, No inheritance | {}
ispyufabu | Superuser, No inheritance | {}
newisp | Superuser, No inheritance | {}
pg_client | | {}
postgres | Superuser, Create role, Create DB, Replication | {}

二、登录账号并赋予pg_client查询表的权限，但是在list of roles attributes 并未出现select信息

点击(此处)折叠或打开

[postgres@v_22 bin]$ ./psql
psql (9.3.6)
Type "help" for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------------+------------------+----------+-------------+-------------+-----------------------
ctsi_lbs_db | ctsi_lbs | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
ctsi_lbs_geo_db | ctsi_lbs | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
ctsi_lbs_gis_db | ctsi_lbs | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
ctsi_lbs_loc_db | ctsi_lbs_loc | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
ctsi_lbs_monitor_db | ctsi_lbs_monitor | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
ctsi_lbs_sms_db | ctsi_lbs_sms | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
isptest | ispyufabu | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
newisp | newisp | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
[postgres@v_22 bin]$ ./psql -U ctsi_lbs -d ctsi_lbs_db
ctsi_lbs_db=# grant select on all tables in schema public to pg_client;
GRANT
ctsi_lbs_db=> \du
List of roles
Role name | Attributes | Member of
------------------+------------------------------------------------+-----------
ctsi_lbs | Superuser, No inheritance | {}
ctsi_lbs_loc | Superuser, No inheritance | {}
ctsi_lbs_monitor | | {}
ctsi_lbs_sms | Superuser, No inheritance | {}
ispyufabu | Superuser, No inheritance | {}
newisp | Superuser, No inheritance | {}
pg_client | | {}
postgres | Superuser, Create role, Create DB, Replication | {}

三、登录pg_client账号，验证权限是否成功

点击(此处)折叠或打开

[postgres@v_22 bin]$ ./psql -U pg_client -d ctsi_lbs_db
psql (9.3.6)
Type "help" for help.

ctsi_lbs_db=> \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | auto_loc_mdn | table | postgres
public | auto_location | table | postgres
public | ent_billing_file | table | postgres
public | last_location | table | postgres
public | loc_db_config | table | postgres
public | loc_status | table | postgres
public | location_navigator | table | postgres
(7 rows)

ctsi_lbs_db=> select * from loc_status;
loc_code | definition | scene | remark
----------+----------------------------------------------------------------+-------+--------
0 | 成功 | |
1 | 基础定位平台异常 | |
2 | 基础定位平台繁忙或超出定位请求上限 | |
3 | 核心网忙而无法处理请求 | |
4 | SPID鉴权失败（SPID不存在或IP地址不存在或状态错误） | |
5 | SPID密码错误 | |
6 | 请求包中号码超过限制 | |
7 | 请求包格式有问题 | |
8 | 请求包的语法不对 | |
9 | 定位时超出定位请求上限 | |
10 | 取消周期定位消息时reqid不存在 | |
11 | 未知的MSID | |
12 | 回叫号码未知 | |
13 | 无效的请求信息 | |
14 | 返回的是手机的信道信息 | |
15 | 未检测到信号 | |
16 | PDE超时 | |
17 | 位置未确定 | |
18 | 返回的是TDMA MAHO信息 | |
19 | TDMA MAHO信息不可得达 | |
ctsi_lbs_db=> update loc_status set loc_code = 300 where loc_code= 0;
ERROR: permission denied for relation loc_status
ctsi_lbs_db=> insert into loc_status values ( 300,'hello',null ,null);
ERROR: permission denied for relation loc_status
ctsi_lbs_db=>

四、验证pg_client 用户属性

点击(此处)折叠或打开

ctsi_lbs_db=> SELECT * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
ctsi_lbs | t | f | f | f | t | t | f | -1 | ******** | | | 16389
ctsi_lbs_loc | t | f | f | f | t | t | f | -1 | ******** | | | 16390
ispyufabu | t | f | f | f | t | t | f | -1 | ******** | | | 16428
ctsi_lbs_sms | t | f | f | f | t | t | f | -1 | ******** | | | 17220
ctsi_lbs_monitor | f | t | f | f | f | t | f | -1 | ******** | | | 36755
newisp | t | f | f | f | t | t | f | -1 | ******** | | | 36944
pg_client | f | t | f | f | f | t | f | -1 | ******** | | | 38510
(8 rows)

ctsi_lbs_db=>