postgresql常用创建用户和授权

需求

（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)