- 修改集群（实例）的配置，要求如下： 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
