1<?php 2/* 3** Zabbix 4** Copyright (C) 2001-2021 Zabbix SIA 5** 6** This program is free software; you can redistribute it and/or modify 7** it under the terms of the GNU General Public License as published by 8** the Free Software Foundation; either version 2 of the License, or 9** (at your option) any later version. 10** 11** This program is distributed in the hope that it will be useful, 12** but WITHOUT ANY WARRANTY; without even the implied warranty of 13** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 14** GNU General Public License for more details. 15** 16** You should have received a copy of the GNU General Public License 17** along with this program; if not, write to the Free Software 18** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 19**/ 20 21 22/** 23 * Database backend class for PostgreSQL. 24 */ 25class PostgresqlDbBackend extends DbBackend { 26 /** 27 * Database name. 28 * 29 * @var string 30 */ 31 protected $dbname = ''; 32 33 /** 34 * DB schema. 35 * 36 * @var string 37 */ 38 protected $schema = ''; 39 40 /** 41 * User name. 42 * 43 * @var string 44 */ 45 protected $user = ''; 46 47 /** 48 * Check if 'dbversion' table exists. 49 * 50 * @return bool 51 */ 52 protected function checkDbVersionTable() { 53 $table_exists = DBfetch(DBselect( 54 'SELECT 1 FROM information_schema.tables'. 55 ' WHERE table_catalog='.zbx_dbstr($this->dbname). 56 ' AND table_schema='.zbx_dbstr($this->schema). 57 ' AND table_name='.zbx_dbstr('dbversion') 58 )); 59 60 if (!$table_exists) { 61 $this->setError(_s('Unable to determine current Zabbix database version: %1$s.', 62 _s('the table "%1$s" was not found', 'dbversion') 63 )); 64 65 return false; 66 } 67 68 return true; 69 } 70 71 /** 72 * Check database and table fields encoding. 73 * 74 * @return bool 75 */ 76 public function checkEncoding() { 77 global $DB; 78 79 return $this->checkDatabaseEncoding($DB) && $this->checkTablesEncoding($DB); 80 } 81 82 /** 83 * Check database schema encoding. On error will set warning message. 84 * 85 * @param array $DB Array of database settings, same as global $DB. 86 * 87 * @return bool 88 */ 89 protected function checkDatabaseEncoding(array $DB) { 90 $row = DBfetch(DBselect('SELECT pg_encoding_to_char(encoding) db_charset FROM pg_database'. 91 ' WHERE datname='.zbx_dbstr($DB['DATABASE']) 92 )); 93 94 if ($row && $row['db_charset'] != ZBX_DB_POSTGRESQL_ALLOWED_CHARSET) { 95 $this->setWarning(_s('Incorrect default charset for Zabbix database: %1$s.', 96 _s('"%1$s" instead "%2$s"', $row['db_charset'], ZBX_DB_POSTGRESQL_ALLOWED_CHARSET) 97 )); 98 return false; 99 } 100 101 return true; 102 } 103 104 /** 105 * Check tables schema encoding. On error will set warning message. 106 * 107 * @param array $DB Array of database settings, same as global $DB. 108 * 109 * @return bool 110 */ 111 protected function checkTablesEncoding(array $DB) { 112 $schema = $DB['SCHEMA'] ? $DB['SCHEMA'] : 'public'; 113 $row = DBfetch(DBselect('SELECT oid FROM pg_namespace WHERE nspname='.zbx_dbstr($schema))); 114 115 /** 116 * Getting all fields in all Zabbix tables to check for collation. 117 * If collation is not default (its mean collation is the same that was during db creation), 118 * than we consider it as error. 119 */ 120 $tables = DBfetchColumn(DBSelect('SELECT c.relname AS table_name FROM pg_attribute AS a'. 121 ' LEFT JOIN pg_class AS c ON c.relfilenode=a.attrelid'. 122 ' LEFT JOIN pg_collation AS l ON l.oid=a.attcollation'. 123 ' WHERE '.dbConditionInt('atttypid', [25, 1043]). 124 ' AND '.dbConditionInt('c.relnamespace', [$row['oid']]). 125 ' AND c.relam=0 AND '.dbConditionString('c.relname', array_keys(DB::getSchema())). 126 ' AND l.collname!='.zbx_dbstr('default') 127 ), 'table_name'); 128 129 if ($tables) { 130 $tables = array_unique($tables); 131 $this->setWarning(_n('Unsupported charset or collation for table: %1$s.', 132 'Unsupported charset or collation for tables: %1$s.', 133 implode(', ', $tables), implode(', ', $tables), count($tables) 134 )); 135 return false; 136 } 137 138 return true; 139 } 140 141 /** 142 * Check if database is using IEEE754 compatible double precision columns. 143 * 144 * @return bool 145 */ 146 public function isDoubleIEEE754() { 147 global $DB; 148 149 $conditions_or = [ 150 '(table_name=\'history\' AND column_name=\'value\')', 151 '(table_name=\'trends\' AND column_name IN (\'value_min\', \'value_avg\', \'value_max\'))' 152 ]; 153 154 $sql = 155 'SELECT COUNT(*) cnt FROM information_schema.columns'. 156 ' WHERE table_catalog='.zbx_dbstr($DB['DATABASE']). 157 ' AND table_schema='.zbx_dbstr($DB['SCHEMA'] ? $DB['SCHEMA'] : 'public'). 158 ' AND data_type=\'double precision\''. 159 ' AND ('.implode(' OR ', $conditions_or).')'; 160 161 $result = DBfetch(DBselect($sql)); 162 163 return (is_array($result) && array_key_exists('cnt', $result) && $result['cnt'] == 4); 164 } 165 166 /** 167 * Check is current connection contain requested cipher list. 168 * 169 * @return bool 170 */ 171 public function isConnectionSecure() { 172 $row = DBfetch(DBselect('SHOW server_version')); 173 $is_secure = false; 174 175 if (version_compare($row['server_version'], '9.5', '<')) { 176 $row = DBfetch(DBselect('SHOW ssl')); 177 $is_secure = ($row && $row['ssl'] === 'on'); 178 } 179 else { 180 $is_secure = (bool) DBfetch(DBselect('SELECT datname, usename, ssl, client_addr, cipher FROM pg_stat_ssl'. 181 ' JOIN pg_stat_activity ON pg_stat_ssl.pid=pg_stat_activity.pid'. 182 ' AND pg_stat_activity.usename='.zbx_dbstr($this->user))); 183 } 184 185 if (!$is_secure) { 186 $this->setError('Error connecting to database. Connection is not secure.'); 187 } 188 189 return $is_secure; 190 } 191 192 /** 193 * Create connection to database server. 194 * 195 * @param string $host Host name. 196 * @param string $port Port. 197 * @param string $user User name. 198 * @param string $password Password. 199 * @param string $dbname Database name. 200 * @param string $schema DB schema. 201 * 202 * @param 203 * @return resource|null 204 */ 205 public function connect($host, $port, $user, $password, $dbname, $schema) { 206 $this->user = $user; 207 $this->dbname = $dbname; 208 $this->schema = ($schema) ? $schema : 'public'; 209 $params = compact(['host', 'port', 'user', 'password', 'dbname']); 210 211 if ($this->tls_encryption && (bool) $this->tls_ca_file) { 212 $params += [ 213 'sslmode' => $this->tls_verify_host ? 'verify-full' : 'verify-ca', 214 'sslkey' => $this->tls_key_file, 215 'sslcert' => $this->tls_cert_file, 216 'sslrootcert' => $this->tls_ca_file 217 ]; 218 } 219 220 $conn_string = ''; 221 222 foreach ($params as $key => $param) { 223 $conn_string .= ((bool) $param) ? $key.'=\''.pg_connect_escape($param).'\' ' : ''; 224 } 225 226 $resource = @pg_connect($conn_string); 227 228 if (!$resource) { 229 $this->setError('Error connecting to database.'); 230 return null; 231 } 232 233 return $resource; 234 } 235 236 /** 237 * Initialize database connection. 238 * 239 * @return bool 240 */ 241 public function init() { 242 $schema_set = DBexecute('SET search_path='.zbx_dbstr($this->schema), true); 243 244 if(!$schema_set) { 245 $this->setError(pg_last_error()); 246 return false; 247 } 248 249 $pgsql_version = pg_parameter_status('server_version'); 250 251 if ($pgsql_version !== false && (int) $pgsql_version >= 9) { 252 // change the output format for values of type bytea from hex (the default) to escape 253 DBexecute('SET bytea_output=escape'); 254 } 255 256 return true; 257 } 258 259 /** 260 * Check if tables have compressed data. 261 * 262 * @static 263 * 264 * @param array $tables Tables list. 265 * 266 * @return bool 267 */ 268 public static function isCompressed(array $tables): bool { 269 // Compression is available for TimescaleDB 1.5 and greater. 270 $config = select_config(); 271 if ($config['db_extension'] != ZBX_DB_EXTENSION_TIMESCALEDB || $config['compression_availability'] != 1) { 272 return false; 273 } 274 275 $timescale_v1 = DBfetch(DBselect( 276 'SELECT NULL FROM pg_catalog.pg_class c'. 277 ' JOIN pg_catalog.pg_namespace n'. 278 ' ON n.oid=c.relnamespace'. 279 ' WHERE n.nspname='.zbx_dbstr('timescaledb_information'). 280 ' AND c.relname='.zbx_dbstr('compressed_hypertable_stats') 281 )); 282 283 if ($timescale_v1) { 284 $result = DBfetch(DBselect('SELECT coalesce(sum(number_compressed_chunks),0) chunks'. 285 ' FROM timescaledb_information.compressed_hypertable_stats'. 286 ' WHERE number_compressed_chunks != 0 AND '.dbConditionString('hypertable_name::text', $tables) 287 )); 288 289 return (bool) $result['chunks']; 290 } 291 292 $query = implode(' UNION ', array_map(function ($table) { 293 return 'SELECT number_compressed_chunks chunks'. 294 ' FROM hypertable_compression_stats('.zbx_dbstr($table).')'. 295 ' WHERE number_compressed_chunks != 0'; 296 }, $tables)); 297 298 $result = DBfetch(DBselect($query)); 299 300 return (bool) $result['chunks']; 301 } 302} 303