pg database security configuration

Keywords: Database PostgreSQL network Session

Safety configuration instruction


Database security configuration is one of the important links of database management. Security configuration mainly includes password, network access control, audit, etc. Next, we will explain how to realize the password related security configuration in Hangao database.

1 password encrypted storage

The password in pg is always stored in the system directory encrypted. The encrypt keyword has no effect, but is accepted for backward compatibility. The encryption mode can be configured through the password  encryption parameter

--create role test with login encryped password 'test';
create role test with login password 'test';

show password_encryption;
(1 row)

select usename,passwd from pg_shadow where usename='test';
 usename |               passwd                
 test    | md505a671c66aefea124cc08b76ea6d30bb
(1 row)

2 password validity

pg supports the configuration of password validity period. You can set the password replacement period by configuring the password validity period.

Set the validity period on the server side

alter role test valid until '2020-04-24 10:10:00';

select usename,valuntil  from pg_user where usename='test';
 usename |        valuntil        
 test    | 2020-12-10 16:58:00+08

Client connection test. After the password expires, you need to reset the password

//Error report for expiration date

Fri Apr 24 10:19:02 CST 2020

psql -h -U test -d highgo
Password for user test:
psql: FATAL: password authentication failed for user "test"

//Modification validity
alter role test valid until '2020-12-31 10:10:00';

//Normal landing
psql -h -U test -d highgo
Password for user test: 
psql (5.6.5)

PSQL: Release 5.6.5
Connected to:
HighGo Database V5.6 Enterprise Edition Release 5.6.5 - 64-bit Production

Type "help" for help.


be careful:

  • If pg_hba.conf is configured with local trust, you can log in to the server without restriction.

3 password complexity strategy

The module can meet the password complexity requirements. This module can check the password. If the password is too weak, he will refuse to connect When creating or modifying a user's password, the complexity of the password must be limited, and the password cannot be reused For example, password length, including numbers, letters, case, special characters, etc., while excluding brute force cracking of strings in the dictionary reference resources Official documents

3.1 enable module

Add the passwordcheck 'under the' $libdir directory to the parameter shared The default so files are stored in the $libdir directory

select name,setting from pg_settings where name like '%dynamic%';
name | setting
dynamic_library_path | $libdir
dynamic_shared_memory_type | posix
(2 rows)

ls -atl $PGHOME/lib/postgresql/passwordcheck*
-rwxr-xr-x 1 postgres postgres 8616 Feb 27 10:09 /opt/pg122/lib/postgresql/

alter system set shared_preload_libraries=passwordcheck;

//Restart effective

The shared library preloading parameter uses the reference "shared library preloading"

3.2 complexity function verification

Password check module

  • Verify that the created user password meets the rules. Password: at least 8 characters; must contain numbers and letters; password cannot contain user name field.
alter role test with password 'test';
ERROR: password is too short
alter role test password '12345678';
ERROR: password must contain both letters and nonletters
alter role test with password 'test1234';
ERROR: password must not contain user name
alter role test with password 'tttt1234';

4. Password verification failure delay

The module will cause the server to pause for a short time before reporting the authentication failure, which is mainly used to prevent brute force cracking. After the authentication failure, the authentication can be continued only after a time window is delayed. Note that it does not prevent denial of service attacks, and may even aggravate them, because the processes waiting before reporting authentication failures will still use the connection slots.

4.1 enable module

The following parameters need to be configured to delay password verification failure

so files are stored under $libdir

ls -atl $PGHOME/lib/auth_delay*
-rwxr-xr-x 1 postgres postgres 8352 Feb 27 10:09 /opt/pg122/lib/postgresql/

Parameter modification

Shared? Preload? Libraries -- preload module
 auth_delay.milliseconds (int) -- specify delay time
alter system set shared_preload_libraries=passwordcheck,auth_delay;
Restart effective
alter system set auth_delay.milliseconds=5000;
reload takes effect

Note: auth · delay.milliseconds can only be set after auth · delay is enabled

4.2 verification

psql -h -U test -p 5432 -d postgres
Password for user test:
psql: FATAL: password authentication failed for user "test"

//After entering the password, if the password is incorrect, it will wait for 5s, and then return to the password failure prompt
psql -h -U test -p 5432 -d postgres
Password for user test:
psql (10.4)
Type "help" for help.

//After entering the password, if the password is correct, there is no waiting.

5. Password verification failure limit, lock after failure, and unlock time

At present, PostgreSQL does not support this security policy. At present, it can only use auth? Delay to extend the time of brute force cracking

6. Prevent password from being recorded in database log when setting password

The password configuration command may be recorded in the history file and csvlog log file (if DDL or higher-level audit log statement is enabled), which records the password in clear text, which may cause the risk of password disclosure.

6.1. Password recording to two places


  • HISTFILE The 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%\postgresql\psql_history on Windows. For example, putting: \set HISTFILE ~/.psql_history- :DBNAME in ~/.psqlrc will cause psql to maintain a separate history for each database. Note This feature was shamelessly plagiarized from Bash. --
  • csvlog Database error log

Record As the following command, it will be recorded in the HISTFILE and csvlog logs

postgres=# alter role test with password 'tttt1234';
  • history file record [pg@pg ~]$ cat ~/.psql_history |grep tttt1234 alter role test with password 'tttt1234'; [pg@pg ~]$
  • csvlog record [pg@pg ~]$ cat $PGDATA/postgresql.conf |grep log_statement #log_statement = 'none'         # none, ddl, mod, all log_statement = 'ddl' #log_statement_stats = off

[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_092557.csv |grep tttt1234 2019-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" [pg@pg ~]$

6.2 solutions

Use the createuser command-line tool - W option to prompt for a password. .

[pg@pg ~]$ createuser -l -h -p 5432 -U pg -W tuser
[pg@pg ~]$
[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_092557.csv |grep tuser
2019-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;",,,,,,,,,"createuser"
[pg@pg ~]$
  • Note: you can use the PG md5 tool provided by PG pool to generate the password, and use ALTER ROLE to fill in the md5 value in psql. Similar to the above, PG [MD5] is a tool provided by pgpool, which actually calls the above functions

7 network access control

Configure network access control in the $PGDATA/pg_hba.conf file to restrict connection segments and IP addresses

For example:

# IP reject set
host       all           all              reject
host       all           all              reject

reload takes effect pg_ctl reload

Explanation: Column 1: type, host indicates TCP/IP connection Column 2: database. All means that all databases can be accessed The third column: database user, all means to allow all database users to connect Column 4: address, specify specific host name or IP address Column 5: verification method, reject means connection is rejected

be careful:

1. If there is no IP matching record in $PGDATA/pg_hba.conf, access will be automatically denied 2. Each connection attempt will check the records in the pg_hba.conf file in order, so the IP records should be placed in the front position

8 authority allocation

For the allocation of user rights, the principle of minimum rights is recommended. Grant only the required permissions to the user.

Management of all permissions

For all permissions of objects, it is recommended to use schema for management. The general process is as follows 1. Create a new schema

create schema SCHEMA_NAME;

2. Grant users the right to manage this schema

grant all on schema SCHEMA_NAME to USER_NAME;

3. Set to add the schema to the search path

alter database "benchmarksql" set search_path to "$user", public,SCHEMA_NAME; 

Just add a new path, do not change the original value.

Specific object authorization management

Specific object authorization reference related syntax

Possible authorization syntax

Grant the user operation permission to some or all tables under a certain schema

[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

Grant the user EXECUTE permission (EXECUTE) or ALL permissions (ALL) to a function under a schema

ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

*Grant the user operation permission to some or all sequences under a certain schema

[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

Grant users administrative rights to a database

ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Grant users permission to external data

ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Give users permission to use the language, such as pl/pgsql, PL/Tcl, etc

ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Grant users the right to manage schema

ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Grant users permission to manage tablespaces

ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Grant users permission to manage data types

ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
[ GROUP ] role_name

Grant permissions to one user to another

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

Note: the user is a special role in pg. USAGE means only use permission, WITH ADMIN OPTION means that the receiver has permission to grant the object to other users.

9 audit configuration

Only the audit mode of database logging is discussed here. pg also has a special audit plug-in (pg audit) for professional audit. For pg audit, please refer to relevant articles.

Configure log parameters, audit connection information and SQL statements, and generate audit information in database log. Perform the following statement configuration:

alter system set log_destination = 'csvlog';
alter system set logging_collector=on;

alter system set log_connections=on;
alter system set log_disconnections=on;
alter system set log_statement = 'mod';

reload takes effect pg_ctl reload

Explanation: log_destination: PG logging method Logging collector: start a background process to grab the log information and write it to the log file

log_connections: records of trying to connect to the server and completing client authentication Log u disconnections: record of session termination and session duration Log menu statement: logged statement

Log statement parameter value: none, i.e. no record DDL (record create,drop and alter) Mod (record ddl+insert,delete,update and truncate)        all(mod+select)

Posted by zavin on Tue, 12 May 2020 01:04:56 -0700