Safety configuration instruction
[TOC]
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; password_encryption --------------------- md5 (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 date Fri Apr 24 10:19:02 CST 2020 psql -h 192.168.6.10 -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 192.168.6.10 -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. highgo=>
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 passwordcheck.so 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/passwordcheck.so 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'; ALTER ROLE
4. Password verification failure delay
The auth_delay.so 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/auth_delay.so
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 192.168.6.10 -U test -p 5432 -d postgres Password for user test: --5s 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 192.168.6.12 -U test -p 5432 -d postgres Password for user test: psql (10.4) Type "help" for help. postgres=> //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
introduce
- 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 127.0.0.1 -p 5432 -U pg -W tuser Password: [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 192.168.6.1/32 reject host all all 192.168.6.0/24 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
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |TRIGGER } [, ...] | 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
GRANT { EXECUTE | ALL [ PRIVILEGES ] } 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
GRANT { { USAGE | SELECT | UPDATE } [, ...] | 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
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ]
Grant users permission to external data
GRANT { USAGE | ALL [ PRIVILEGES ] } 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
GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ]
Grant users the right to manage schema
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ]
Grant users permission to manage tablespaces
GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ]
Grant users permission to manage data types
GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] where role_specification can be: [ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER
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)