Namespaces

Classes

Interfaces

1: <?php
2: declare(strict_types=1);
3: /**
4: * +------------------------------------------------------------+
5: * | apnscp |
6: * +------------------------------------------------------------+
7: * | Copyright (c) Apis Networks |
8: * +------------------------------------------------------------+
9: * | Licensed under Artistic License 2.0 |
10: * +------------------------------------------------------------+
11: * | Author: Matt Saladna (msaladna@apisnetworks.com) |
12: * +------------------------------------------------------------+
13: */
14:
15: use Module\Support\Sql;
16:
17: /**
18: * PostgreSQL operations
19: *
20: * @package core
21: */
22: class Pgsql_Module extends Sql
23: {
24: const DEPENDENCY_MAP = [
25: 'siteinfo',
26: 'diskquota'
27: ];
28: // @link https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
29: const IDENTIFIER_MAXLEN = 63;
30:
31: const PG_TEMP_PASSWORD = '23f!eoj3';
32: const PGSQL_DATADIR = '/var/lib/pgsql';
33:
34: // maximum number of simultaneous connections to a given DB
35: // higher increases the risk of monopolization
36: // used by PostgreSQL
37: const PER_DATABASE_CONNECTION_LIMIT = PGSQL_DATABASE_CONCURRENCY_LIMIT ?: DATABASE_CONCURRENCY_LIMIT;
38:
39: /* @ignore */
40: const MASTER_USER = 'root';
41:
42: protected const PGSQL_PERMITTED_EXTENSIONS = ['pg_trgm', 'hstore', 'pgcrypto', 'postgis', 'unaccent'];
43:
44: /**
45: * {{{ void __construct(void)
46: *
47: * @ignore
48: */
49: public function __construct()
50: {
51: parent::__construct();
52: $this->exportedFunctions = array(
53: '*' => PRIVILEGE_SITE,
54: 'version' => PRIVILEGE_ALL,
55: 'get_elevated_password_backend' => PRIVILEGE_ALL | PRIVILEGE_SERVER_EXEC,
56: 'prep_tablespace_backend' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
57: 'vacuum_backend' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
58: 'get_uptime' => PRIVILEGE_ALL,
59: 'get_username' => PRIVILEGE_ALL,
60: 'get_password' => PRIVILEGE_ALL,
61: 'set_password' => PRIVILEGE_ALL,
62: 'enabled' => PRIVILEGE_SITE | PRIVILEGE_USER,
63: 'get_prefix' => PRIVILEGE_SITE | PRIVILEGE_USER,
64:
65: 'export_pipe_real' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
66: 'resolve_site_from_database' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
67:
68: // necessary for DB backup routines
69: 'get_database_size' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
70: 'database_exists' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
71: 'site_from_tablespace' => PRIVILEGE_ADMIN
72: );
73: }
74:
75: public function __destruct()
76: {
77: foreach ($this->_tempUsers as $user) {
78: if (!$this->user_exists($user)) {
79: continue;
80: }
81: $this->_delete_temp_user($user);
82: }
83: }
84:
85: /**
86: * Verify if PostgreSQL user exists
87: *
88: * @param string $user username
89: * @param string $host unused
90: * @return bool
91: * @throws PostgreSQLError
92: */
93: public function user_exists($user, $host = 'localhost')
94: {
95: $db = \PostgreSQL::initialize();
96: $prefix = $this->get_prefix();
97: if (!($this->permission_level & PRIVILEGE_ADMIN) && $user != $this->getServiceValue('pgsql', 'dbaseadmin') &&
98: 0 !== strpos($user, $prefix)
99: ) {
100: $user = $prefix . $user;
101: }
102: $q = $db->query_params('SELECT 1 FROM pg_authid WHERE rolname = $1', array($db->escape_string($user)));
103:
104: return !$q || $db->num_rows() > 0;
105: }
106:
107: public function get_prefix()
108: {
109: return $this->getServiceValue('pgsql', 'dbaseprefix');
110: }
111:
112: private function _delete_temp_user($user)
113: {
114: if (!$this->delete_user($user)) {
115: return false;
116: }
117:
118: $idx = array_search($user, $this->_tempUsers);
119: if ($idx !== false) {
120: unset($this->_tempUsers[$idx]);
121: }
122:
123: return true;
124: }
125:
126: // {{{ connect_mysql_root()
127:
128: /**
129: * bool delete_user(string[, bool = false])
130: * Delete a PostgreSQL user
131: *
132: * @param string $user username
133: * @param bool $cascade casecade delete
134: * @return bool
135: */
136: public function delete_user($user, $cascade = false)
137: {
138: if ($user == $this->username && !Util_Account_Hooks::is_mode('delete')) {
139: return error('Cannot remove main user');
140: }
141: if (!$this->user_exists($user)) {
142: return error("db user `$user' not found");
143: }
144:
145: $prefix = $this->get_prefix();
146: if ($user !== $this->getConfig('pgsql', 'dbaseadmin') && strncmp($user, $prefix, strlen($prefix))) {
147: $user = $prefix . $user;
148: }
149: $tblspace = $this->get_tablespace();
150: $pghandler = \PostgreSQL::initialize();
151: Opcenter\Database\PostgreSQL::terminateUser($user);
152: if (function_exists('pg_escape_literal')) {
153: $usersafe = pg_escape_identifier($pghandler->getHandler(), $user);
154: } else {
155: $usersafe = '"' . pg_escape_string($pghandler->getHandler(), $user) . '"';
156: }
157: $pghandler->query('REVOKE ALL ON TABLESPACE ' . $tblspace . ' FROM ' . $usersafe . '');
158: $pghandler->query('DROP ROLE ' . $usersafe);
159:
160: if ($pghandler->error) {
161: return new PostgreSQLError('Invalid query, ' . $pghandler->error);
162: }
163:
164: return true;
165:
166: }
167:
168: // }}}
169:
170: /**
171: * Get tablespace name for domain
172: *
173: * @return null|string
174: */
175: public function get_tablespace(): ?string
176: {
177: return $this->getServiceValue(
178: 'pgsql',
179: 'tablespace',
180: \Opcenter\Database\PostgreSQL::getTablespaceFromUser($this->username)
181: );
182: }
183:
184: /**
185: * Get site from tablespace
186: *
187: * @param string $tblspace
188: * @return null|string
189: */
190: public function site_from_tablespace(string $tblspace): ?string
191: {
192: $db = \PostgreSQL::pdo();
193: $query = "SELECT rolname FROM pg_authid JOIN pg_tablespace ON (pg_authid.oid = pg_tablespace.spcowner) WHERE spcname = " . $db->quote($tblspace);
194: $rs = $db->query($query);
195: if (!$rs) {
196: return null;
197: }
198: $user = $rs->fetchObject()->rolname;
199: if ($siteid = \Auth::get_site_id_from_admin($user)) {
200: return 'site' . $siteid;
201: }
202: // not strictly enforced yet...
203: $map = \Opcenter\Map::load('pgsql.usermap');
204:
205: return $map->fetch($user) ?: null;
206: }
207:
208: public function set_username($user)
209: {
210: if (!IS_CLI) {
211: return $this->query('pgsql_set_username', $user);
212: }
213:
214: return $this->_set_pg_param('username', $user);
215:
216: }
217:
218: private function _set_pg_param($param, $val)
219: {
220: $pwd = $this->user_getpwnam();
221: $file = $this->domain_fs_path() . $pwd['home'] . '/.pgpass';
222:
223: if (!file_exists($file)) {
224: \Opcenter\Filesystem::touch($file, $this->user_id, $this->group_id, 0600);
225: }
226:
227: return \Opcenter\Database\PostgreSQL::setUserConfigurationField($file, $param, $val);
228:
229: }
230:
231: public function get_password($user = null)
232: {
233: if (DEMO_ADMIN_LOCK && ($this->permission_level & PRIVILEGE_ADMIN) && posix_getuid()) {
234: return error("Demo may not alter ~/.pgpass");
235: }
236:
237: if (!IS_CLI) {
238: return $this->query('pgsql_get_password', $user);
239: }
240: if (!$user) {
241: $user = $this->username;
242: }
243: $pwd = $this->user_getpwnam($user);
244: if (!$pwd) {
245: return error('unknown system user `%s\'', $user);
246: }
247: $file = $this->domain_fs_path() . $pwd['home'] . '/.pgpass';
248: if (!file_exists($file)) {
249: return false;
250: }
251:
252: return \Opcenter\Database\PostgreSQL::getUserConfiguration($file)['password'];
253: }
254:
255: public function get_elevated_password_backend()
256: {
257: return Opcenter\Database\MySQL::rootPassword();
258: }
259:
260: /**
261: * Change account database prefix
262: *
263: * @param string $prefix
264: * @return bool
265: */
266: public function change_prefix($prefix)
267: {
268: return error('use sql_change_prefix');
269: }
270:
271: public function get_sql_prefix()
272: {
273: deprecated('use pgsql_get_prefix');
274:
275: return $this->get_prefix();
276: }
277:
278: /**
279: * bool service_enabled (string)
280: *
281: * Checks to see if a service is enabled
282: *
283: * @deprecated @see enabled()
284: * @return bool
285: */
286: public function service_enabled()
287: {
288: deprecated('use enabled()');
289:
290: return $this->enabled();
291: }
292:
293: /**
294: * MySQL/PostgreSQL service enabled on account
295: *
296: * Checks to see if either MySQL or PostgreSQL is enabled on an account
297: *
298: * @return bool
299: */
300: public function enabled()
301: {
302: return parent::svc_enabled('pgsql');
303: }
304:
305: /**
306: * bool create_database (string)
307: *
308: * @param string $db
309: * @return bool creation succeeded
310: */
311: public function create_database($db)
312: {
313: if (!$this->enabled()) {
314: return error("%(service)s is disabled", ['service' => 'PostgreSQL']);
315: }
316:
317: if (!preg_match(\Regex::SQL_DATABASE, $db)) {
318: return error("invalid database name `%s'", $db);
319: }
320: if ($this->database_exists($db)) {
321: return error("database `$db' exists");
322: }
323: $prefix = $this->get_prefix();
324:
325: // db name passed without prefix
326: if (0 !== strpos($db, $prefix)) {
327: $db = $prefix . $db;
328: }
329:
330: if (null !== ($limit = $this->getConfig('pgsql', 'dbasenum', null)) && $limit >= 0) {
331: $count = \count($this->list_databases());
332: if ($count >= $limit) {
333: return error("Database limit `%d' reached - cannot create additional databases", $limit);
334: }
335: }
336:
337: if (!$this->prep_tablespace()) {
338: return false;
339: }
340: // optional template
341: $pghandler = \PostgreSQL::initialize();
342: $pghandler->query('CREATE DATABASE ' . pg_escape_identifier($pghandler->getHandler(), $db) . ' WITH OWNER = ' .
343: pg_escape_identifier($pghandler->getHandler(), $this->username) . ' TABLESPACE = ' .
344: pg_escape_identifier($pghandler->getHandler(), $this->get_tablespace()) . ' CONNECTION LIMIT = ' .
345: static::PER_DATABASE_CONNECTION_LIMIT);
346: if ($pghandler->error) {
347: return error('error while creating database: %s', $pghandler->error);
348: }
349:
350: return info("created database `%s'", $db);
351: }
352:
353: /**
354: * Query PostgreSQL system table for existence of database
355: *
356: * @param string $db database name
357: * @return bool
358: */
359: public function database_exists($db)
360: {
361: if (!($this->permission_level & PRIVILEGE_ADMIN)) {
362: $prefix = $this->get_prefix();
363: if (0 !== strpos($db, $prefix)) {
364: $db = $prefix . $db;
365: }
366: }
367: $pgdb = \PostgreSQL::initialize();
368: $q = $pgdb->query_params('SELECT 1 FROM pg_database WHERE datname = $1', array($pgdb->escape_string($db)));
369:
370: return !$q || $pgdb->num_rows() > 0;
371: }
372:
373: /**
374: * void prep_tablespace ()
375: * Checks to see if tablespace exists, if not, creates it
376: *
377: * @private
378: */
379: public function prep_tablespace()
380: {
381:
382: if (\Opcenter\Database\PostgreSQL::getTablespaceFromUser($this->username)) {
383: return true;
384: }
385: $path = $this->domain_fs_path() . self::PGSQL_DATADIR;
386: if (!file_exists($path)) {
387: $this->query('pgsql_prep_tablespace_backend', $path);
388: }
389:
390: return \Opcenter\Database\PostgreSQL::initializeTablespace($this->site, $path, $this->username);
391: }
392:
393: /**
394: * Enable extension on database
395: *
396: * @param string $db
397: * @param string $extension
398: * @return bool
399: */
400: public function add_extension(string $db, string $extension): bool
401: {
402: if (!IS_CLI) {
403: return $this->query('pgsql_add_extension', $db, $extension);
404: }
405:
406: $extensions = $this->_getPermittedExtensions();
407: if (!in_array($extension, $extensions, true)) {
408: return error("extension `%s' unrecognized or disallowed usage", $extension);
409: }
410:
411: $prefix = $this->get_prefix();
412: if (0 !== strpos($db, $prefix)) {
413: $db = $prefix . $db;
414: }
415:
416: $dbs = $this->list_databases();
417: if (!in_array($db, $dbs, true)) {
418: return error("database `%s' unknown", $db);
419: }
420:
421: $cmd = 'CREATE EXTENSION IF NOT EXISTS ' . $extension . ' WITH SCHEMA public';
422: $proc = Util_Process_Safe::exec('psql -c %s %s', $cmd, $db);
423: if (!$proc['success']) {
424: return error('extension creation failed - %s', $proc['stderr']);
425: }
426:
427: return $proc['success'];
428: }
429:
430: /**
431: * Extensions permitted on database
432: *
433: * @return string[]
434: */
435: protected function _getPermittedExtensions()
436: {
437: return static::PGSQL_PERMITTED_EXTENSIONS;
438: }
439:
440: /**
441: * array list_databases ()
442: * Queries the db table in the pgsql database for applicable grants
443: *
444: * @return array list of databases
445: */
446: public function list_databases(): array
447: {
448:
449: $prefix = $this->get_prefix();
450: if (!$prefix) {
451: // compatibility with DTSS behavior in PostgreSQL
452: if (!$this->enabled()) {
453: return [];
454: }
455: if ($this->enabled()) {
456: report('Prefixless site - bug');
457: }
458:
459: return [];
460: }
461: $pgdb = \PostgreSQL::initialize();
462: $pgdb->query("SELECT datname FROM pg_database WHERE datname LIKE '"
463: . str_replace(array('-', '_'), array('', '\_'), $prefix) . "%' OR datdba = "
464: . "(SELECT oid FROM pg_roles WHERE rolname = '" . $this->username . "')");
465: $dbs = array();
466: while ($row = $pgdb->fetch_object()) {
467: $dbs[] = $row->datname;
468: }
469:
470: return $dbs;
471: }
472:
473: /**
474: * Set database owner
475: *
476: * @param string $db
477: * @param string $owner
478: * @return bool
479: */
480: public function set_owner(string $db, string $owner): bool
481: {
482: $pgdb = \PostgreSQL::initialize();
483:
484: $db = $pgdb->escape_string($this->canonicalize($db));
485: $users = $this->list_users();
486:
487: if (!isset($users[$owner])) {
488: $tmp = $this->canonicalize($owner);
489: if (!isset($users[$tmp])) {
490: return error("Unknown pgsql user `%s'", $owner);
491: }
492: $owner = $tmp;
493: }
494: $dbs = $this->list_databases();
495: if (!in_array($db, $dbs, true)) {
496: return error("Unknown database `%s'", $db);
497: }
498:
499: $q = 'ALTER DATABASE ' . pg_escape_identifier($pgdb->getHandler(), $db) . ' OWNER TO ' . pg_escape_identifier($pgdb->getHandler(), $owner);
500: if (false === $pgdb->query($q)) {
501: return error("Failed to change owner to `%s'", $owner);
502: }
503:
504: if ($this->version() >= 150000) {
505: // v15 revokes on public from PUBLIC
506: $pgdb = \PostgreSQL::stub();
507: $pgdb->connect(null, null, null, $db);
508: $pgdb->query(
509: "GRANT ALL ON DATABASE " . pg_escape_identifier($pgdb->getHandler(), $db) . " TO " .
510: pg_escape_identifier($pgdb->getHandler(), $owner)
511: );
512: $q = 'GRANT ALL ON SCHEMA public TO ' . pg_escape_identifier($pgdb->getHandler(), $owner);
513: $pgdb->query($q);
514: }
515:
516: return true;
517: }
518:
519: /**
520: * Append prefix if necessary
521: *
522: * @param string $name
523: * @return string
524: */
525: private function canonicalize(string $name): string
526: {
527: $prefix = $this->get_prefix();
528: if (0 !== strpos($name, $prefix)) {
529: $name = $prefix . $name;
530: }
531:
532: return $name;
533: }
534:
535: /**
536: * array list_users ()
537: * Lists all created users for PostgreSQL
538: *
539: * @return array
540: */
541: public function list_users(): array
542: {
543: // meta is corrupted, let's bail
544: if (!$prefix = $this->get_prefix()) {
545: return [];
546: }
547:
548: $pgdb = \PostgreSQL::initialize();
549: $q = $pgdb->query("SELECT rolname, rolpassword, rolconnlimit FROM pg_authid WHERE rolname = '"
550: . $this->username . "' OR rolname LIKE '" . str_replace(array('-', '_'), array('', '\_'),
551: $prefix) . "%' ORDER BY rolname");
552: $users = array();
553: while ($row = $pgdb->fetch_object()) {
554: $users[$row->rolname] = array(
555: 'max_connections' => (int)$row->rolconnlimit,
556: 'password' => $row->rolpassword
557: );
558: }
559:
560: return $users;
561: }
562:
563: /**
564: * Assign privileges for database to user
565: *
566: * @param string $user
567: * @param string $host
568: * @param string $db
569: * @param array $privileges
570: * @return bool
571: */
572: public function set_privileges(string $user, string $host, string $db, array $privileges): bool
573: {
574: deprecated_func('Use change_owner() to change database owner');
575: return $this->change_owner($db, $user);
576: }
577:
578: /**
579: * Change database owner
580: *
581: * @param string $db
582: * @param string $newowner
583: * @return bool
584: * @throws PostgreSQLError
585: */
586: public function change_owner(string $db, string $newowner): bool
587: {
588: $prefix = $this->get_prefix();
589:
590: if (!$this->user_exists($newowner)) {
591: if (0 !== strpos($newowner, $prefix)) {
592: return $this->change_owner($db, $prefix . $newowner);
593: }
594: return error("User `%s' does not exist", $newowner);
595: }
596:
597: if (!$this->database_exists($db)) {
598: if (0 !== strpos($db, $prefix)) {
599: return $this->change_owner($prefix . $db, $newowner);
600: }
601: return error("Database `%s' does not exist", $db);
602: }
603:
604: $pgdb = \PostgreSQL::initialize();
605:
606: $vendor = \Opcenter\Database\PostgreSQL::vendor();
607: $res = \PostgreSQL::initialize()->query($vendor->changeDatabaseOwner($db, $newowner));
608:
609: return $res && $res->affected_rows() > 0;
610: }
611:
612: /**
613: * Get owner from database
614: *
615: * @param string $database
616: * @return string|null
617: */
618: public function get_owner(string $database): ?string
619: {
620: if (!$this->database_exists($database)) {
621: $prefix = $this->get_prefix();
622: if (0 !== strpos($database, $prefix)) {
623: return $this->get_owner($prefix . $database);
624: }
625: }
626: if (!($tblspace = $this->get_tablespace())) {
627: report('Failed tablespace inquiry for %s on %s', $database, $this->site);
628: return null;
629: }
630: $pgdb = \PostgreSQL::initialize();
631: $vendor = \Opcenter\Database\PostgreSQL::vendor();
632:
633: $res = \PostgreSQL::initialize()->query($vendor->ownerFromDatabase($database, $tblspace));
634: if (!$res) {
635: return null;
636: }
637:
638: return array_get($res->fetch_assoc(), 'owner', null);
639: }
640:
641: /**
642: * void prep_tablespace_backend ()
643: * {@link prep_tablespace}
644: *
645: * @param string $path tablespace path
646: * @return bool
647: */
648: public function prep_tablespace_backend($location)
649: {
650: if (!is_dir($location)) {
651: mkdir($location) || fatal("failed to create pgsql data directory `%s'", $location);
652: }
653: chown($location, 'postgres');
654: chgrp($location, (int)\Session::get('group_id', posix_getgrnam('postgres')));
655: chmod($location, 02750);
656: }
657:
658: /**
659: * bool add_user_permissions (string, string, string, array)
660: * Add/removes privileges for a user to a table, any value listed as
661: * false or not supplied as an array key will revoke the privilege
662: *
663: * @param string $user
664: * @param string $db
665: * @param array $opts
666: */
667: public function add_user_permissions($user, $db, array $opts)
668: {
669: return error('Function not implemented in PostgreSQL');
670: }
671:
672: public function delete_user_permissions($user, $db)
673: {
674: return error('Function not implemented in PostgreSQL');
675: }
676:
677: /**
678: * void get_user_permissions(string, string)
679: * Function not implemented in PostgreSQL
680: *
681: * @return void
682: */
683: public function get_user_permissions($user, $db)
684: {
685: return error('Function not implemented in PostgreSQL');
686: }
687:
688: /**
689: * bool delete_database(string)
690: * Drops the database and revokes all permssions
691: *
692: * @param string $db
693: * @return bool drop succeeded
694: */
695: public function delete_database($db)
696: {
697: $pgdb = \PostgreSQL::initialize();
698: $prefix = $this->get_prefix();
699: if (!$prefix) {
700: report('Prefixless site - bug');
701:
702: return false;
703: }
704: if (0 !== strpos($db, $prefix)) {
705: $db = $prefix . $db;
706: }
707: $db = $pgdb->escape_string($db);
708: if (!in_array($db, $this->list_databases())) {
709: return error("Unknown database `%s'", $db);
710: }
711: $resp = \Opcenter\Database\PostgreSQL::dropDatabase($db);
712:
713: if (in_array($db, $this->list_backups(), true)) {
714: $this->delete_backup($db);
715: }
716:
717: if (!$resp) {
718: return error('Error while dropping database, ' . $pgdb->error);
719: }
720:
721: return true;
722: }
723:
724: /**
725: * Remove PostgreSQL Backup
726: *
727: * @param string $db
728: * @return bool
729: */
730: public function delete_backup($db)
731: {
732: return parent::delete_backup_real('pgsql', $db);
733: }
734:
735: /**
736: * Modify use password and connection limit
737: *
738: * NOTE: Not implemented with PostgreSQL, owner of database automatically
739: * receives grants. Varying degrees of grants impact the usability of
740: * this function, i.e. common grants [SELECT, INSERT, UPDATE, DELETE] exist
741: * solely on the table level, while [CREATE, TEMP] exist on the database
742: * level
743: *
744: * @param string $user user
745: * @param string $password
746: * @param int $maxconn connection limit
747: * @return bool query succeeded
748: */
749: public function edit_user($user, $password, $maxconn = null)
750: {
751: $prefix = str_replace('-', '', $this->get_prefix());
752: if ($user != $this->getServiceValue('pgsql', 'dbaseadmin') &&
753: strncmp($user, $prefix, strlen($prefix))
754: ) {
755: $user = $prefix . $user;
756: }
757: if (is_int($maxconn) && ($maxconn < 1)) {
758: $maxconn = self::DEFAULT_CONCURRENCY_LIMIT;
759: }
760: if (!$password && !$maxconn) {
761: return warn("no action taken for `$user'");
762: }
763: if ($password && strlen($password) < self::MIN_PASSWORD_LENGTH) {
764: return error('pgsql password must be at least %d characters long', self::MIN_PASSWORD_LENGTH);
765: }
766: $pgdb = \PostgreSQL::pdo();
767: $params = [
768: ':name' => $user,
769: ':password' => $password,
770: ':connlimit' => $maxconn
771: ];
772:
773: if (!$password && is_int($maxconn)) {
774: $query = 'UPDATE pg_authid SET rolconnlimit = :connlimit WHERE rolname = :name';
775: unset($params[':password']);
776: } else if ($password && is_int($maxconn)) {
777: $query = 'UPDATE pg_authid SET rolpassword = :password, rolconnlimit = :connlimit WHERE rolname = :name';
778: } else if ($password && !is_int($maxconn)) {
779: $query = 'UPDATE pg_authid SET rolpassword = :password WHERE rolname = :name';
780: unset($params[':connlimit']);
781: }
782:
783: $stmt = $pgdb->prepare($query);
784:
785: if (!$stmt->execute($params)) {
786: return error("Failed to edit user `%s': %s",
787: $user,
788: array_get($stmt->errorInfo(), 2, 'UNKNOWN')
789: );
790: }
791:
792: // @TODO v7.5
793: if ($password) {
794: $pgdb->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
795: $stmt = $pgdb->prepare('UPDATE pg_authid SET rolpassword = CONCAT(\'md5\', md5(CONCAT(:password, :name))) WHERE rolname = :name;');
796: $ret = $stmt->execute(array_except($params, [':connlimit']));
797: if (!$ret) {
798: return error("failed to update postgresql password for user `%s'", $user);
799: }
800:
801: if ($user == $this->get_username()) {
802: $this->set_password($password);
803: }
804: }
805:
806: return true;
807: }
808:
809: public function get_username()
810: {
811: if (!IS_CLI) {
812: return $this->query('pgsql_get_username');
813: }
814:
815: $user = $this->username;
816: $pwd = $this->user_getpwnam($user);
817: if (!$pwd) {
818: return error('unknown system user `%s\'', $user);
819: }
820: $file = $this->domain_fs_path() . $pwd['home'] . '/.pgpass';
821: if (!file_exists($file)) {
822: return $this->username;
823: }
824: $contents = file_get_contents($file);
825: if (!preg_match(Regex::SQL_PGPASS, $contents, $matches)) {
826: return $user;
827: }
828:
829: if (!$matches['username'] || $matches['username'] === '*') {
830: return $this->username;
831: }
832:
833: return $matches['username'];
834: }
835:
836: public function set_password($password)
837: {
838: if (DEMO_ADMIN_LOCK && posix_getuid()) {
839: return error("Demo may not alter ~/.pgpass");
840: }
841:
842: if (!IS_CLI) {
843: return $this->query('pgsql_set_password', $password);
844: }
845:
846: return $this->_set_pg_param('password', $password);
847:
848: }
849:
850: /**
851: * string pg_vacuum_db (string)
852: * Vacuums a database
853: *
854: * @return string vacuum output
855: */
856: public function vacuum($db)
857: {
858: $pgdb = \PostgreSQL::initialize();
859: $db = $pgdb->escape_string($db);
860: $prefix = $this->get_prefix();
861:
862: // db name passed without prefix
863: if (0 !== strpos($db, $prefix)) {
864: $db = $prefix . $db;
865: }
866: $q = 'SELECT 1 FROM pg_database WHERE datname = $1 ' .
867: "AND datdba = (SELECT oid FROM pg_roles WHERE rolname = '" . $this->username . "')";
868: $pgdb->query_params($q, array($db));
869: if ($pgdb->num_rows() < 1) {
870: return error("Database `$db' not owned by main user");
871: }
872:
873: return $this->query('pgsql_vacuum_backend', $db);
874: }
875:
876: public function vacuum_backend($db)
877: {
878: $status = Util_Process::exec('vacuumdb -zfq --dbname=' . escapeshellarg($db));
879: if ($status['error'] instanceof Exception) {
880: return error($status['error']);
881: }
882:
883: return $status['success'];
884: }
885:
886: public function truncate_database($db)
887: {
888:
889: return $this->_empty_truncate_wrapper($db, 'truncate');
890: }
891:
892: private function _empty_truncate_wrapper($db, $mode)
893: {
894: if ($mode != 'truncate' && $mode != 'empty') {
895: return error("unknown mode `%s'", $mode);
896: }
897: if ($mode == 'empty') {
898: // semantically more correct
899: $mode = 'drop';
900: }
901:
902: $prefix = $this->get_prefix();
903: if (strncmp($db, $prefix, strlen($prefix))) {
904: $db = $prefix . $db;
905: }
906:
907: if (!$this->database_exists($db)) {
908: return error("unknown database, `%s'", $db);
909: }
910:
911: $user = $this->_create_temp_user($db);
912: if (!$user) {
913: return error("failed to %s db `%s'", $mode, $db);
914: }
915: $dsn = 'host=localhost dbname=' . $db . ' user=' . $user . ' password=' . self::PG_TEMP_PASSWORD;
916: $sqldb = pg_connect($dsn);
917: if (!$sqldb) {
918: $this->_delete_temp_user($user);
919:
920: return error("failed to %s db `%s', db connection failed", $mode, $db);
921: }
922: // via psql -E, unlikely to
923: $q = 'SELECT n.nspname as "schema", ' .
924: 'c.relname as "name", ' .
925: 'r.rolname as "owner"' .
926: 'FROM pg_catalog.pg_class c ' .
927: 'JOIN pg_catalog.pg_roles r ON r.oid = c.relowner ' .
928: 'LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' .
929: "WHERE c.relkind IN ('r','') " .
930: "AND n.nspname <> 'pg_catalog' " .
931: "AND n.nspname !~ '^pg_toast' " .
932: 'AND pg_catalog.pg_table_is_visible(c.oid) ' .
933: 'ORDER BY 1,2;';
934: $rs = \pg_query($sqldb, $q);
935: $pgver = $this->version();
936: // available in 8.4
937: $identity = ($mode !== 'drop' && $pgver >= 80400) ? 'RESTART IDENTITY' : '';
938: while (false !== ($res = pg_fetch_object($rs))) {
939: if (function_exists('pg_escape_identifier')) {
940: $tablesafe = \pg_escape_identifier($sqldb, $res->name);
941: } else {
942: $tablesafe = '"' . \pg_escape_string($sqldb, $res->name) . '"';
943: }
944: $q = strtoupper($mode) . ' TABLE ' . $tablesafe . ' ' . $identity . ' CASCADE';
945: if (!($res = \pg_query($sqldb, $q))) {
946: warn("failed to %s table `%s': %s", $mode, $res->name, pg_errormessage($sqldb));
947: }
948: }
949: $this->_delete_temp_user($user);
950:
951: return true;
952: }
953:
954: /***************** STATISTICS *******************/
955:
956: private function _create_temp_user($db)
957: {
958:
959: $prefix = $this->get_prefix();
960: $maxlen = self::IDENTIFIER_MAXLEN - strlen($prefix);
961: if ($maxlen < 1) {
962: warn('temp pgsql user exceeds field length');
963: return false;
964: }
965: $chars = array(
966: 'a',
967: 'b',
968: 'c',
969: 'd',
970: 'e',
971: 'f',
972: '0',
973: '1',
974: '2',
975: '3',
976: '4',
977: '5',
978: '6',
979: '7',
980: '8',
981: '9'
982: );
983: $maxlen = min(6, $maxlen);
984:
985: $user = $prefix;
986: for ($i = 0; $i < $maxlen; $i++) {
987: $n = random_int(0, 15);
988: $user .= $chars[$n];
989: }
990:
991: if ($this->user_exists($user)) {
992: return error('cannot create temp pgsql user');
993: }
994:
995: if (!$this->add_user($user, self::PG_TEMP_PASSWORD, 1)) {
996: return error('unable to create role on pgsql database %s', $db);
997: }
998:
999: $sqldb = \PostgreSQL::initialize();
1000: $q = "SELECT 'GRANT SELECT ON ' || relname || ' TO \"$user\";'
1001: FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
1002: WHERE nspname = 'public' AND relkind IN ('r', 'v');";
1003: $rs = $sqldb->query($q);
1004: if (!$rs->fetch_object()) {
1005: return error("cannot create temp pgsql user `%s'", $user);
1006: }
1007: $sqldb->query('GRANT "' . $this->username . '" TO "' . $user . '"');
1008: $this->_register_temp_user($user);
1009:
1010: return $user;
1011:
1012: }
1013:
1014: /**
1015: * bool add_user(string, string[, int])
1016: */
1017: public function add_user($user, $password, $maxconn = self::DEFAULT_CONCURRENCY_LIMIT)
1018: {
1019: if (!$this->enabled()) {
1020: return error("%(service)s is disabled", ['service' => 'PostgreSQL']);
1021: }
1022: if (!$user) {
1023: return error('no username specified');
1024: }
1025: $prefix = str_replace('-', '', $this->get_prefix());
1026: if ($user != $this->getServiceValue('pgsql', 'dbaseadmin') &&
1027: 0 !== strpos($user, $prefix)) {
1028: $user = $prefix . $user;
1029: }
1030: if (!$this->enabled()) {
1031: return error('PostgreSQL service not enabled for account.');
1032: } else if ($this->user_exists($user)) {
1033: return error("pg user `$user' exists");
1034: }
1035:
1036: if ($maxconn < 0) {
1037: $maxconn = self::PER_DATABASE_CONNECTION_LIMIT;
1038: }
1039: if (strlen($password) < self::MIN_PASSWORD_LENGTH) {
1040: return error('Password must be at least %d characters', self::MIN_PASSWORD_LENGTH);
1041: } else if ($maxconn < 0) {
1042: return error('Max connections, queries, and updates must be greater than -1');
1043: }
1044: if (!\Opcenter\Database\PostgreSQL::createUser($user, $password)) {
1045: return false;
1046: }
1047: \Opcenter\Database\PostgreSQL::setRole($user, $this->username);
1048: $vendor = \Opcenter\Database\PostgreSQL::vendor();
1049:
1050: return (bool)\PostgreSQL::initialize()->query($vendor->setMaxConnections($user, $maxconn));
1051: }
1052:
1053: public function version(bool $pretty = false): int|string
1054: {
1055: $version = \Opcenter\Database\PostgreSQL::version();
1056: if (!$pretty) {
1057: return $version;
1058: }
1059: $pgver = array();
1060: foreach (array('patch', 'minor', 'major') as $v) {
1061: $pgver[$v] = (int)$version % 100;
1062: $version /= 100;
1063: }
1064:
1065: return $pgver['major'] . '.' . $pgver['minor'] . '.' .
1066: $pgver['patch'];
1067: }
1068:
1069: /**
1070: * Database is empty
1071: *
1072: * @param $db
1073: * @return bool
1074: */
1075: public function empty($db)
1076: {
1077: if (!$this->database_exists($db)) {
1078: return true;
1079: }
1080:
1081: return \count(\Opcenter\Database\PostgreSQL::getTablesFromDatabase($db)) === 0;
1082: }
1083:
1084: public function empty_database($db)
1085: {
1086: return $this->_empty_truncate_wrapper($db, 'empty');
1087: }
1088:
1089: /**
1090: * bool pgsql_import(string, string, string, strin)
1091: */
1092: public function import($db, $file)
1093: {
1094: if (!IS_CLI) {
1095: return $this->query('pgsql_import', $db, $file);
1096: }
1097:
1098: $prefix = $this->get_prefix();
1099: // db name passed without prefix
1100: if (strncmp($db, $prefix, strlen($prefix))) {
1101: $db = $prefix . $db;
1102: }
1103:
1104: $dbs = $this->list_databases();
1105: if (false === array_search($db, $dbs, true)) {
1106: return error("database `%s' does not exist", $db);
1107: }
1108: $unlink = null;
1109: if (false === ($realfile = $this->_preImport($file, $unlink))) {
1110: return false;
1111: }
1112: $user = $this->_create_temp_user($db);
1113: if (!$user) {
1114: return error('import failed - cannot create temp user');
1115: }
1116: $proc = new Util_Process_Safe();
1117: $proc->setEnvironment('PGPASSWORD', self::PG_TEMP_PASSWORD);
1118: $cmd = 'psql -q -h 127.0.0.1 -f %(file)s -U %(user)s %(db)s';
1119: if (\basename($realfile) === 'toc.dat' && file_exists(\dirname($realfile) . '/restore.sql')) {
1120: // via pg_restore
1121: $realfile = \dirname($realfile);
1122: $cmd = 'pg_restore -h 127.0.0.1 -x -O -U %(user)s -d %(db)s %(file)s';
1123: }
1124: $args = array(
1125: 'password' => self::PG_TEMP_PASSWORD,
1126: 'file' => $realfile,
1127: 'user' => $user,
1128: 'db' => $db
1129: );
1130: $proc->setPriority(19);
1131: $status = $proc->run($cmd, $args);
1132: $this->_delete_temp_user($user);
1133: $this->_postImport($unlink);
1134:
1135: if (!$status['success']) {
1136: return error('import failed: %s', $status['error']);
1137: }
1138:
1139: return $status['success'];
1140: }
1141:
1142: public function export($db, $file = null)
1143: {
1144: if (!IS_CLI) {
1145: return $this->query('pgsql_export', $db, $file);
1146: }
1147: if (is_null($file)) {
1148: $file = $db . '.sql';
1149: }
1150:
1151: if ($file[0] !== '/' && $file[0] !== '.' && $file[0] !== '~') {
1152: $path = $this->domain_fs_path() . '/tmp/' . $file;
1153: } else {
1154: $path = $this->file_make_path($file);
1155: }
1156: if (!$path) {
1157: return error("invalid file `%s'", $file);
1158: }
1159:
1160: if (file_exists($path) &&
1161: (filesize($path) > 0 || realpath($path) !== $path || fileowner($path) < USER_MIN_UID))
1162: {
1163: return error('%s: file exists, cannot overwrite', $file);
1164: }
1165:
1166: $file = $this->file_unmake_path($path);
1167: $pdir = dirname($file);
1168: if (!$this->file_exists($pdir) && !$this->file_create_directory($pdir, 0755, true)) {
1169: return error("failed to create parent directory, `%s'", $pdir);
1170: }
1171:
1172: if (!in_array($db, $this->list_databases())) {
1173: return error("invalid database `%s'", $db);
1174: }
1175:
1176: $user = $this->_create_temp_user($db);
1177: if (!$user) {
1178: return error('pgsql export failed - unable to create user');
1179: }
1180:
1181: $fsizelimit = Util_Ulimit::get('fsize');
1182: if ($this->get_database_size($db) > $fsizelimit / self::DB_BIN2TXT_MULT) {
1183: // make sure ulimit accommodates the db dump
1184: Util_Ulimit::set('fsize', 'unlimited');
1185: } else {
1186: $fsizelimit = null;
1187: }
1188: $cmd = 'cd %s && umask 077 && env PGPASSWORD=%s pg_dump -h 127.0.0.1 -U %s -x --file=%s %s';
1189:
1190: $proc = new Util_Process_Safe();
1191: // gid must come first
1192: $proc->setSgid($this->group_id);
1193: $proc->setSuid($this->user_id);
1194: $proc->setPriority(19);
1195: $status = $proc->run($cmd,
1196: sys_get_temp_dir(),
1197: self::PG_TEMP_PASSWORD,
1198: $user,
1199: $path,
1200: $db
1201: );
1202:
1203: if ($user != self::MASTER_USER) {
1204: $this->_delete_temp_user($user);
1205: }
1206: if (!is_null($fsizelimit)) {
1207: Util_Ulimit::set('fsize', $fsizelimit);
1208: }
1209: if (!file_exists($path)) {
1210: return error('export failed: %s', $status['stderr']);
1211: }
1212:
1213: if (!$status['success']) {
1214: return error('export failed: %s', $status['stderr']);
1215: }
1216:
1217: return $this->file_unmake_path($path);
1218: }
1219:
1220: // }}}
1221:
1222: /**
1223: * Get disk space occupied by database
1224: *
1225: * @param string $db database name
1226: * @return int storage in bytes
1227: */
1228: public function get_database_size($db)
1229: {
1230: $pgdb = \PostgreSQL::initialize();
1231: $size = $pgdb->query('SELECT pg_database_size(' . pg_escape_literal($pgdb->getHandler(), $db) . ') as size')->fetch_object();
1232:
1233: return (int)$size->size;
1234: }
1235:
1236: /**
1237: * Clone a database
1238: *
1239: * @param string $from source database
1240: * @param string $to target database
1241: * @return bool
1242: */
1243: public function clone(string $from, string $to): bool
1244: {
1245: if ($this->database_exists($to) && !$this->empty($to)) {
1246: return error("Database `%s' already exists", $to);
1247: }
1248: if (!$this->database_exists($from)) {
1249: return error("Database `%s' does not exist", $from);
1250: }
1251: if (!$this->database_exists($to) && !$this->create_database($to)) {
1252: return false;
1253: }
1254:
1255: $pipe = $this->export_pipe($from);
1256: defer($_, static function () use ($pipe) {
1257: if (file_exists($pipe)) {
1258: unlink($pipe);
1259: }
1260: });
1261:
1262: return $this->import($to, $this->file_unmake_path($pipe));
1263: }
1264:
1265: /**
1266: * Export a PGSQL db to a named pipe for immediate download
1267: *
1268: * @param $db
1269: * @return bool|void
1270: */
1271: public function export_pipe($db)
1272: {
1273: $dbs = $this->list_databases();
1274: if (!\in_array($db, $dbs, true)) {
1275: $originalDb = $db;
1276: $db = $this->get_prefix() . $db;
1277: if (!\in_array($db, $dbs, true)) {
1278: return error('Invalid database %s', $originalDb);
1279: }
1280: }
1281:
1282: $user = $this->_create_temp_user($db);
1283:
1284: return $this->query('pgsql_export_pipe_real', $db, $user);
1285: }
1286:
1287: /**
1288: * Resolve database from site
1289: *
1290: * @param string $db
1291: * @return string|null
1292: */
1293: public function resolve_site_from_database(string $db): ?string
1294: {
1295: $db = strtok($db, '/');
1296: $prefix = strtok($db, '_') . '_';
1297:
1298: if ($this->site) {
1299: return $this->database_exists($db) ? $this->site : null;
1300: }
1301:
1302: if (!preg_match(Regex::SQL_DATABASE, $db)) {
1303: error("Invalid database name");
1304:
1305: return null;
1306: }
1307:
1308: if (1 || $db === $prefix) {
1309: $path = Opcenter\Database\PostgreSQL::tablespaceLocationFromDatabase($db);
1310:
1311: if (!$path || !str_starts_with($path, FILESYSTEM_VIRTBASE)) {
1312: return null;
1313: }
1314: $site = strtok(substr($path, strlen(FILESYSTEM_VIRTBASE)), '/');
1315:
1316: } else {
1317: $site = Opcenter\Map::read(\Opcenter\Database\PostgreSQL::PREFIX_MAP)[$prefix];
1318: }
1319:
1320:
1321: return $site ?: null;
1322: }
1323:
1324: /**
1325: * Export a PGSQL database to a named pipe
1326: *
1327: * Differs from export_pipe in that it may only be called internally
1328: * or from backend, no API access
1329: *
1330: * @param $db
1331: * @param $user if empty use superuser
1332: * @return bool|string|void
1333: */
1334: public function export_pipe_real($db, $user)
1335: {
1336: if (!IS_CLI) {
1337: return $this->query('pgsql_export_pipe_real', $db, $user);
1338: }
1339: // automatically cleaned up on exit()/destruct
1340:
1341: $cmd = '/usr/bin/pg_dump -h 127.0.0.1 -U %s -x --file=%s %s';
1342:
1343: // @XXX potential race condition
1344: $fifo = tempnam($this->domain_fs_path('/tmp'), 'id-' . $this->site);
1345: unlink($fifo);
1346: if (!posix_mkfifo($fifo, 0600)) {
1347: return error('failed to ready pipe for export');
1348: }
1349: chown($fifo, File_Module::UPLOAD_UID);
1350: $proc = new Util_Process_Fork();
1351: $proc->setUser(APNSCP_SYSTEM_USER);
1352:
1353: // lowest priority
1354: $proc->setPriority(19);
1355: $proc->setEnvironment('PGPASSWORD', self::PG_TEMP_PASSWORD);
1356: $status = $proc->run($cmd,
1357: $user,
1358: $fifo,
1359: $db
1360: );
1361:
1362: if (!$status['success'] || !file_exists($fifo)) {
1363: return error('export failed: %s', $status['stderr']);
1364: }
1365: register_shutdown_function(static function () use ($fifo) {
1366: if (file_exists($fifo)) {
1367: unlink($fifo);
1368: }
1369:
1370: });
1371:
1372: return $fifo;
1373: }
1374:
1375: /**
1376: * int get_uptime
1377: *
1378: * @return int time in seconds
1379: */
1380: public function get_uptime(): int
1381: {
1382: $q = $this->psql->query('SELECT pg_postmaster_start_time() as st')->fetch_object();
1383:
1384: return $q->st;
1385: }
1386:
1387:
1388: public function add_backup(
1389: string $db,
1390: string $extension = DATABASE_BACKUP_EXTENSION,
1391: int $span = DATABASE_BACKUP_SPAN,
1392: int $preserve = DATABASE_BACKUP_PRESERVE,
1393: $email = ''
1394: )
1395: {
1396: return parent::add_backup_real('pgsql', $db, $extension, $span, $preserve, $email);
1397: }
1398:
1399: public function edit_backup(
1400: string $db,
1401: string $extension = DATABASE_BACKUP_EXTENSION,
1402: int $span = DATABASE_BACKUP_SPAN,
1403: int $preserve = DATABASE_BACKUP_PRESERVE,
1404: $email = ''
1405: )
1406: {
1407: return parent::edit_backup_real('pgsql', $db, $extension, $span, $preserve, $email);
1408: }
1409:
1410: public function list_backups()
1411: {
1412: return parent::list_backups_real('pgsql');
1413: }
1414:
1415: /**
1416: * Fetch MySQL backup task information
1417: *
1418: * span => (integer) days between backups
1419: * hold => (integer) number of backups to preserve
1420: * next => (integer) unix timestamp of next backup
1421: * ext => (string) extension of backup
1422: * email => (string) notify address after backup
1423: *
1424: * @param string $db database name
1425: * @return array
1426: */
1427: public function get_backup_config($db)
1428: {
1429: return parent::get_backup_config_real('pgsql', $db);
1430: }
1431: }
ApisCP Modules API documentation generated by ApiGen v7.0.0-alpha.6