1 前言

一个问题，一篇文章，一出故事。
笔者今天需要给PostgreSQL创建一个只读用户，于是整理当前章节。

2 最佳实践

2.1 准备PostgreSQL环境

如何部署Oracle Linux 9.x PostgreSQL？

2.2 配置权限

2.2.1 使用超级用户登陆

su - postgres
psql

2.2.2 创建数据库

CREATE DATABASE "appDB";

2.2.3 创建应用用户

CREATE USER "appUser" WITH PASSWORD 'appUserPWD';

2.2.4 授予连接权限

GRANT CONNECT ON DATABASE "appDB" TO "appUser";

2.2.5 授予Schema权限

GRANT USAGE, CREATE ON SCHEMA public TO "appUser";

2.2.6 授予表的读写权限

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "appUser";

上面授权，如果没有执行“psql -U postgres -d appDB”，请执行“/c appDB”再执行，否则授权失败。
另外，如果需要指定独立的单表，请使用如下命令，

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE myTable TO "appUser";

确保新表自动取得权限

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "appUser";

2.2.7 授予序列的读写权限

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "appUser";

确保新序列自动取得权限

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO "appUser";

2.2.8 设置访问控制

vim /etc/pgsql/pg_hba.conf

加入如下配置，

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all             ::1/128                 ident

配置完成后，你可能需要重载服务使配置生效，

systemctl reload postgresql

2.3 常用权限验证操作

2.3.1 用户身份连接数据库

psql -U "appUser" -d "appDB" -h localhost

2.3.2 验证CREATE权限

CREATE TABLE test_table (id INT, name VARCHAR(50));

2.3.3 验证INSERT权限

INSERT INTO test_table VALUES (1, 'test');

2.3.4 验证SELECT 权限

SELECT * FROM test_table;

2.3.5 验证DELETE权限

DELETE FROM test_table WHERE id=1;