/**
* @param Account $account
*/
public function removeAccount(Account $account)
{
$workspaces = new Workspaces($this->getClient());
try {
$workspaces->deleteWorkspace($account->getWorkspaceId(), ["async" => true]);
} catch (\Keboola\StorageApi\Exception $e) {
$this->logException(Logger::DEBUG, $e->getMessage(), $e);
}
}
/**
* @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
* @return bool
* @throws InvalidCredentialsException
*/
public function validateAccount(Account $account)
{
if (!$this->isAlive() || !$account->isAlive()) {
$this->dropAccount($account);
throw new InvalidCredentialsException();
}
return true;
}
/**
* @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)) {
$conn->exec("DROP DATABASE {$account->getDatabase()};");
}
$conn->close();
}
/**
* @param Account $account Docker account
* @return \Keboola\ProvisioningBundle\Entity\Server[]
* @throws Exception
*/
public function getServersByAccount(Account $account)
{
/** @var Account\Docker $account */
$cluster = $account->getCluster();
$servers = $this->getRepository()->findBy(array("cluster" => $cluster, "active" => true));
if (count($servers) === 0) {
throw new NoActiveServerException("No active '{$this->getBackend()}' server found.");
}
return $servers;
}
/**
* @param Account $account
*/
public function removeAccount(Account $account)
{
$servers = $this->getServersByHostname($account->getHostname());
if (!$servers || !isset($servers[0]) || !$servers[0]) {
return;
}
$server = $servers[0];
if ($account->getGroupId()) {
$accounts = $this->getAccountRepository()->findBy(array("hostname" => $account->getHostname(), "groupId" => $account->getGroupId(), "active" => true));
$otherAccounts = array();
foreach ($accounts as $item) {
if ($item->getId() != $account->getId()) {
$otherAccounts[] = $item;
}
}
if (!count($otherAccounts)) {
$server->dropAccount($account);
} else {
try {
$server->killProcesses($account);
$server->revokePermission($account, $account->getDatabase());
} catch (InvalidCredentialsException $e) {
// Skip this exception
}
}
} else {
$server->dropAccount($account);
}
}
/**
* Create and start a new docker sandbox.
*
* @param Account $account
* @throws Exception
*/
public function addAccount(Account $account)
{
/** @var Account\Docker $account */
$ecsClient = $this->getEcsClient();
$ec2Client = $this->getEc2Client();
$imageName = $this->getDockerImageByType($account->getType());
$selectedPort = random_int(self::MIN_PORT, self::MAX_PORT);
// Start the container
try {
$taskDefinition = $this->getTaskDefinition($this->getTaskName($account), $imageName, $selectedPort, $this->getContainerPortByType($account->getType()));
$res = $ecsClient->registerTaskDefinition($taskDefinition)->toArray();
$this->checkResponseSuccess($res);
$taskId = $res['taskDefinition']['family'] . ':' . $res['taskDefinition']['revision'];
$res = $ecsClient->runTask($this->getRunTaskDefinition($taskId, $imageName, $account->getToken(), $this->exportConfig, $this->script, $account, $this->runId))->toArray();
$this->checkResponseSuccess($res);
$taskArn = $res['tasks'][0]['taskArn'];
} catch (EcsException $e) {
$this->logException(Logger::ERROR, $e->getMessage(), $e);
throw $e;
} catch (Exception $e) {
$this->logException(Logger::ERROR, $e->getMessage(), $e);
throw $e;
}
// Wait for the task to start
$attempt = 0;
do {
$attempt++;
sleep(min(pow(2, $attempt), self::MAX_WAIT_DELAY));
$res = $ecsClient->describeTasks(['cluster' => $this->getCluster(), 'tasks' => [$taskArn]])->toArray();
$this->checkResponseSuccess($res);
} while ($res['tasks'][0]['lastStatus'] == 'PENDING');
if ($res['tasks'][0]['lastStatus'] != 'RUNNING') {
throw new Exception("ECS task did not start successfully. " . !empty($res['tasks'][0]['stoppedReason']) ? $res['tasks'][0]['stoppedReason'] : '');
}
// Determine container EC2 instance ID
$taskContainerArn = $res['tasks'][0]['containerInstanceArn'];
$result = $ecsClient->describeContainerInstances(['containerInstances' => [$taskContainerArn], 'cluster' => $this->getCluster()])->toArray();
// Get DNS name of EC2 instance
$ec2InstanceId = $result['containerInstances'][0]['ec2InstanceId'];
$result = $ec2Client->describeInstances(['InstanceIds' => [$ec2InstanceId]])->toArray();
$dns = $result['Reservations'][0]['Instances'][0]['PublicDnsName'];
$account->setArn($taskArn);
$account->setPort($selectedPort);
$account->setHostname($dns);
}
