创建开发库 dpgsql , 测试库 ppgsql, 生产库 ppgsql
创建开发库对象为例:
创建用户 srv_qpgsql
create user srv_qpgsql with ENCRYPTED password 'srv_qpgsql';
创建 schema, 并赋予所有者为用户 srv_qpgsql
- CREATE SCHEMA qpgsql;
- ALTER SCHEMA qpgsql OWNER to srv_qpgsql;
创建 schema 表空间
- mkdir -p /data/pgsql_5432/tbs_qpgsql
- CREATE TABLESPACE tbs_qpgsql OWNER srv_qpgsql LOCATION '/data/pgsql_5432/tbs_qpgsql/';
创建所有者是 srv_qpgsql 的数据库
CREATE DATABASE qpgsql WITH OWNER srv_qpgsql ENCODING UTF8 TEMPLATE template1 TABLESPACE tbs_qpgsql;
创建只读用户:
赋予用户连接数据库权限:
GRANT CONNECT ON DATABASE foo to r_user;
切换到指定数据库:
\c foo
赋予用户表, 序列查看权限:
- GRANT USAGE ON SCHEMA public to r_user;
- GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO r_user;
- GRANT SELECT ON ALL TABLES IN SCHEMA public TO r_user;
来源: http://www.bubuko.com/infodetail-3602432.html