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