From bcd9e48a33e6447ba598884002bd5d3dbc8fb419 Mon Sep 17 00:00:00 2001 From: Greg Sabino Mullane Date: Mon, 24 Jul 2006 22:13:24 +0000 Subject: [PATCH] Add quote_ident function. Overhaul setup with proper paranoid quoting. --- includes/DatabasePostgres.php | 111 +++++++++++++++++++++++----------- 1 file changed, 75 insertions(+), 36 deletions(-) diff --git a/includes/DatabasePostgres.php b/includes/DatabasePostgres.php index 2fe2c79a68..602900ec40 100644 --- a/includes/DatabasePostgres.php +++ b/includes/DatabasePostgres.php @@ -92,52 +92,72 @@ class DatabasePostgres extends Database { global $wgDBname, $wgDBuser, $wgDBpass, $wgDBsuperuser, $wgDBmwschema, $wgDBts2schema; print "OK\n"; + $safeuser = $this->quote_ident($wgDBuser); ## Are we connecting as a superuser for the first time? if ($wgDBsuperuser) { + ## Are we really a superuser? Check out our rights + $SQL = "SELECT + CASE WHEN usesuper IS TRUE THEN + CASE WHEN usecreatedb IS TRUE THEN 3 ELSE 1 END + ELSE CASE WHEN usecreatedb IS TRUE THEN 2 ELSE 0 END + END AS rights + FROM pg_catalog.pg_user WHERE usename = " . $this->addQuotes($wgDBsuperuser); + $rows = $this->numRows($res = $this->doQuery($SQL)); + if (!$rows) { + print "
  • ERROR: Could not read permissions for user \"$wgDBsuperuser\"
    • \n"; + dieout(''); + } + $perms = pg_fetch_result($res, 0, 0); + $SQL = "SELECT 1 FROM pg_catalog.pg_user WHERE usename = " . $this->addQuotes($wgDBuser); $rows = $this->numRows($this->doQuery($SQL)); if ($rows) { print "
  • User \"$wgDBuser\" already exists, skipping account creation.
    • "; } else { - ## Can we create users? - $SQL = "SELECT 1 FROM pg_catalog.pg_user WHERE usesuper IS TRUE AND ". - "usename = " . $this->addQuotes($wgDBsuperuser); - $rows = $this->numRows($this->doQuery($SQL)); - if (!$rows) { + if ($perms != 1 and $perms != 3) { print "
  • ERROR: the user \"$wgDBsuperuser\" cannot create other users. "; print 'Please use a different Postgres user.
    • '; dieout(''); } print "
  • Creating user $wgDBuser..."; $safepass = $this->addQuotes($wgDBpass); - $SQL = "CREATE USER \"$wgDBuser\" NOCREATEDB PASSWORD $safepass"; + $SQL = "CREATE USER $safeuser NOCREATEDB PASSWORD $safepass"; $this->doQuery($SQL); print "OK
    • \n"; } ## User now exists, check out the database - $safename = $this->addQuotes($wgDBname); - $SQL = "SELECT 1 FROM pg_catalog.pg_database WHERE datname = $safename"; - $rows = $this->numRows($this->doQuery($SQL)); - if ($rows) { - print "
  • Database \"$wgDBname\" already exists, skipping database creation.
    • "; - } - else { - print "
  • Creating database $wgDBname..."; - $SQL = "CREATE DATABASE \"$wgDBname\" OWNER \"$wgDBuser\" "; - $this->doQuery($SQL); + if ($dbName != $wgDBname) { + $SQL = "SELECT 1 FROM pg_catalog.pg_database WHERE datname = " . $this->addQuotes($wgDBname); + $rows = $this->numRows($this->doQuery($SQL)); + if ($rows) { + print "
  • Database \"$wgDBname\" already exists, skipping database creation.
    • "; + } + else { + if ($perms < 2) { + print "
  • ERROR: the user \"$wgDBsuperuser\" cannot create databases. "; + print 'Please use a different Postgres user.
    • '; + dieout(''); + } + print "
  • Creating database $wgDBname..."; + $safename = $this->quote_ident($wgDBname); + $SQL = "CREATE DATABASE $safename OWNER $safeuser "; + $this->doQuery($SQL); + print "OK
    • \n"; + ## Hopefully tsearch2 and plpgsql are in template1... + } + + ## Reconnect to check out tsearch2 rights for this user + print "
  • Connecting to \"$wgDBname\" as superuser \"$wgDBsuperuser\" to check rights..."; + @$this->mConn = pg_connect("$hstring dbname=$wgDBname user=$user password=$password"); + if ( $this->mConn == false ) { + print "FAILED TO CONNECT!
    • "; + dieout(""); + } print "OK\n"; - ## Hopefully tsearch2 and plpgsql are in template1... } - ## Reconnect to check out tsearch2 rights for this user - print "
  • Connecting to \"$wgDBname\" as superuser \"$wgDBsuperuser\" to check rights..."; - @$this->mConn = pg_connect("$hstring dbname=$wgDBname user=$user password=$password"); - if ( $this->mConn == false ) { - print "FAILED TO CONNECT!
    • "; - dieout(""); - } - print "OK!"; + ## Tsearch2 checks print "
  • Checking that tsearch2 is installed in the database \"$wgDBname\"..."; if (! $this->tableExists("pg_ts_cfg", $wgDBts2schema)) { print "FAILED. tsearch2 must be installed in the database \"$wgDBname\"."; @@ -146,11 +166,26 @@ class DatabasePostgres extends Database { dieout(""); } print "OK
    • \n"; - print "Ensuring that user \"$wgDBuser\" has select rights on the tsearch2 tables..."; + print "
  • Ensuring that user \"$wgDBuser\" has select rights on the tsearch2 tables..."; foreach (array('cfg','cfgmap','dict','parser') as $table) { - $SQL = "GRANT SELECT ON pg_ts_$table TO \"$wgDBuser\""; + $SQL = "GRANT SELECT ON pg_ts_$table TO $safeuser"; $this->doQuery($SQL); } + print "OK
    • \n"; + + + ## Setup the schema for this user if needed + $result = $this->schemaExists($wgDBmwschema); + if (!$result) { + print "
  • Creating schema $wgDBmwschema ..."; + $safeschema = $this->quote_ident($wgDBmwschema); + $result = $this->doQuery("CREATE SCHEMA $safeschema AUTHORIZATION $safeuser"); + if (!$result) { + print "FAILED.
    • \n"; + dieout(""); + } + print "OK\n"; + } $wgDBsuperuser = ''; return true; ## Reconnect as regular user @@ -181,7 +216,7 @@ class DatabasePostgres extends Database { print "OK"; ## Do we have plpgsql installed? - print "
  • Checking for plpgsql ..."; + print "
  • Checking for Pl/Pgsql ..."; $SQL = "SELECT 1 FROM pg_catalog.pg_language WHERE lanname = 'plpgsql'"; $rows = $this->numRows($this->doQuery($SQL)); if ($rows < 1) { @@ -197,9 +232,9 @@ class DatabasePostgres extends Database { $result = $this->doQuery("CREATE SCHEMA $wgDBmwschema"); if (!$result) { print "FAILED.
    • \n"; - return false; + dieout(""); } - print "ok\n"; + print "OK\n"; } else if ($result != $user) { print "
  • Schema \"$wgDBmwschema\" exists but is not owned by \"$user\". Not ideal.
    • \n"; @@ -210,24 +245,24 @@ class DatabasePostgres extends Database { ## Fix up the search paths if needed print "
  • Setting the search path for user \"$user\" ..."; - $path = "$wgDBmwschema"; + $path = $this->quote_ident($wgDBmwschema); if ($wgDBts2schema !== $wgDBmwschema) - $path .= ", $wgDBts2schema"; + $path .= ", ". $this->quote_ident($wgDBts2schema); if ($wgDBmwschema !== 'public' and $wgDBts2schema !== 'public') $path .= ", public"; - $SQL = "ALTER USER $user SET search_path = $path"; + $SQL = "ALTER USER $safeuser SET search_path = $path"; $result = pg_query($this->mConn, $SQL); if (!$result) { print "FAILED.
    • \n"; - return false; + dieout(""); } - print "ok\n"; + print "OK\n"; ## Set for the rest of this session $SQL = "SET search_path = $path"; $result = pg_query($this->mConn, $SQL); if (!$result) { print "
  • Failed to set search_path
    • \n"; - return false; + dieout(""); } define( "POSTGRES_SEARCHPATH", $path ); }} @@ -699,6 +734,10 @@ class DatabasePostgres extends Database { return "E'" . pg_escape_string($s) . "'"; } + function quote_ident( $s ) { + return '"' . preg_replace( '/"/', '""', $s) . '"'; + } + } ?> -- 2.20.1