浅谈PostgreSQL用户权限

# 创建用户lottu1
postgres=# create user lottu1;
CREATE ROLE
# 创建用户lottu2
postgres=# create user lottu2;
CREATE ROLE
# 创建数据库db1；属于lottu1
postgres=# create database db1 owner  lottu1;
CREATE DATABASE
# 创建schema、table、并插入记录
postgres=# c db1 lottu1;
You are now connected to database "db1" as user "lottu1".
db1=> create schema lottu1;
CREATE SCHEMA
db1=> create table tbl_lottu_01(id int, info text, reg_time timestamp);
CREATE TABLE
db1=> insert into tbl_lottu_01 select 1,"lottu",now();
INSERT 0 1

db1=> c db1 lottu2
You are now connected to database "db1" as user "lottu2".

db1=> c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# revoke CONNECT ON DATABASE db1 from public;
REVOKE
db1=# c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# c db1 lottu1;
You are now connected to database "db1" as user "lottu1".
db1=> c db1 lottu2;
FATAL:  permission denied for database "db1"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

db1=> grant CONNECT ON DATABASE db1 to lottu2;
GRANT
db1=> grant USAGE ON SCHEMA lottu1 to lottu2;
GRANT
db1=> grant select on TABLE tbl_lottu_01 to lottu2;
GRANT
db1=> c db1 lottu2;
You are now connected to database "db1" as user "lottu2".
db1=> select * from lottu1.tbl_lottu_01;
 id | info  |          reg_time          
----+-------+----------------------------
  1 | lottu | 2020-05-19 10:50:15.206569
(1 row)

grant select on ALL TABLES IN SCHEMA lottu1 to lottu2;