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)