postgresql数据库基础
创建只读账号 1.1以初始化账号登入 [root@localhost ~]# psql -U postgres 1.2创建用户 postgres=# create role develop with login password '123456'; CREATE ROLE postgres=# select usename from pg_user; usename ---------- postgres test develop (3 rows) 1.3切换数据库 c current_product 1.4赋予只读权限 current_product=# grant select on all tables in schema public to develop; GRANT 1.5切换到develop用户 current_product=# c - develop You are now connected to database "current_product" as user "develop". 1.6检测是否拥有只读权限 current_product=> select * from test; id ---- (0 rows) 2创建读写账号 2.1初始账号登录 psql -U postgres 2.2查看用户 postgres=# select usename from pg_user; usename ---------- postgres test test1 u2 (4 rows) 2.3创建读写用户 postgres=# create role test2 with login password '123456'; CREATE ROLE postgres=# grant ALL on all tables in schema public to test2; #这种授权方式是不对的,test2用户对current_product数据库没有权限 GRANT 2.4检测用户是否有读写权限 postgres=# c - test2 You are now connected to database "postgres" as user "test2". 切换数据库 postgres=> c current_product You are now connected to database "current_product" as user "test2". current_product=> dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | aaa | table | postgres public | test | table | postgres (2 rows) current_product=> select * from aaa; #显示没有权限 ERROR: permission denied for relation aaa 2.5 正确的授权方式是 :切换到目标数据库,执行授权语句 postgres=# c current_product #切换到目标数据库 You are now connected to database "current_product" as user "postgres". current_product=# grant ALL on all tables in schema public to test2; #执行授权语句 GRANT 2.6 切换到读写用户,检测是否有权限 current_product=# c - test2 ###切换至读写用户 You are now connected to database "current_product" as user "test2". current_product=> dt ###查看几个表 List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | aaa | table | postgres public | test | table | postgres (2 rows) current_product=> select * from aaa; #查权限正常 id ---- (0 rows) current_product=> insert into aaa values(1); #增权限正常 INSERT 0 1 current_product=> select * from aaa; id ---- 1 (1 row) current_product=> delete from aaa; #删除权限正常 DELETE 1 2.7 切换至超级用户 current_product=> c - postgres You are now connected to database "current_product" as user "postgres". current_product=# create table bbb(id int); ###新增一张表 CREATE TABLE 2.8 切换至读写用户 current_product=# c - test2 You are now connected to database "current_product" as user "test2". current_product=> dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | aaa | table | postgres public | bbb | table | postgres public | test | table | postgres (3 rows) current_product=> select * from bbb; #显示无权限 ERROR: permission denied for relation bbb 2.9 解决办法: 每次新增表都执行一次授权语句,否则无权限(其它方法正在探索中……) current_product=> c - postgres You are now connected to database "current_product" as user "postgres". current_product=# grant ALL on all tables in schema public to test2; GRANT 切换至读写用户 , 检测权限 current_product=# c - test2 You are now connected to database "current_product" as user "test2". current_product=> select * from bbb; id ---- (0 rows) current_product=> insert into bbb values(2222); INSERT 0 1 current_product=> select * from bbb; id ------ 2222 (1 row) current_product=> delete from bbb; DELETE 1 current_product=> select * from bbb; id ---- (0 rows) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |