postgresql常用创建用户和授权-526互联

需求

（1）给用户a创建一个数据库，并且给a用户对这个库有所有权限
（2）给read_a用户对这个数据库有只读权限

步骤

1.创建用户a
2.创建数据库db_a，并设置owner为a
3.回收默认的public schema create权限
4.设置db_a的public schema 默认的owner 为a
5.创建只读用户read_a
6.用a用户给read_a用户设置默认的权限
7.给read_a用户设置对public schema 查询权限

具体操作如下：

db_test=# create user a with password '1234';        # 1. 创建用户a
CREATE ROLE
db_test=# create database db_a with owner a;         # 2. 创建数据库db_a， owner为a
CREATE DATABASE    
db_test=# \c db_a;
You are now connected to database "db_a" as user "postgres".
db_a=# revoke create on schema public from public;    # 3. 回收默认public create权限， 这样就不是每个人都可以在这里创建表了
REVOKE
db_a=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

db_a=# alter schema  public owner to a;              # 4. 设置db_a 的public schema的owner 为a
ALTER SCHEMA
db_a=# \dn
List of schemas
  Name  | Owner 
--------+-------
 public | a
(1 row)

db_a=# create user read_a with password '1234';       # 5. 创建只读用户read_a
CREATE ROLE
db_a=# \c - a                                         # 切换到用户a, db_a数据库
You are now connected to database "db_a" as user "a".
db_a=> alter default privileges in schema public grant select on tables to read_a;     # 6. 修改默认权限   
ALTER DEFAULT PRIVILEGES


db_a=> GRANT USAGE ON SCHEMA public to read_a;        # 6.授权read_a 对public schema权限
GRANT
db_a=> GRANT SELECT ON ALL TABLES IN SCHEMA  public to read_a;    # 授权read_a 对public schema权限
GRANT
db_a=> \ddp
         Default access privileges
 Owner | Schema | Type  | Access privileges 
-------+--------+-------+-------------------
 a     | public | table | read_a=r/a
(1 row)

postgres=> \c db_a                # 用a用户创建一个表t2，插入语句，用read_a查询测试一下
You are now connected to database "db_a" as user "a".
db_a=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t1   | table | a
(1 row)

db_a=> create table t2(id int);
CREATE TABLE
db_a=> insert into t2(id) values(1);
INSERT 0 1

db_a=> \c - read_a;            # 切换到read_a用户，测试查询t2表
You are now connected to database "db_a" as user "read_a".
db_a=> 
db_a=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t1   | table | a
 public | t2   | table | a
(2 rows)

db_a=> select * from t2;
 id 
----
  1
(1 row)