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