一、 创建一个不带有权限的基本用户
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=>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27039319/viewspace-2079613/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27039319/viewspace-2079613/