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