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