Table of Contents

• 如果角色已经存在, 可以采用如下方法进行删除
• 权限实例

如果角色已经存在, 可以采用如下方法进行删除

psql -U pgsql -d postgres

reassign owned by r_read to pgsql;
drop owned by r_read;
drop user if exists r_read;

reassign owned by r_read_write to pgsql;
drop owned by r_read_write;
drop user if exists  r_read_write;

reassign owned by u01 to pgsql;
drop owned by u01;
drop user if exists  u01;

reassign owned by u02 to pgsql;
drop owned by u02;
drop user  if exists u02;

drop database if exists test;

权限实例

create database test template=template0 encoding='UTF8' locale='C';
revoke create on schema public from public;

\c test pgsql
drop table if exists t1;
drop table if exists t2;

create table t1(id int, name varchar);
create table t2(id int, name varchar);
insert into t1 values(1,'a'),(2,'b');
insert into t2 values(1,'a'),(2,'b');


select * from t1;
select * from t2;

-- 创建只读角色
create role r_read;
grant select on all tables in schema public to r_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to r_read;

-- 创建读写角色
create role r_read_write;
grant select, insert,update,delete  on all tables in schema public to r_read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select, insert, update, delete  on tables to r_read_write;

-- 创建只读用户
create user u01; 
grant r_read to u01;

-- 对于未来创建的表也授予 select 权限

-- 创建读写用户
create user u02; 
grant r_read_write to u02;

\c test u01
select * from t1;
select * from t2;
insert into t1 values(3,'c'); -- 报错才是正常的


\c test u02
select * from t1;
select * from t2;
insert into t1 values(3,'c');
insert into t2 values(3,'c');


\c test pgsql
create table t3(id int);
insert into t3 values(1);

\c test u01
select * from t3;
insert into t3 values(2);

\c test u02 
select * from t3;
insert into t3 values(2);  -- 可以正常插入


# 实操
```sql
[pgsql@mysql01 ~]$ psql -U pgsql -d postgres
psql (14.5)
Type "help" for help.

postgres=# create database test template=template0 encoding='UTF8' locale='C';

CREATE DATABASE
postgres=# revoke create on schema public from public;
REVOKE
postgres=# 
postgres=# \c test pgsql
You are now connected to database "test" as user "pgsql".
test=# create table t1(id int, name varchar);
CREATE TABLE
test=# create table t2(id int, name varchar);
CREATE TABLE
test=# insert into t1 values(1,'a'),(2,'b');
[pgsql@mysql01 ~]$ psql -U pgsql -d postgres -c "drop database test;"
DROP DATABASE
[pgsql@mysql01 ~]$ psql -U pgsql -d postgres
psql (14.5)
postgres=# drop database if exists test;
DROP DATABASE
postgres=# create database test template=template0 encoding='UTF8' locale='C';
CREATE DATABASE
postgres=# revoke create on schema public from public;
REVOKE
postgres=# \c test pgsql
You are now connected to database "test" as user "pgsql".
test=# drop table if exists t1;
NOTICE:  table "t1" does not exist, skipping
DROP TABLE
test=# drop table if exists t2;
NOTICE:  table "t2" does not exist, skipping
DROP TABLE
test=# 
test=# create table t1(id int, name varchar);
CREATE TABLE
test=# create table t2(id int, name varchar);
CREATE TABLE
test=# insert into t1 values(1,'a'),(2,'b');
INSERT 0 2
test=# insert into t2 values(1,'a'),(2,'b');
INSERT 0 2
test=# 
test=# select * from t1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

test=# select * from t2;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

test=# 
test=# -- 创建只读角色
test=# create role r_read;
CREATE ROLE
test=# grant select on all tables in schema public to r_read;
GRANT
test=# ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to r_read;
ALTER DEFAULT PRIVILEGES
test=# create role r_read_write;
CREATE ROLE
test=# grant select, insert,update,delete  on all tables in schema public to r_read_write;
GRANT
test=# ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select, insert, update, delete  on tables to r_read_write;
ALTER DEFAULT PRIVILEGES
test=# create user u01; 
CREATE ROLE
test=# grant r_read to u01;
GRANT ROLE
test=# 
test=# create user u02; 
CREATE ROLE
test=# grant r_read_write to u02;
GRANT ROLE
test=# \c test u01
You are now connected to database "test" as user "u01".
test=> select * from t1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

test=> select * from t2;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

test=> insert into t1 values(3,'c'); -- 报错才是正常的
ERROR:  permission denied for table t1
test=> \c test u02
You are now connected to database "test" as user "u02".
test=> select * from t1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

test=> select * from t2;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

test=> insert into t1 values(3,'c');
INSERT 0 1
test=> insert into t2 values(3,'c');
INSERT 0 1
test=> 
test=> \c test pgsql
You are now connected to database "test" as user "pgsql".
test=# create table t3(id int);
CREATE TABLE
test=# insert into t3 values(1);
INSERT 0 1
test=# 
test=# \c test u01
You are now connected to database "test" as user "u01".
test=> select * from t3;
 id 
----
  1
(1 row)

test=> insert into t3 values(2);
ERROR:  permission denied for table t3
test=> \c test u02
You are now connected to database "test" as user "u02".
test=> select * from t3;
 id 
----
  1
(1 row)

test=> insert into t3 values(2);
INSERT 0 1
test=>