| PostgreSQL Security Assessement Report |
Detailled security assessment |
Identify and inspect configured repositories to ensure they are all valid and authorized sources of packages.
Inspect installed package to ensure they are all valid and authorized packages.
postgresql-11 11.22-2.pgdg22.04+1 amd64 The World's Most Advanced Open Source Relational Database postgresql-12 12.18-1.pgdg22.04+1 amd64 The World's Most Advanced Open Source Relational Database postgresql-13 13.14-1.pgdg22.04+1 amd64 The World's Most Advanced Open Source Relational Database postgresql-14 14.11-1.pgdg22.04+1 amd64 The World's Most Advanced Open Source Relational Database postgresql-14-hypopg 1.4.0-2.pgdg22.04+1 amd64 PostgreSQL extension adding support for hypothetical indexes. postgresql-14-postgis-3 3.4.2+dfsg-1.pgdg22.04+1 amd64 Geographic objects support for PostgreSQL 14 postgresql-14-postgis-3-scripts 3.4.2+dfsg-1.pgdg22.04+1 all Geographic objects support for PostgreSQL 14 -- SQL scripts postgresql-15 15.6-1.pgdg22.04+1 amd64 The World's Most Advanced Open Source Relational Database postgresql-15-citus-12.0 12.0.1.citus-1 amd64 sharding and distributed joins for PostgreSQL postgresql-15-postgis-3 3.4.2+dfsg-1.pgdg22.04+1 amd64 Geographic objects support for PostgreSQL 15 postgresql-15-postgis-3-scripts 3.4.2+dfsg-1.pgdg22.04+1 all Geographic objects support for PostgreSQL 15 -- SQL scripts postgresql-15-tds-fdw 2.0.3-3.pgdg22.04+1 amd64 PostgreSQL foreign data wrapper for TDS databases postgresql-16 16.2-1.pgdg22.04+1 amd64 The World's Most Advanced Open Source Relational Database postgresql-16-postgis-3 3.4.2+dfsg-1.pgdg22.04+1 amd64 Geographic objects support for PostgreSQL 16 postgresql-16-postgis-3-scripts 3.4.2+dfsg-1.pgdg22.04+1 all Geographic objects support for PostgreSQL 16 -- SQL scripts postgresql-9.3 9.3.25-9.pgdg22.04+3 amd64 object-relational SQL database, version 9.3 server postgresql-9.4 9.4.26-8.pgdg22.04+3 amd64 object-relational SQL database, version 9.4 server
PostgreSQL packages not supported by the PostgreSQL community are generaly not recommended.
Check that the PostgreSQL systemd service is enabled. Enabling the systemd PostgreSQL service ensures that the database service is active when at system startup and reboot. This check is not done if Patroni is installed, in this case the start of PostgreSQL is handled by Patroni. (HexaCLuster)
PostgreSQL enforces ownership and permissions of the data cluster such that the data cluster cannot be accessed by other UNIX user accounts and the data cluster cannot owned by root.
The command initdb might have been run before starting PostgreSQL, verify that this is the case.
PostgreSQL maintain a file called PG_VERSION in the base directory, verify that .
When checksum are not enabled, silent data corruption can not be detected by PostgreSQL. Verify that they are enabled. (*)
The PostgreSQL cluster is organized to carry out specific tasks in subdirectories. For the purposes of performance, reliability, and security some of these subdirectories should be relocated outside the data cluster. (*)
PostgreSQL storage encryption can be performed at the file system level or the block level, for example using LUKS. This mechanism prevents unencrypted data from being read from the drives if the drives or the entire computer is stolen. This does not protect against attacks while the file system is mounted, because when mounted, the operating system provides an unencrypted view of the data. (*)
NAME FSTYPE FSVER LABEL UUID FSAVAIL FSUSE% MOUNTPOINTS
nvme0n1
├─nvme0n1p1 vfat FAT32 9562-6409 449,5M 12% /boot/efi
├─nvme0n1p2 ext4 1.0 b094a4f8-e122-4507-aef2-b840488970a9 1,1G 27% /boot
└─nvme0n1p3 crypto_LUKS 2 7942f063-81b2-412f-a9d7-9b085d6635b2
└─nvme0n1p3_crypt LVM2_member LVM2 001 6v9nXI-HLMs-2b9u-Tbqx-hw51-JAsl-RjaiHL
├─vgubuntu-root ext4 1.0 dc73afb5-86bd-4405-aa11-576f1f023fcb 18,6G 91% /var/snap/firefox/common/host-hunspell
│ /
└─vgubuntu-swap_1 swap 1 e5cdf1a9-0ed6-4573-9c4b-8e8e1f918ece [SWAP]
| Name | Version | Schema | Description |
|---|---|---|---|
| credcheck | 2.6.0 | public | credcheck - postgresql plain text credential checker |
| plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language |
| Name | Version | Schema | Description |
|---|---|---|---|
| btree_gist | 1.7 | public | support for indexing common datatypes in GiST |
| credcheck | 2.6.0 | public | credcheck - postgresql plain text credential checker |
| oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access |
| orafce | 4.3 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS |
| pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed |
| pg_subxact_counters | 1.0 | public | |
| pg_wait_sampling | 1.1 | public | sampling based statistics of wait events |
| pgcrypto | 1.3 | public | cryptographic functions |
| plperl | 1.0 | pg_catalog | PL/Perl procedural language |
| plperlu | 1.0 | pg_catalog | PL/PerlU untrusted procedural language |
| plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language |
| plpgsql_enc | 2.0 | pg_catalog | Encrypted PL/pgSQL procedural language |
| postgis | 3.4.2 | public | PostGIS geometry and geography spatial types and functions |
| postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers |
The postgres system user should have a restrictive umask for file creation so that other UNIX users can not look at anything.
The data cluster Unix permissions must be 0700 (*).
The content of the PGDATA must be generated by PostgreSQL itself except custom configuration files. (*).
total 108 drwx------ 21 postgres postgres 4096 avril 17 16:39 . drwxr-xr-x 3 postgres postgres 4096 nov. 6 2022 .. drwx------ 32 postgres postgres 4096 avril 15 21:02 base drwx------ 2 postgres postgres 4096 avril 17 09:41 global drwx------ 2 postgres postgres 4096 mars 12 10:46 log drwx------ 2 postgres postgres 4096 nov. 6 2022 pg_commit_ts drwx------ 2 postgres postgres 4096 nov. 6 2022 pg_dynshmem drwx------ 4 postgres postgres 4096 avril 17 16:44 pg_logical drwx------ 4 postgres postgres 4096 nov. 6 2022 pg_multixact drwx------ 2 postgres postgres 4096 nov. 6 2022 pg_notify -rw------- 1 postgres postgres 8192 avril 15 21:02 pg_password_history drwx------ 2 postgres postgres 4096 nov. 6 2022 pg_replslot drwx------ 2 postgres postgres 4096 nov. 6 2022 pg_serial drwx------ 2 postgres postgres 4096 mars 14 20:27 pg_snapshots drwx------ 2 postgres postgres 4096 avril 17 09:40 pg_stat drwx------ 2 postgres postgres 4096 févr. 16 23:32 pg_stat_tmp drwx------ 2 postgres postgres 4096 déc. 4 18:14 pg_subtrans drwx------ 2 postgres postgres 4096 avril 17 16:40 pg_tblspc drwx------ 2 postgres postgres 4096 nov. 6 2022 pg_twophase -rw------- 1 postgres postgres 3 nov. 6 2022 PG_VERSION drwx------ 3 postgres postgres 4096 avril 15 21:02 pg_wal drwx------ 2 postgres postgres 4096 déc. 1 19:32 pg_xact -rw------- 1 postgres postgres 88 nov. 6 2022 postgresql.auto.conf -rw------- 1 postgres postgres 130 avril 17 09:40 postmaster.opts -rw------- 1 postgres postgres 100 avril 17 09:40 postmaster.pid drwx------ 3 postgres postgres 4096 avril 17 16:40 TB1
The pg_hba.conf UNIX permission must be 0640 or 0600, especially when it is stored outside the PGDATA (*).
The default permissions are 0777, meaning anyone can connect. Reasonable alternatives are 0770 (only user and group, see also unix_socket_group) and 0700 (only user). (*).
This section provides guidance with respect to PostgreSQL's logging behavior as it applies to security and auditing.
Having an audit trail is an important feature of any relational database system. You want enough detail to describe when an event of interest has started and stopped, what the event is/was, the event's cause, and what the event did/is doing to the system. Ideally, the logged information is in a format permitting further analysis giving us new perspectives and insight.
If log_destination is not set, then any log messages generated by the core PostgreSQL processes will be lost.
The logging collector approach is often more useful than logging to syslog, since some types of messages might not appear in syslog output. One common example is dynamic-linker failure message; another may be error messages produced by scripts such as archive_command.
If log_directory is not set, it is interpreted as the absolute path '/' and PostgreSQL will attempt to write its logs there
If log_filename is not set, then the value of log_directory is appended to an empty string and PostgreSQL will fail to start as it will try to write to a directory instead of a file.
Log files often contain sensitive data. Allowing unnecessary access to log files may inadvertently expose sensitive data to unauthorized personnel.
If this setting is disabled, pre-existing log files will be appended to if log_filename is configured in such a way that static or recurring names are generated.
Current best practices advise log rotation at least daily, but your organization's logging policy should dictate your rotation schedule.
If this is set to zero, the size-triggered creation of new log files is disabled. This will prevent automatic log file rotation when files become too large, which could put log data at increased risk of loss (unless age-based rotation is configured).
If not set to the appropriate facility, the PostgreSQL log messages may be intermingled with other applications log messages, incorrectly routed, or potentially dropped (depending on your syslog configuration).
If disabled, messages sent to Syslog could be suppressed and not logged. While a message is emitted stating that a given message was repeated and suppressed, the timestamp associated with these suppressed messages is lost, potentially damaging the recreation of an incident timeline.
Depending on the Syslog server in use, log messages exceeding 1024 bytes may be lost or, potentially, cause the Syslog server processes to abort.
If this is not set correctly, it may be difficult or impossible to distinguish PostgreSQL messages from other messages in Syslog logs.
If this is not set to the correct value, too many or too few messages may be written to the server log.
If this is not set to the correct value, too many erring or too few erring SQL statements may be written to the server log.
Enabling any of the DEBUG printing variables may cause the logging of sensitive information that would otherwise be omitted based on the configuration of the other logging settings.
Enabling any of the DEBUG printing variables may cause the logging of sensitive information that would otherwise be omitted based on the configuration of the other logging settings.
Enabling any of the DEBUG printing variables may cause the logging of sensitive information that would otherwise be omitted based on the configuration of the other logging settings.
If this setting is disabled, the "compact" format is used instead, significantly reducing the readability of the DEBUG statement log messages.
PostgreSQL does not maintain an internal record of attempted connections to the database for later auditing. It is only by enabling the logging of these attempts that one can determine if unexpected attempts are being made.
PostgreSQL does not maintain the beginning or ending of a connection internally for later review. It is only by enabling the logging of these that one can examine connections for failed attempts, 'over long' duration, or other anomalies.
If this is not set to the correct value, too many details or too few details may be logged.
Depending on your hostname resolution setup, enabling this setting might impose a non-negligible performance penalty. Additionally, the IP addresses that are logged can be resolved to their DNS names when reviewing the logs (unless dynamic hostnames are being used as part of your DHCP setup).
Properly setting log_line_prefix allows for adding additional information to each log entry (such as the user, or the database). Said information may then be of use in auditing or security reviews. The prefix should at least include '%m [%p]: db=%d,user=%u,app=%a,client=%h ' (for logging to stderr) and for syslog logging, the prefix should include 'user=%u,db=%d,app=%a,client=%h '.
Setting log_statement to align with your organization's security and logging policies facilitates later auditing and review of database activities.
Log entry timestamps should be configured for an appropriate time zone as defined by your organization's logging policy to ensure a lack of confusion around when a logged event occurred. Note that this setting affects only the timestamps present in the logs.
Best practice is to not write PostgreSQL logs into the PGDATA for performances reason and disk space use. (*)
The PostgreSQL Audit Extension (pgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility. The goal of pgAudit is to provide PostgreSQL users with the capability to produce audit logs often required to comply with government, financial, or ISO certifications.
These authorizations must be structured to block unauthorized use and/or corruption of vital data and services by setting restrictions on user capabilities.
By not restricting global administrative commands to superusers only, regular users granted excessive privileges may execute administrative commands with unintended and undesirable results.
| Role | Attributs | Description | |
|---|---|---|---|
| gilles | Superuser | {} | |
| postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
Functions in PostgreSQL can be created with the SECURITY DEFINER option. When SECURITY DEFINER functions are executed by a user, said function is run with the privileges of the user who created it, not the user who is running it. List of the functions with SECURITY DEFINER option not associated to an extension, per database.
| oid | nspname | proname | rolname | prosecdef | proconfig | proacl |
|---|---|---|---|---|---|---|
| 511628 | public | add | gilles | t |
Excessive DML grants can lead to unprivileged users changing or deleting information without proper authorization.
| schema | table | user | select | insert | update | delete |
|---|---|---|---|---|---|---|
| public | t3 | dump_anon | t | f | f | f |
| public | customer | dump_anon | t | f | f | f |
| public | spatial_ref_sys | dump_anon | t | f | f | f |
| public | spatial_ref_sys | test | t | f | f | f |
| public | spatial_ref_sys | testuser | t | f | f | f |
| public | spatial_ref_sys | hr | t | f | f | f |
| public | spatial_ref_sys | u01 | t | f | f | f |
| public | spatial_ref_sys | pgtt_user1 | t | f | f | f |
| public | spatial_ref_sys | user | t | f | f | f |
| public | spatial_ref_sys | dolibarr | t | f | f | f |
| public | tab | dump_anon | t | f | f | f |
| utl_file | utl_file_dir | dump_anon | t | f | f | f |
| utl_file | utl_file_dir | test | t | f | f | f |
| utl_file | utl_file_dir | testuser | t | f | f | f |
| utl_file | utl_file_dir | hr | t | f | f | f |
| utl_file | utl_file_dir | u01 | t | f | f | f |
| utl_file | utl_file_dir | pgtt_user1 | t | f | f | f |
| utl_file | utl_file_dir | user | t | f | f | f |
| utl_file | utl_file_dir | dolibarr | t | f | f | f |
If RLS policies and privileges are not configured correctly, users could perform actions on tables that they are not authorized to perform, such as inserting, updating, or deleting rows. List tables with RLS enabled.
Even when reducing and limiting the access to the superuser role, it is still difficult to determine who accessed the superuser role and what actions were taken using that role. As such, it is ideal to prevent anyone from logging in as the superuser and forcing them to escalate their role. The set_user extension allows for this setup.
| rolname | roloid | rolcanlogin | rolsuper | rolparents |
|---|---|---|---|---|
| gilles | 16389 | t | t | {} |
| postgres | 10 | t | t | {} |
In keeping with the principle of least privilege, judicious use of the PostgreSQL predefined roles can greatly limit the access to privileged, or superuser, access.
| rolname | roloid | rolcanlogin | rolsuper | rolparents |
|---|---|---|---|---|
| pg_monitor | 3373 | f | f | {pg_read_all_settings} |
| pg_monitor | 3373 | f | f | {pg_read_all_stats} |
| pg_monitor | 3373 | f | f | {pg_stat_scan_tables} |
Privileges on the PostgreSQL default public schema must be restricted to some users, grant to public users must be removed or the schema dropped.
| nspname | nspowner | nspacl |
|---|---|---|
| public | 6171 | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner} |
| nspname | nspowner | nspacl |
|---|---|---|
| public | 6171 | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner,dump_anon=U/pg_database_owner} |
The restrictions on client/user connections to the PostgreSQL database blocks unauthorized access to data and services by setting access rules. These security measures help to ensure that successful logins cannot be easily made through brute-force password attacks, replaying the password hash, or intuited by clever social engineering exploits.
A remote host login, via SSH, is arguably the most secure means of remotely accessing and administering the PostgreSQL server. Once connected to the PostgreSQL server, using the psql client, via UNIX DOMAIN SOCKETS, while using the peer authentication method is the most secure mechanism available for local database connections.
local all all trust
A large number of authentication methods are available for hosts connecting using TCP/IP sockets. Methods trust, password, and ident are not to be used for remote logins. Method md5 used to be the most popular and can be used in both encrypted and unencrypted sessions, however, it is vulnerable to packet replay attacks. It is recommended that scram-sha-256 be used instead of md5. Use of the gss, sspi, pam, ldap, radius, and cert methods are dependent upon the availability of external authenticating processes/services and thus are not covered here.
host all all 192.168.43.0/24 trust
Having strong password management for your locally-authenticated PostgreSQL accounts will protect against attackers' brute force techniques. This is important especially if external authentication is not possible to implement due to application requirements or restrictions.
Authentication timeout is the maximum amount of time allowed to complete client authentication. If a would-be client has not completed the authentication protocol in this much time, the server closes the connection. This prevents hung clients from occupying a connection indefinitely. Authentication delay causes the server to pause briefly before reporting authentication failure, to make brute-force attacks on database passwords more difficult. (*)
All remote client connection should be encrypted and non encrypted connexion should be reject to not permit data sniffing on the network. (*)
Allowing a too large range of Ip addresses to connect to PostgreSQL cluster multiply the risks unnecessarily. (*)
The keyword "all" in the database and user part of the pg_hba.conf rules can allow any user to connect to any database, it is recommended to restrict the connection to specific user and database. (*)
host all all 127.0.0.1/32 scram-sha-256
host all all 172.18.0.3/32 scram-sha-256
host all all 192.168.43.0/24 trust
host all all ::1/128 scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
Allowing a PostgreSQL superuser to connect to a database from a remote host is dangerous, best is to only allow the superuser(s) to connect locally with a peer authentication. If some advanced privileges are required, best is to use the PostgreSQL predefined roles. (*)
PostgreSQL allow to set password encryption, default is now 'scram-sha-256' but it can be set to 'md5' which is insecure. (*)
A denial of service is possible by denying the use of indexes and by slowing down client access to an unreasonable level. Unsanctioned behavior can be introduced by introducing rogue libraries which can then be called in a database session. Logging can be altered and obfuscated inhibiting root cause analysis. All changes made on this level will affect the overall behavior of the server. These changes can only be affected by a server restart after the parameters have been altered in the configuration files.
The postmaster process is the supervisory process that assigns a backend process to an incoming client connection. The postmaster manages key runtime parameters that are either shared by all backend connections or needed by the postmaster process itself to run. The following parameters can only be set at server start by the owner of the PostgreSQL server process and cluster, typically the UNIX user account postgres. Therefore, all exploits require the successful compromise of either that UNIX account or the postgres superuser account itself.
| name | setting |
|---|---|
| archive_mode | off |
| autovacuum_freeze_max_age | 200000000 |
| autovacuum_max_workers | 3 |
| autovacuum_multixact_freeze_max_age | 400000000 |
| bonjour | off |
| bonjour_name | |
| cluster_name | 15/main |
| config_file | /etc/postgresql/15/main/postgresql.conf |
| credcheck.auth_failure_cache_size | 1024 |
| credcheck.history_max_size | 65535 |
| data_directory | /var/lib/postgresql/15/main |
| data_sync_retry | off |
| dynamic_shared_memory_type | posix |
| event_source | PostgreSQL |
| external_pid_file | /var/run/postgresql/15-main.pid |
| hba_file | /etc/postgresql/15/main/pg_hba.conf |
| hot_standby | on |
| huge_pages | try |
| huge_page_size | 0 |
| ident_file | /etc/postgresql/15/main/pg_ident.conf |
| ignore_invalid_pages | off |
| jit_provider | llvmjit |
| listen_addresses | * |
| logging_collector | off |
| max_connections | 100 |
| max_files_per_process | 1000 |
| max_locks_per_transaction | 64 |
| max_logical_replication_workers | 4 |
| max_pred_locks_per_transaction | 64 |
| max_prepared_transactions | 0 |
| max_replication_slots | 10 |
| max_wal_senders | 10 |
| max_worker_processes | 8 |
| min_dynamic_shared_memory | 0 |
| old_snapshot_threshold | -1 |
| port | 5432 |
| recovery_target | |
| recovery_target_action | pause |
| recovery_target_inclusive | on |
| recovery_target_lsn | |
| recovery_target_name | |
| recovery_target_time | |
| recovery_target_timeline | latest |
| recovery_target_xid | |
| shared_buffers | 16384 |
| shared_memory_type | mmap |
| shared_preload_libraries | credcheck |
| superuser_reserved_connections | 3 |
| track_activity_query_size | 1024 |
| track_commit_timestamp | off |
| unix_socket_directories | /var/run/postgresql |
| unix_socket_group | |
| unix_socket_permissions | 0777 |
| wal_buffers | 512 |
| wal_decode_buffer_size | 524288 |
| wal_level | replica |
| wal_log_hints | off |
In order to define server behavior and optimize server performance, the server's superuser has the privilege of setting these parameters which are found in the configuration files postgresql.conf and pg_hba.conf. Alternatively, those parameters found in postgresql.conf can also be changed using a server login session and executing the SQL command ALTER SYSTEM which writes its changes in the configuration file postgresql.auto.conf. All changes made on this level will affect the overall behavior of the server. These changes can be effected by editing the PostgreSQL configuration files and by either executing a server SIGHUP from the command line or, as superuser postgres, executing the SQL command select pg_reload_conf(). A denial of service is possible by the over-allocating of limited resources, such as RAM. Data can be corrupted by allowing damaged pages to load or by changing parameters to reinterpret values in an unexpected fashion, e.g. changing the time zone. Client messages can be altered in such a way as to interfere with the application logic. Logging can be altered and obfuscated inhibiting root cause analysis.
| name | setting |
|---|---|
| archive_cleanup_command | |
| archive_command | (disabled) |
| archive_library | |
| archive_timeout | 0 |
| authentication_timeout | 60 |
| autovacuum | on |
| autovacuum_analyze_scale_factor | 0.1 |
| autovacuum_analyze_threshold | 50 |
| autovacuum_naptime | 60 |
| autovacuum_vacuum_cost_delay | 2 |
| autovacuum_vacuum_cost_limit | -1 |
| autovacuum_vacuum_insert_scale_factor | 0.2 |
| autovacuum_vacuum_insert_threshold | 1000 |
| autovacuum_vacuum_scale_factor | 0.2 |
| autovacuum_vacuum_threshold | 50 |
| autovacuum_work_mem | -1 |
| bgwriter_delay | 200 |
| bgwriter_flush_after | 64 |
| bgwriter_lru_maxpages | 100 |
| bgwriter_lru_multiplier | 2 |
| checkpoint_completion_target | 0.9 |
| checkpoint_flush_after | 32 |
| checkpoint_timeout | 300 |
| checkpoint_warning | 30 |
| credcheck.auth_delay_ms | 0 |
| credcheck.reset_superuser | off |
| db_user_namespace | off |
| fsync | on |
| full_page_writes | on |
| hot_standby_feedback | off |
| krb_caseins_users | off |
| krb_server_keyfile | FILE:/etc/postgresql-common/krb5.keytab |
| log_autovacuum_min_duration | 600000 |
| log_checkpoints | on |
| log_destination | csvlog |
| log_directory | log |
| log_file_mode | 0600 |
| log_filename | postgresql-%a.log |
| log_hostname | off |
| log_line_prefix | %m [%p] %q%u@%d |
| log_recovery_conflict_waits | off |
| log_rotation_age | 1440 |
| log_rotation_size | 10240 |
| log_startup_progress_interval | 10000 |
| log_timezone | Europe/Paris |
| log_truncate_on_rotation | off |
| max_pred_locks_per_page | 2 |
| max_pred_locks_per_relation | -2 |
| max_slot_wal_keep_size | -1 |
| max_standby_archive_delay | 30000 |
| max_standby_streaming_delay | 30000 |
| max_sync_workers_per_subscription | 2 |
| max_wal_size | 1024 |
| min_wal_size | 80 |
| pre_auth_delay | 0 |
| primary_conninfo | |
| primary_slot_name | |
| promote_trigger_file | |
| recovery_end_command | |
| recovery_init_sync_method | fsync |
| recovery_min_apply_delay | 0 |
| recovery_prefetch | try |
| remove_temp_files_after_crash | on |
| restart_after_crash | on |
| restore_command | |
| ssl | on |
| ssl_ca_file | |
| ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem |
| ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL |
| ssl_crl_dir | |
| ssl_crl_file | |
| ssl_dh_params_file | |
| ssl_ecdh_curve | prime256v1 |
| ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key |
| ssl_max_protocol_version | |
| ssl_min_protocol_version | TLSv1.2 |
| ssl_passphrase_command | |
| ssl_passphrase_command_supports_reload | off |
| ssl_prefer_server_ciphers | on |
| synchronous_standby_names | |
| syslog_facility | local0 |
| syslog_ident | postgres |
| syslog_sequence_numbers | on |
| syslog_split_messages | on |
| trace_recovery_messages | log |
| vacuum_defer_cleanup_age | 0 |
| wal_keep_size | 0 |
| wal_receiver_create_temp_slot | off |
| wal_receiver_status_interval | 10 |
| wal_receiver_timeout | 60000 |
| wal_retrieve_retry_interval | 5000 |
| wal_sync_method | fdatasync |
| wal_writer_delay | 200 |
| wal_writer_flush_after | 128 |
In order to improve and optimize server performance, the server's superuser has the privilege of setting these parameters which are found in the configuration file postgresql.conf. Alternatively, they can be changed in a PostgreSQL login session via the SQL command ALTER SYSTEM which writes its changes in the configuration file postgresql.auto.conf. All changes made on this level will affect the overall behavior of the server. These changes can only be affected by a server restart after the parameters have been altered in the configuration files. A denial of service is possible by the over-allocating of limited resources, such as RAM. Data can be corrupted by allowing damaged pages to load or by changing parameters to reinterpret values in an unexpected fashion, e.g. changing the time zone. Client messages can be altered in such a way as to interfere with the application logic. Logging can be altered and obfuscated inhibiting root cause analysis.
| name | setting |
|---|---|
| allow_in_place_tablespaces | off |
| allow_system_table_mods | off |
| backtrace_functions | |
| commit_delay | 0 |
| compute_query_id | auto |
| credcheck.encrypted_password_allowed | off |
| credcheck.max_auth_failure | 3 |
| credcheck.no_password_logging | on |
| credcheck.password_contain | |
| credcheck.password_contain_username | on |
| credcheck.password_ignore_case | off |
| credcheck.password_min_digit | 0 |
| credcheck.password_min_length | 1 |
| credcheck.password_min_lower | 0 |
| credcheck.password_min_repeat | 0 |
| credcheck.password_min_special | 0 |
| credcheck.password_min_upper | 0 |
| credcheck.password_not_contain | |
| credcheck.password_reuse_history | 2 |
| credcheck.password_reuse_interval | 0 |
| credcheck.password_valid_max | 0 |
| credcheck.password_valid_until | 0 |
| credcheck.username_contain | |
| credcheck.username_contain_password | on |
| credcheck.username_ignore_case | off |
| credcheck.username_min_digit | 0 |
| credcheck.username_min_length | 1 |
| credcheck.username_min_lower | 0 |
| credcheck.username_min_repeat | 0 |
| credcheck.username_min_special | 0 |
| credcheck.username_min_upper | 0 |
| credcheck.username_not_contain | |
| credcheck.whitelist | |
| deadlock_timeout | 1000 |
| debug_discard_caches | 0 |
| dynamic_library_path | $libdir |
| extension_destdir | |
| ignore_checksum_failure | off |
| jit_dump_bitcode | off |
| lc_messages | en_US.UTF-8 |
| lo_compat_privileges | off |
| log_duration | off |
| log_error_verbosity | default |
| log_executor_stats | off |
| log_lock_waits | on |
| log_min_duration_sample | -1 |
| log_min_duration_statement | -1 |
| log_min_error_statement | error |
| log_min_messages | warning |
| log_parameter_max_length | -1 |
| log_parser_stats | off |
| log_planner_stats | off |
| log_replication_commands | off |
| log_statement | none |
| log_statement_sample_rate | 1 |
| log_statement_stats | off |
| log_temp_files | -1 |
| log_transaction_sample_rate | 0.01 |
| max_stack_depth | 2048 |
| session_preload_libraries | |
| session_replication_role | origin |
| temp_file_limit | -1 |
| track_activities | on |
| track_counts | on |
| track_functions | none |
| track_io_timing | off |
| track_wal_io_timing | off |
| update_process_title | on |
| wal_compression | off |
| wal_consistency_checking | |
| wal_init_zero | on |
| wal_recycle | on |
| zero_damaged_pages | off |
These PostgreSQL runtime parameters are managed at the user account (ROLE) level. In order to improve performance and optimize features, a ROLE has the privilege of setting numerous parameters in a transaction, session, or entity attribute. Any ROLE can alter any of these parameters. A denial of service is possible by the over-allocating of limited resources, such as RAM. Changing VACUUM parameters can force a server shutdown which is standard procedure preventing data corruption from transaction ID wraparound. Data can be corrupted by changing parameters to reinterpret values in an unexpected fashion, e.g. changing the time zone. Logging can be altered and obfuscated to inhibit root cause analysis.
| name | setting |
|---|---|
| application_name | psql |
| array_nulls | on |
| backend_flush_after | 0 |
| backslash_quote | safe_encoding |
| bytea_output | hex |
| check_function_bodies | on |
| client_connection_check_interval | 0 |
| client_encoding | UTF8 |
| client_min_messages | notice |
| commit_siblings | 5 |
| constraint_exclusion | partition |
| cpu_index_tuple_cost | 0.005 |
| cpu_operator_cost | 0.0025 |
| cpu_tuple_cost | 0.01 |
| cursor_tuple_fraction | 0.1 |
| DateStyle | ISO, DMY |
| debug_pretty_print | on |
| debug_print_parse | off |
| debug_print_plan | off |
| debug_print_rewritten | off |
| default_statistics_target | 100 |
| default_table_access_method | heap |
| default_tablespace | |
| default_text_search_config | pg_catalog.french |
| default_toast_compression | pglz |
| default_transaction_deferrable | off |
| default_transaction_isolation | read committed |
| default_transaction_read_only | off |
| effective_cache_size | 524288 |
| effective_io_concurrency | 1 |
| enable_async_append | on |
| enable_bitmapscan | on |
| enable_gathermerge | on |
| enable_hashagg | on |
| enable_hashjoin | on |
| enable_incremental_sort | on |
| enable_indexonlyscan | on |
| enable_indexscan | on |
| enable_material | on |
| enable_memoize | on |
| enable_mergejoin | on |
| enable_nestloop | on |
| enable_parallel_append | on |
| enable_parallel_hash | on |
| enable_partition_pruning | on |
| enable_partitionwise_aggregate | off |
| enable_partitionwise_join | off |
| enable_seqscan | on |
| enable_sort | on |
| enable_tidscan | on |
| escape_string_warning | on |
| exit_on_error | off |
| extra_float_digits | 1 |
| force_parallel_mode | off |
| from_collapse_limit | 8 |
| geqo | on |
| geqo_effort | 5 |
| geqo_generations | 0 |
| geqo_pool_size | 0 |
| geqo_seed | 0 |
| geqo_selection_bias | 2 |
| geqo_threshold | 12 |
| gin_fuzzy_search_limit | 0 |
| gin_pending_list_limit | 4096 |
| hash_mem_multiplier | 2 |
| idle_in_transaction_session_timeout | 0 |
| idle_session_timeout | 0 |
| IntervalStyle | postgres |
| jit | on |
| jit_above_cost | 100000 |
| jit_expressions | on |
| jit_inline_above_cost | 500000 |
| jit_optimize_above_cost | 500000 |
| jit_tuple_deforming | on |
| join_collapse_limit | 8 |
| lc_monetary | fr_FR.UTF-8 |
| lc_numeric | fr_FR.UTF-8 |
| lc_time | fr_FR.UTF-8 |
| local_preload_libraries | |
| lock_timeout | 0 |
| logical_decoding_work_mem | 65536 |
| log_parameter_max_length_on_error | 0 |
| maintenance_io_concurrency | 10 |
| maintenance_work_mem | 65536 |
| max_parallel_maintenance_workers | 2 |
| max_parallel_workers | 8 |
| max_parallel_workers_per_gather | 2 |
| min_parallel_index_scan_size | 64 |
| min_parallel_table_scan_size | 1024 |
| parallel_leader_participation | on |
| parallel_setup_cost | 1000 |
| parallel_tuple_cost | 0.1 |
| password_encryption | scram-sha-256 |
| plan_cache_mode | auto |
| quote_all_identifiers | off |
| random_page_cost | 4 |
| recursive_worktable_factor | 10 |
| row_security | on |
| search_path | "$user", public |
| seq_page_cost | 1 |
| standard_conforming_strings | on |
| statement_timeout | 0 |
| stats_fetch_consistency | cache |
| synchronize_seqscans | on |
| synchronous_commit | on |
| tcp_keepalives_count | 9 |
| tcp_keepalives_idle | 7200 |
| tcp_keepalives_interval | 75 |
| tcp_user_timeout | 0 |
| temp_buffers | 1024 |
| temp_tablespaces | |
| TimeZone | Europe/Paris |
| timezone_abbreviations | Default |
| trace_notify | off |
| trace_sort | off |
| transaction_deferrable | off |
| transaction_isolation | read committed |
| transaction_read_only | off |
| transform_null_equals | off |
| vacuum_cost_delay | 0 |
| vacuum_cost_limit | 200 |
| vacuum_cost_page_dirty | 20 |
| vacuum_cost_page_hit | 1 |
| vacuum_cost_page_miss | 2 |
| vacuum_failsafe_age | 1600000000 |
| vacuum_freeze_min_age | 50000000 |
| vacuum_freeze_table_age | 150000000 |
| vacuum_multixact_failsafe_age | 1600000000 |
| vacuum_multixact_freeze_min_age | 5000000 |
| vacuum_multixact_freeze_table_age | 150000000 |
| wal_sender_timeout | 60000 |
| wal_skip_threshold | 2048 |
| work_mem | 4096 |
| xmlbinary | base64 |
| xmloption | content |
Install, configure, and use OpenSSL on a platform that has a NIST certified FIPS 140-2 installation of OpenSSL. This provides PostgreSQL instances the ability to generate and validate cryptographic hashes to protect unclassified information requiring confidentiality and cryptographic protection, in accordance with the data owner's requirements.
OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)
If TLS is not enabled and configured correctly, this increases the risk of data being compromised in transit. A self-signed certificate can be used for testing, but a certificate signed by a certificate authority (CA) (either one of the global CAs or a local one) should be used in production so that clients can verify the server's identity. If all the database clients are local to the organization, using a local CA is recommended. To ultimately enable and enforce TLS authentication for the server, appropriate "hostssl" records must be added to the pg_hba.conf file.
PostgreSQL instances handling data that requires "data at rest" protections must employ cryptographic mechanisms to prevent unauthorized disclosure and modification of the information at rest. These cryptographic mechanisms may be native to PostgreSQL or implemented via additional software or operating system/file system settings, as appropriate to the situation.
As it is not necessary to be a superuser to initiate a replication connection, it is proper to create an account specifically for replication. This allows further "locking down" the uses of the superuser account and follows the general principle of using the least privileges necessary.
A successful replication connection allows for a complete copy of the data stored within the data cluster to be offloaded to another, potentially insecure, host. As such, it is advisable to log all replication commands that are executed in your database cluster to ensure the data is not off-loaded to an unexpected/undesired location.
A 'base backup' is a copy of the PRIMARY host's data cluster (PGDATA) and is used to create STANDBY hosts and for Point In Time Recovery (PITR) mechanisms. Base backups should be copied across networks in a secure manner using an encrypted transport mechanism. The PostgreSQL CLI pg_basebackup can be used, however, TLS encryption should be enabled on the server as per section 6.8 of this benchmark.
Write Ahead Log (WAL) Archiving, or Log Shipping, is the process of sending transaction log files from the PRIMARY host either to one or more STANDBY hosts or to be archived on a remote storage device for later use, e.g. PITR. There are several utilities that can copy WALs including, but not limited to, cp, scp, sftp, and rynsc. Basically, the server follows a set of runtime parameters which define when the WAL should be copied using one of the aforementioned utilities.
Streaming replication from a PRIMARY host transmits DDL, DML, passwords, and other potentially sensitive activities and data. These connections should be protected with Secure Sockets Layer (SSL). Verify on STANDBY that primary_conninfo contains 'sslmode=require sslcompression=1'
The recommendations proposed here try to address some of the less common use cases which may warrant additional configuration guidance/consideration.
This report is part of chapter "1.3 Ensure Data Cluster Initialized Successfully".
The native PostgreSQL backup facility pg_dump provides adequate logical backup operations but does not provide for Point In Time Recovery (PITR). The PostgreSQL facility pg_basebackup performs a physical backup of the database files and does provide for PITR, but it is constrained by single threading. Both of these methodologies are standard in the PostgreSQL ecosystem and appropriate for particular backup/recovery needs. pgBackRest offers another option with much more robust features and flexibility.
This recommendation covers non-regular, special files, and dynamic libraries. PostgreSQL permits local logins via the UNIX DOMAIN SOCKET and, for the most part, anyone with a legitimate Unix login account can make the attempt. Limiting PostgreSQL login attempts can be made by relocating the UNIX DOMAIN SOCKET to a subdirectory with restricted permissions. The creation and implementation of user-defined dynamic libraries is an extraordinary powerful capability. In the hands of an experienced DBA/programmer, it can significantly enhance the power and flexibility of the RDBMS; but new and unexpected behavior can also be assigned to the RDBMS, resulting in a very dangerous environment in what should otherwise be trusted.
| name | setting |
|---|---|
| dynamic_library_path | $libdir |
| external_pid_file | /var/run/postgresql/15-main.pid |
| local_preload_libraries | |
| session_preload_libraries | |
| shared_preload_libraries | credcheck |
| unix_socket_directories | /var/run/postgresql |
(*) Check not part of the CIS Benchmark
| Copyright © 2024 : HexaCluster Corp | Report generated by pgdsat v1.0. |