[pg10@data01 ~]$ psql -d postgres
psql (10.14)
Type "help" for help.
postgres=# create user appuser with createdb login valid until ‘2022-05-01‘ password ‘1qaz@WSX‘;
CREATE ROLE
postgres=# \q
[pg10@data01 ~]$ su - root
Password:
Last login: Mon Jan 4 22:02:58 CST 2021 from gateway on pts/1
[root@data01 ~]# mkdir /appuser
[root@data01 ~]# chown pg10.pg10 /appuser
[root@data01 ~]# su - pg10
Last login: Mon Jan 4 22:03:26 CST 2021 on pts/1
[pg10@data01 ~]$ vim .pgpass
[pg10@data01 ~]$ cat .pgpass br/>data01:5666:postgres:postgres:1qaz@WSX
data01:5666:postgres:appuser:1qaz@WSX
[pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# create tablespace appuser owner appuser location ‘/appuser‘;
CREATE TABLESPACE
postgres=# create database appdb with owner appuser tablespace appuser;
CREATE DATABASE
postgres=# \c appdb appuser
You are now connected to database "appdb" as user "appuser".
appdb=> exit
[pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# \c appdb postgres
You are now connected to database "appdb" as user "postgres".
appdb=# revoke create on schema public from public;
REVOKE
appdb=# \c appdb appuser
You are now connected to database "appdb" as user "appuser".
appdb=> create schema appuser;
CREATE SCHEMA
appdb=> create table appuser.app(id int);
CREATE TABLE
appdb=> revoke connect on database appdb from public;
REVOKE
appdb=>
appdb=> \c appdb postgres
You are now connected to database "appdb" as user "postgres".
appdb=# create user readonlyuser with password ‘1qaz@WSX‘;
CREATE ROLE
appdb=# grant connect on database appdb to readonlyuser;
GRANT
appdb=# \c appdb appuser
You are now connected to database "appdb" as user "appuser".
appdb=> grant usage on schema appuser to readonlyuser;
GRANT
appdb=> grant select on all tables in schema appuser to readonlyuser;
GRANT
appdb=> alter default privileges grant select on tables to readonlyuser;
ALTER DEFAULT PRIVILEGES
原文：https://blog.51cto.com/lishiyan/2582115