a. 运行日志路径/pglog
b. 运行日志格式修改为‘csvlog‘
c. 每天生成一个新的日志文件
d. 不限制单个日志文件的大小
e. PostgreSQL 的运行 log 日志需要保留最近 1 个月的 log 日志文件。
f. 最多允许 100 个非超级用户的连接连入数据库中。
g. 运行日志内容中要体现时间戳(带毫秒，不适用 unix 时间戳)，用户名，数据库名称和进程 ID，依照上述顺序
h. 需要监听的地址设置为*，
i. 开启全页写配置
j. 数据库用户的密码加密算法修改为 scram-sha-256，并确保数据库用户能正常登录。
k. 配置好.pgpass 文件，以便 postgres 用户可以免密输入
pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# alter system set logging_collector=on;
ALTER SYSTEM
postgres=# \q
[pg10@data01 ~]$ su - root
Password:
Last login: Mon Jan 4 22:27:02 CST 2021 on pts/1
[root@data01 ~]# mkdir /pglog
[root@data01 ~]# chown pg10.pg10 /pglog
[root@data01 ~]# exit
logout
[pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# show log_directory;
log_directory
---------------
log
(1 row)
postgres=# alter system set log_directory=‘/pglog‘;
ALTER SYSTEM
postgres=# show log_destination;
log_destination
---------------
stderr
(1 row)
postgres=# alter system set log_destination=csvlog;
ALTER SYSTEM
postgres=#
[pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# alter system set logging_collector=on;
ALTER SYSTEM
postgres=# \q
[pg10@data01 ~]$ su - root
Password:
Last login: Mon Jan 4 22:27:02 CST 2021 on pts/1
[root@data01 ~]# mkdir /pglog
[root@data01 ~]# chown pg10.pg10 /pglog
[root@data01 ~]# exit
logout
[pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# show log_directory;
log_directory
log
(1 row)**
postgres=# alter system set log_directory=‘/pglog‘;
ALTER SYSTEM
postgres=# show log_destination;
log_destination
stderr
(1 row)
postgres=# alter system set log_destination=csvlog;
ALTER SYSTEM
postgres=#
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# alter system set password_encryption=‘scram-sha-256‘;
ALTER SYSTEM
postgres=# alter system set listen_address=‘*‘;
ERROR: unrecognized configuration parameter "listen_address"
postgres=# alter system set listen_addresses=‘*‘;
ALTER SYSTEM
postgres=# alter system set log_rotation_size =0;
ALTER SYSTEM
postgres=# alter system set log_truncate_on_rotation=on;
ALTER SYSTEM
postgres=# alter system set log_line_prefix=‘%m-%u-%d-%p‘;
ALTER SYSTEM
postgres=# alter system set log_checkpoints=off;
ALTER SYSTEM
postgres=# alter system set log_rotation_age=1440;
ALTER SYSTEM
postgres=# alter system set log_filename=‘postgresql.%d‘;
ALTER SYSTEM
postgres=# alter system set full_page_writes=on;
ALTER SYSTEM
postgres=# alter database postgres connection limit 100;
ALTER DATABASE
postgres=# alter database appdb connection limit 100;
ALTER DATABASE
postgres=#
postgres=#
postgres=# \q
[pg10@data01 ~]$
$ pg_ctl restart -D $PGDATA -l /tmp/logfile
[pg10@data01 ~]$ psql -d postgres -U postgres
psql (10.14)
Type "help" for help.
postgres=# select * from pg_authid;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypa***ls | rolconnlimit | rolpassword | rolvaliduntil ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+------------------------
pg10 | t | t | t | t | t | t | t | -1 | |
pg_monitor | f | t | f | f | f | f | f | -1 | |
pg_read_all_settings | f | t | f | f | f | f | f | -1 | |
pg_read_all_stats | f | t | f | f | f | f | f | -1 | |
pg_stat_scan_tables | f | t | f | f | f | f | f | -1 | |
pg_signal_backend | f | t | f | f | f | f | f | -1 | |
postgres | t | t | f | f | t | f | f | -1 | md58974bc84228498bd3aed770083258de1 |
xxx | f | t | f | f | t | f | f | -1 | md5ee7cdcdd6c1b0ae3715c921c03a929e1 |
appuser | f | t | f | t | t | f | f | -1 | md53db339d42b581e6c3ffd248be5660325 | 2022-05-01 00:00:00+08
readonlyuser | f | t | f | f | t | f | f | -1 | md59f9b164f064a95803e4f0f0d0251ea7f |
(10 rows)
postgres=# ALTER USER postgres WITH PASSWORD ‘1qaz@WSX‘;
ALTER ROLE
postgres=# ALTER USER appuser WITH PASSWORD ‘1qaz@WSX‘;
ALTER ROLE
postgres=# ALTER USER readonlyuser WITH PASSWORD ‘1qaz@WSX‘;
ALTER ROLE
postgres=# ALTER USER xxx WITH PASSWORD ‘1qaz@WSX‘;
ALTER ROLE
postgres=# select * from pg_authid;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypa***ls | rolconnlimit | rolpassword
| rolvaliduntil
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+----------------------------------------------------------------------------------------------------------------------
-----------------+------------------------
pg10 | t | t | t | t | t | t | t | -1 |
|
pg_monitor | f | t | f | f | f | f | f | -1 |
|
pg_read_all_settings | f | t | f | f | f | f | f | -1 |
|
pg_read_all_stats | f | t | f | f | f | f | f | -1 |
|
pg_stat_scan_tables | f | t | f | f | f | f | f | -1 |
|
pg_signal_backend | f | t | f | f | f | f | f | -1 |
|
postgres | t | t | f | f | t | f | f | -1 | SCRAM-SHA-256$4096:+R8EIMEgg2BPUxJB93UHvQ==$ECWx/3bfao9Cl8UOnuKRGcQALwZo5ES9xFSgQjnVcfs=:29ZHkjRGuPi/kKj+xgqgQ7IHRkr/
eXRaZhWwP5fk/Ew= |
appuser | f | t | f | t | t | f | f | -1 | SCRAM-SHA-256$4096:gOzsiSoQaB1Zg/JSCqpfRg==$5Oo8o/RHS6AGKmmu/hFEGb+twK6YchsQMazU9E+L2gk=:bu1YVl3dyj+X1+0XGWQ2KRF/dRGJ
eG0D3B/zEwtnJN4= | 2022-05-01 00:00:00+08
readonlyuser | f | t | f | f | t | f | f | -1 | SCRAM-SHA-256$4096:DIyZg6JQao2QLdV62onyGA==$ykKdKV3kitcI+M8dYW30LntPcKf+Cfmf4907rcyOg8A=:qlSk1WpcU59OBznTGH+td1zw7v5A
5oeBjBQ9wyqSUno= |
xxx | f | t | f | f | t | f | f | -1 | SCRAM-SHA-256$4096:PY1RyRR7u/WXt7ACaoJKSw==$tFswveoRSs7vwXnCDRnKHi22RdYs97xm7CfwY4qFSpk=:UGppJ9FQU/0f6KWgkZyyFrf7Onc+
Pow8RMr2UkIRMOU= | (10 rows)
配置pg_hba.conf
[pg10@data01 ~]$ egrep -v "^#" $PGDATA/pg_hba.conf | egrep "scram-sha-256|reject"
local all postgres scram-sha-256
host all postgres 0.0.0.0/0 reject
host all all 0.0.0.0/0 scram-sha-256
然后pg_ctl reload