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)