/**
* @param Account $account
*/
protected function dropDatabase(Account $account)
{
// User current users credentials to see available databases
$conn = $this->connect($account->getDatabase());
$databases = $conn->fetchArray("SHOW DATABASES WHERE `Database` = '{$account->getDatabase()}';");
if (is_array($databases) && in_array($account->getDatabase(), $databases)) {
$privileges = $conn->fetchAll("\n SELECT\n *\n FROM information_schema.schema_privileges\n WHERE\n TABLE_SCHEMA = '{$account->getDatabase()}'\n AND SUBSTR(SUBSTRING_INDEX(GRANTEE, '\\'@', 1), 2) != '{$account->getUser()}';\n ");
// Drop only if no other users have access to the db
if (count($privileges) == 0) {
$conn->exec("DROP DATABASE {$account->getDatabase()};");
}
}
$conn->close();
}
/**
* @param Account $account
* @param Connection $conn
* @param $tokenInfo
* @param $buckets
* @throws DBALException
*/
protected function grantPermissionsTry(Account $account, Connection $conn, $tokenInfo, $buckets)
{
$userName = $account->getUser();
$allowedBuckets = array();
if ($account->getType() == 'transformations' || $account->getType() == 'sandbox' || $account->getType() == 'luckyguess') {
foreach ($buckets as $bucket) {
if ((substr($bucket["id"], 0, 3) == 'in.' || substr($bucket["id"], 0, 4) == 'out.') && $bucket["backend"] == 'redshift' && in_array($bucket["id"], array_keys($tokenInfo["bucketPermissions"]))) {
$allowedBuckets[] = strtolower($bucket["id"]);
}
}
}
// Set custom permissions
if (count($allowedBuckets) > 0) {
// Tables
$query = "\n SELECT TRIM(schemaname) AS schema, TRIM(tablename) AS table\n FROM pg_tables\n WHERE TRIM(schemaname) IN ('" . join("', '", $allowedBuckets) . "');\n ";
$tablesInRs = $conn->fetchAll($query);
$schemaNames = array();
if (count($tablesInRs)) {
$tableIds = array();
foreach ($tablesInRs as $tableInRs) {
if (substr($tableInRs["table"], 0, 6) != '__temp') {
$tableIds[] = '"' . strtolower($tableInRs["schema"]) . '"."' . strtolower($tableInRs["table"]) . '"';
$schemaNames[] = '"' . strtolower($tableInRs["schema"]) . '"';
}
}
$query = "\n GRANT SELECT\n ON " . join(', ', $tableIds) . "\n TO {$userName};\n ";
$conn->exec($query);
}
// Views
$query = "\n SELECT TRIM(schemaname) AS schema, TRIM(viewname) AS view\n FROM pg_views\n WHERE TRIM(schemaname) IN ('" . join("', '", $allowedBuckets) . "');\n ";
$viewsInRs = $conn->fetchAll($query);
if (count($viewsInRs)) {
$viewIds = array();
foreach ($viewsInRs as $viewInRs) {
$viewIds[] = '"' . strtolower($viewInRs["schema"]) . '"."' . strtolower($viewInRs["view"]) . '"';
$schemaNames[] = '"' . strtolower($viewInRs["schema"]) . '"';
}
$query = "\n GRANT SELECT\n ON " . join(', ', $viewIds) . "\n TO {$userName};\n ";
$conn->exec($query);
}
// Schemas
if (count($schemaNames)) {
$schemaNames = array_unique($schemaNames);
$query = "\n GRANT USAGE\n ON SCHEMA " . join(', ', $schemaNames) . "\n TO {$userName};\n ";
$conn->exec($query);
}
}
// system tables
if ($account->getType() == "transformations") {
$query = "\n \t\t\t\tGRANT SELECT\n \t\t\t\tON SVV_TABLE_INFO\n \t\t\t\tTO {$account->getUser()};\n \t\t\t";
$conn->exec($query);
}
// Grant access to its own schema
if ($account->getType() == 'read') {
$conn->exec("GRANT USAGE ON SCHEMA \"{$account->getSchema()}\" TO \"{$account->getUser()}\";");
$conn->exec("GRANT SELECT ON ALL TABLES IN SCHEMA \"{$account->getSchema()}\" TO \"{$account->getUser()}\";");
} else {
$conn->exec("GRANT ALL ON SCHEMA \"{$account->getSchema()}\" TO \"{$account->getUser()}\";");
}
}
/**
* @param Account $account
*/
protected function grantPrivileges(Account $account)
{
$conn = $this->connect();
$conn->exec("GRANT ALL ON `{$account->getDatabase()}`.* TO '{$account->getUser()}';");
$conn->exec("FLUSH PRIVILEGES;");
$conn->close();
}
/**
* Stop the running container.
* @param Account $account
* @throws \Exception
*/
public function dropAccount(Account $account)
{
/** @var Account\Docker $account */
try {
$ecsClient = $this->getEcsClient();
$response = $ecsClient->describeTasks(['cluster' => $this->getCluster(), 'tasks' => [$account->getArn()]])->toArray();
// Stop the task if it really exists
if (count($response['tasks']) > 0) {
$ecsClient = $this->getEcsClient();
$response = $ecsClient->stopTask(['cluster' => $this->getCluster(), 'reason' => 'dropAccount', 'task' => $account->getArn()])->toArray();
$this->checkResponseSuccess($response);
}
} catch (\Exception $e) {
$this->logException(Logger::ERROR, "Can't stop task {$account->getUser()}", $e);
throw $e;
}
}
