PostgreSQL密码安全策略

网站建设1160 更新时间：2025-06-08 09:03:37

2023年7月31日发(作者：)

PostgreSQL密码安全策略引⽤地址：请以原⽂为主，引⽤注明出处。问题：今天公司进⾏软件测评，在测评期间，测评⼈员问起PostgreSQL登录失败导致⽤户锁定的次数，密码输错⼏次账户会被锁定？⽹上查了⼀圈，oracle和mysql都有相关设置，只有pg库没有找到相关的设置参数。偶然发现⽹上的帖⼦，结果发现PG库尚不⽀持相关设置。下⾯引⽤⼀下：数据库密码管理是数据库安全的重要环节之⼀。密码管理及配置策略主要包括：密码加密存储密码有效期密码复杂度密码验证失败延迟密码验证失败次数限制，失败后锁定, 以及解锁时间设置密码时防⽌密码被记录到数据库⽇志中下⾯会依次讲解在PostgreSQL中如何实现密码相关的安全性配置。1、密码加密存储pg中密码始终以加密⽅式存储在系统⽬录中。ENCREPED 关键字没有任何效果, 但被接受向后兼容。加密⽅式可以通过password_encryption参数配置。postgres=# show password_encryption;password_encryption

---------+----------+-------------+----------+---------+--------------+----------+------------------------+-----------test | 49156 | f | f | f | f | ******** | 2019-04-10 16:58:00+08 |

(1 row)客户端连接测试[postgres@pg2 ~]$ dateWed Apr 10 17:11:49 CST 2019[postgres@pg2 ~]$ psql -h 192.168.6.12 -U test -d postgres -p 5432Password for user test:

psql: FATAL: password authentication failed for user "test"注意：pg密码有效期仅针对客户端有效，服务器端不受限制。⽹络访问控制⽂件中不能配置为trust认证⽅式3、密码复杂度策略模块可以实现密码复杂度要求，此模块可以检查密码，如果密码太弱，他会拒绝连接创建⽤户或修改⽤户密码时，强制限制密码的复杂度，限制密码不能重复使⽤例如密码长度，包含数字，字母，⼤⼩写，特殊字符等，同时排除暴⼒破解字典中的字符串3.1、启⽤模块添加'$libdir/passwordcheck'到参数shared_preload_libraries，重启⽣效默认so⽂件都存放在$libdir⽬录下[pg@pg ~]$ ls -atl $LD_LIBRARY_PATH/passwordcheck*-rwxr-xr-x 1 pg pg 8640 Feb 1 14:23 /opt/postgres/lib/gres=# select name,setting from pg_settings where name like '%dynamic%';name | setting

----------------------------+---------dynamic_library_path | $libdirdynamic_shared_memory_type | posix(2 rows)postgres=# alter system set shared_preload_libraries=pg_pathman,pg_stat_statements,passwordcheck;ALTER SYSTEMpostgres=#

重启⽣效shared_preload_libraries参数使⽤参考“Postgresql共享库预加载(Shared Library Preloading)”3.2、复杂度功能验证密码复杂度检查模块Passwordcheck验证创建的⽤户密码是否符合规则。密码：最少8个字符；必须包含数字和字母；密码中不能含有⽤户名字段。postgres=# alter role test with password 'test';ERROR: password is too shortpostgres=# alter role test password '12345678';ERROR: password must contain both letters and nonletterspostgres=# alter role test with password 'test1234';ERROR: password must not contain user namepostgres=# alter role test with password 'tttt1234';ALTER ROLE4、密码验证失败延迟auth_模块会导致服务器在报告⾝份验证失败之前短暂停留,这个主要⽤于防⽌暴⼒破解. 验证失败后, 延迟⼀个时间窗⼝才能继续验证。请注意, 它不会阻⽌拒绝服务攻击,甚⾄可能会加剧这些攻击, 因为在报告⾝份验证失败之前等待的进程仍将使⽤连接插槽。4.1、启⽤模块需要配置以下参数，实现密码验证延迟失败延迟so⽂件存储在$libdir下[pg@pg lib]$ ls -atl $LD_LIBRARY_PATH/auth_delay*-rwxr-xr-x 1 pg pg 8432 Feb 1 14:23 /opt/postgres/lib/auth_参数修改shared_preload_libraries --预加载模块auth_econds (int) --指定延迟时间postgres=# alter system set shared_preload_libraries=pg_pathman, pg_stat_statements, passwordcheck,auth_delay;ALTER SYSTEM重启⽣效postgres=# alter system set auth_econds=5000;ALTER SYSTEMreload⽣效4.2、验证[pg@pg ~]$ psql -h 192.168.6.12 -U test -p 5432 -d postgresPassword for user test:

--5spsql: FATAL: password authentication failed for user "test"[pg@pg ~]$输⼊密码后，如果密码不正确，会等待5s，然后返回密码失败提⽰[pg@pg ~]$ psql -h 192.168.6.12 -U test -p 5432 -d postgresPassword for user test:

psql (10.4)Type "help" for es=>

输⼊密码后，如果密码正确，没有等待。5、密码验证失败次数限制，失败后锁定, 以及解锁时间⽬前PostgreSQL不⽀持这个安全策略, ⽬前只能使⽤auth_delay来延长暴⼒破解的时间.6、设置密码时防⽌密码被记录到数据库⽇志中密码的配置命令可能会被记录到history⽂件及csvlog⽇志⽂件中（如果开启了DDL或更⾼级别审计log_statement），这些⽂件明⽂记录了密码，可能造成密码泄露风险。6.1、密码记录到两个地⽅HISTFILEThe file name that will be used to store the history list. If unset, the file name is taken from the PSQL_HISTORY environment variable. If that is not set either, the default is ~/.psql_history, or %APPDATA%postgresqlpsql_history

set HISTFILE ~/.psql_history- :DBNAMEin ~/.psqlrc will cause psql to maintain a separate history for each is feature was shamelessly plagiarized from Bash. --csvlog

数据库错误⽇志事例：如以下命令，会记录到HISTFILE和csvlog⽇志中postgres=# alter role test with password 'tttt1234';ALTER ROLEhistory file记录[pg@pg ~]$ cat ~/.psql_history |grep tttt1234alter role test with password 'tttt1234';[pg@pg ~]$

csvlog记录[pg@pg ~]$ cat $PGDATA/ |grep log_statement#log_statement = 'none' # none, ddl, mod, alllog_statement = 'ddl'#log_statement_stats = off[pg@pg ~]$

[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_ |grep tttt12342019-04-12 09:33:23.036 CST,"pg","postgres",1309,"[local]",5cafeadb.51d,3,"idle",2019-04-12 09:33:15 CST,3/21,0,LOG,00000,"statement: alter role test with password 'tttt1234';",,,,,,,,,"psql"6.2、解决⽅式1. 使⽤createuser命令⾏⼯具-W选项提⽰输⼊密码。2. 使⽤pg_md5⼯具⽣成密码, 在psql中使⽤ALTER ROLE填⼊md5值。与上⾯类似, pg_md5是pgpool提供的⼀个⼯具, 实际上就是调⽤上⾯的函数。[pg@pg ~]$ createuser -l -h 127.0.0.1 -p 5432 -U pg -W tuserPassword:

[pg@pg ~]$

[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_ |grep tuser2019-04-12 11:17:48.348 CST,"pg","postgres",1574,"localhost:42560",5cb0035c.626,3,"idle",2019-04-12 11:17:48 CST,3/236,0,LOG,00000,"statement: CREATE ROLE tuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN

2023年7月31日发(作者：)

PostgreSQL密码安全策略引⽤地址：请以原⽂为主，引⽤注明出处。问题：今天公司进⾏软件测评，在测评期间，测评⼈员问起PostgreSQL登录失败导致⽤户锁定的次数，密码输错⼏次账户会被锁定？⽹上查了⼀圈，oracle和mysql都有相关设置，只有pg库没有找到相关的设置参数。偶然发现⽹上的帖⼦，结果发现PG库尚不⽀持相关设置。下⾯引⽤⼀下：数据库密码管理是数据库安全的重要环节之⼀。密码管理及配置策略主要包括：密码加密存储密码有效期密码复杂度密码验证失败延迟密码验证失败次数限制，失败后锁定, 以及解锁时间设置密码时防⽌密码被记录到数据库⽇志中下⾯会依次讲解在PostgreSQL中如何实现密码相关的安全性配置。1、密码加密存储pg中密码始终以加密⽅式存储在系统⽬录中。ENCREPED 关键字没有任何效果, 但被接受向后兼容。加密⽅式可以通过password_encryption参数配置。postgres=# show password_encryption;password_encryption

---------+----------+-------------+----------+---------+--------------+----------+------------------------+-----------test | 49156 | f | f | f | f | ******** | 2019-04-10 16:58:00+08 |

(1 row)客户端连接测试[postgres@pg2 ~]$ dateWed Apr 10 17:11:49 CST 2019[postgres@pg2 ~]$ psql -h 192.168.6.12 -U test -d postgres -p 5432Password for user test:

psql: FATAL: password authentication failed for user "test"注意：pg密码有效期仅针对客户端有效，服务器端不受限制。⽹络访问控制⽂件中不能配置为trust认证⽅式3、密码复杂度策略模块可以实现密码复杂度要求，此模块可以检查密码，如果密码太弱，他会拒绝连接创建⽤户或修改⽤户密码时，强制限制密码的复杂度，限制密码不能重复使⽤例如密码长度，包含数字，字母，⼤⼩写，特殊字符等，同时排除暴⼒破解字典中的字符串3.1、启⽤模块添加'$libdir/passwordcheck'到参数shared_preload_libraries，重启⽣效默认so⽂件都存放在$libdir⽬录下[pg@pg ~]$ ls -atl $LD_LIBRARY_PATH/passwordcheck*-rwxr-xr-x 1 pg pg 8640 Feb 1 14:23 /opt/postgres/lib/gres=# select name,setting from pg_settings where name like '%dynamic%';name | setting

----------------------------+---------dynamic_library_path | $libdirdynamic_shared_memory_type | posix(2 rows)postgres=# alter system set shared_preload_libraries=pg_pathman,pg_stat_statements,passwordcheck;ALTER SYSTEMpostgres=#

重启⽣效shared_preload_libraries参数使⽤参考“Postgresql共享库预加载(Shared Library Preloading)”3.2、复杂度功能验证密码复杂度检查模块Passwordcheck验证创建的⽤户密码是否符合规则。密码：最少8个字符；必须包含数字和字母；密码中不能含有⽤户名字段。postgres=# alter role test with password 'test';ERROR: password is too shortpostgres=# alter role test password '12345678';ERROR: password must contain both letters and nonletterspostgres=# alter role test with password 'test1234';ERROR: password must not contain user namepostgres=# alter role test with password 'tttt1234';ALTER ROLE4、密码验证失败延迟auth_模块会导致服务器在报告⾝份验证失败之前短暂停留,这个主要⽤于防⽌暴⼒破解. 验证失败后, 延迟⼀个时间窗⼝才能继续验证。请注意, 它不会阻⽌拒绝服务攻击,甚⾄可能会加剧这些攻击, 因为在报告⾝份验证失败之前等待的进程仍将使⽤连接插槽。4.1、启⽤模块需要配置以下参数，实现密码验证延迟失败延迟so⽂件存储在$libdir下[pg@pg lib]$ ls -atl $LD_LIBRARY_PATH/auth_delay*-rwxr-xr-x 1 pg pg 8432 Feb 1 14:23 /opt/postgres/lib/auth_参数修改shared_preload_libraries --预加载模块auth_econds (int) --指定延迟时间postgres=# alter system set shared_preload_libraries=pg_pathman, pg_stat_statements, passwordcheck,auth_delay;ALTER SYSTEM重启⽣效postgres=# alter system set auth_econds=5000;ALTER SYSTEMreload⽣效4.2、验证[pg@pg ~]$ psql -h 192.168.6.12 -U test -p 5432 -d postgresPassword for user test:

--5spsql: FATAL: password authentication failed for user "test"[pg@pg ~]$输⼊密码后，如果密码不正确，会等待5s，然后返回密码失败提⽰[pg@pg ~]$ psql -h 192.168.6.12 -U test -p 5432 -d postgresPassword for user test:

psql (10.4)Type "help" for es=>

输⼊密码后，如果密码正确，没有等待。5、密码验证失败次数限制，失败后锁定, 以及解锁时间⽬前PostgreSQL不⽀持这个安全策略, ⽬前只能使⽤auth_delay来延长暴⼒破解的时间.6、设置密码时防⽌密码被记录到数据库⽇志中密码的配置命令可能会被记录到history⽂件及csvlog⽇志⽂件中（如果开启了DDL或更⾼级别审计log_statement），这些⽂件明⽂记录了密码，可能造成密码泄露风险。6.1、密码记录到两个地⽅HISTFILEThe file name that will be used to store the history list. If unset, the file name is taken from the PSQL_HISTORY environment variable. If that is not set either, the default is ~/.psql_history, or %APPDATA%postgresqlpsql_history

set HISTFILE ~/.psql_history- :DBNAMEin ~/.psqlrc will cause psql to maintain a separate history for each is feature was shamelessly plagiarized from Bash. --csvlog

数据库错误⽇志事例：如以下命令，会记录到HISTFILE和csvlog⽇志中postgres=# alter role test with password 'tttt1234';ALTER ROLEhistory file记录[pg@pg ~]$ cat ~/.psql_history |grep tttt1234alter role test with password 'tttt1234';[pg@pg ~]$

csvlog记录[pg@pg ~]$ cat $PGDATA/ |grep log_statement#log_statement = 'none' # none, ddl, mod, alllog_statement = 'ddl'#log_statement_stats = off[pg@pg ~]$

[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_ |grep tttt12342019-04-12 09:33:23.036 CST,"pg","postgres",1309,"[local]",5cafeadb.51d,3,"idle",2019-04-12 09:33:15 CST,3/21,0,LOG,00000,"statement: alter role test with password 'tttt1234';",,,,,,,,,"psql"6.2、解决⽅式1. 使⽤createuser命令⾏⼯具-W选项提⽰输⼊密码。2. 使⽤pg_md5⼯具⽣成密码, 在psql中使⽤ALTER ROLE填⼊md5值。与上⾯类似, pg_md5是pgpool提供的⼀个⼯具, 实际上就是调⽤上⾯的函数。[pg@pg ~]$ createuser -l -h 127.0.0.1 -p 5432 -U pg -W tuserPassword:

[pg@pg ~]$

[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_ |grep tuser2019-04-12 11:17:48.348 CST,"pg","postgres",1574,"localhost:42560",5cb0035c.626,3,"idle",2019-04-12 11:17:48 CST,3/236,0,LOG,00000,"statement: CREATE ROLE tuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN

本文发布于:2023-07-31，感谢您对本站的认可！

本文链接:http://torson.com.cn/wangzhan/1690807026a432463.html