codekingpro/portable-devtools
114k
1# ##########################################################################2# #3# # pgAdmin 4 - PostgreSQL Tools4# #5# # Copyright (C) 2013 - 2024, The pgAdmin Development Team6# # This software is released under the PostgreSQL Licence7# #8# ##########################################################################9 10# Google Cloud Deployment Implementation11import pickle12import json13import os14from urllib.parse import unquote15 16from config import root17from pgadmin.utils.csrf import pgCSRFProtect18from pgadmin.utils.ajax import plain_text_response, unauthorized, \19 make_json_response, bad_request20from pgadmin.misc.bgprocess import BatchProcess21from pgadmin.misc.cloud.utils import _create_server, CloudProcessDesc22from pgadmin.utils import PgAdminModule, filename_with_file_manager_path23from pgadmin.user_login_check import pga_login_required24from flask import session, current_app, request25from flask_babel import gettext as _26 27from oauthlib.oauth2 import AccessDeniedError28from googleapiclient import discovery29from googleapiclient.errors import HttpError30from google_auth_oauthlib.flow import InstalledAppFlow31from google.auth.transport.requests import Request32 33MODULE_NAME = 'google'34os.environ['OAUTHLIB_INSECURE_TRANSPORT'] = '1' # Required for Oauth235 36 37class GooglePostgresqlModule(PgAdminModule):38 """Cloud module to deploy on Google Cloud"""39 40 def get_exposed_url_endpoints(self):41 return ['google.verify_credentials',42 'google.projects',43 'google.regions',44 'google.database_versions',45 'google.instance_types',46 'google.availability_zones',47 'google.verification_ack',48 'google.callback']49 50 51blueprint = GooglePostgresqlModule(MODULE_NAME, __name__,52 static_url_path='/misc/cloud/google')53 54 55@blueprint.route("/")56@pga_login_required57def index():58 return bad_request(errormsg=_("This URL cannot be called directly."))59 60 61@blueprint.route('/verify_credentials/',62 methods=['POST'], endpoint='verify_credentials')63@pga_login_required64def verify_credentials():65 """66 Initiate process of authorisation for google oauth267 """68 data = json.loads(request.data)69 client_secret_path = data['secret']['client_secret_file'] if \70 'client_secret_file' in data['secret'] else None71 status = False72 error = None73 res_data = {}74 75 client_secret_path = unquote(client_secret_path)76 try:77 client_secret_path = \78 filename_with_file_manager_path(client_secret_path)79 except PermissionError as e:80 return unauthorized(errormsg=str(e))81 except Exception as e:82 return bad_request(errormsg=str(e))83 84 if client_secret_path and os.path.exists(client_secret_path):85 with open(client_secret_path, 'r') as json_file:86 client_config = json.load(json_file)87 88 if 'google' not in session:89 session['google'] = {}90 91 if 'google_obj' not in session['google'] or \92 session['google']['client_config'] != client_config:93 _google = Google(client_config)94 else:95 _google = pickle.loads(session['google']['google_obj'])96 97 # get auth url98 host_url = request.origin + '/'99 if request.root_path != '':100 host_url = host_url + request.root_path + '/'101 102 auth_url, error_msg = _google.get_auth_url(host_url)103 if error_msg:104 error = error_msg105 else:106 status = True107 res_data = {'auth_url': auth_url}108 # save google object109 session['google']['client_config'] = client_config110 session['google']['google_obj'] = pickle.dumps(_google, -1)111 else:112 error = 'Client secret path not found'113 session.pop('google', None)114 115 return make_json_response(success=status, errormsg=error, data=res_data)116 117 118@blueprint.route('/callback',119 methods=['GET'], endpoint='callback')120@pgCSRFProtect.exempt121@pga_login_required122def callback():123 """124 Call back function on google authentication response.125 :return:126 """127 google_obj = pickle.loads(session['google']['google_obj'])128 res = google_obj.callback(request)129 session['google']['google_obj'] = pickle.dumps(google_obj, -1)130 return plain_text_response(res)131 132 133@blueprint.route('/verification_ack',134 methods=['GET'], endpoint='verification_ack')135@pga_login_required136def verification_ack():137 """138 Checks for google oauth2 authorisation confirmation139 :return:140 """141 verified = False142 if 'google' in session and 'google_obj' in session['google']:143 google_obj = pickle.loads(session['google']['google_obj'])144 verified, error = google_obj.verification_ack()145 session['google']['google_obj'] = pickle.dumps(google_obj, -1)146 return make_json_response(success=verified, errormsg=error)147 else:148 return make_json_response(success=verified,149 errormsg='Authentication is failed.')150 151 152@blueprint.route('/projects/',153 methods=['GET'], endpoint='projects')154@pga_login_required155def get_projects():156 """157 Lists the projects for authorized user158 :return: list of projects159 """160 if 'google' in session and 'google_obj' in session['google']:161 google_obj = pickle.loads(session['google']['google_obj'])162 projects_list = google_obj.get_projects()163 return make_json_response(data=projects_list)164 165 166@blueprint.route('/regions/<project_id>',167 methods=['GET'], endpoint='regions')168@pga_login_required169def get_regions(project_id):170 """171 Lists regions based on project for authorized user172 :param project_id: google project id173 :return: google cloud sql region list174 """175 if 'google' in session and 'google_obj' in session['google'] \176 and project_id:177 google_obj = pickle.loads(session['google']['google_obj'])178 regions_list = google_obj.get_regions(project_id)179 session['google']['google_obj'] = pickle.dumps(google_obj, -1)180 return make_json_response(data=regions_list)181 else:182 return make_json_response(data=[])183 184 185@blueprint.route('/availability_zones/<region>',186 methods=['GET'], endpoint='availability_zones')187@pga_login_required188def get_availability_zones(region):189 """190 List availability zones for specified region191 :param region: google region192 :return: google cloud sql availability zone list193 """194 if 'google' in session and 'google_obj' in session['google'] and region:195 google_obj = pickle.loads(session['google']['google_obj'])196 availability_zone_list = google_obj.get_availability_zones(region)197 return make_json_response(data=availability_zone_list)198 else:199 return make_json_response(data=[])200 201 202@blueprint.route('/instance_types/<project_id>/<region>/<instance_class>',203 methods=['GET'], endpoint='instance_types')204@pga_login_required205def get_instance_types(project_id, region, instance_class):206 """207 List the instances types for specified google project, region &208 instance type209 :param project_id: google project id210 :param region: google cloud region211 :param instance_class: google cloud sql instnace class212 :return:213 """214 if 'google' in session and 'google_obj' in session['google'] and \215 project_id and region:216 google_obj = pickle.loads(session['google']['google_obj'])217 instance_types_dict = google_obj.get_instance_types(218 project_id, region)219 instance_types_list = instance_types_dict.get(instance_class, [])220 return make_json_response(data=instance_types_list)221 else:222 return make_json_response(data=[])223 224 225@blueprint.route('/database_versions/',226 methods=['GET'], endpoint='database_versions')227@pga_login_required228def get_database_versions():229 """230 Lists the postgresql database versions.231 :return: PostgreSQL version list232 """233 if 'google' in session and 'google_obj' in session['google']:234 google_obj = pickle.loads(session['google']['google_obj'])235 db_version_list = google_obj.get_database_versions()236 return make_json_response(data=db_version_list)237 else:238 return make_json_response(data=[])239 240 241def deploy_on_google(data):242 """Deploy the Postgres instance on RDS."""243 _cmd = 'python'244 _cmd_script = '{0}/pgacloud/pgacloud.py'.format(root)245 _label = data['instance_details']['name']246 247 # Supported arguments for google cloud sql deployment248 args = [_cmd_script,249 data['cloud'],250 'create-instance',251 252 '--project', data['instance_details']['project'],253 254 '--region', data['instance_details']['region'],255 256 '--name', data['instance_details']['name'],257 258 '--db-version', data['instance_details']['db_version'],259 260 '--instance-type', data['instance_details']['instance_type'],261 262 '--storage-type', data['instance_details']['storage_type'],263 264 '--storage-size', str(data['instance_details']['storage_size']),265 266 '--public-ip', str(data['instance_details']['public_ips']),267 268 '--availability-zone',269 data['instance_details']['availability_zone'],270 271 '--high-availability',272 str(data['instance_details']['high_availability']),273 274 '--secondary-availability-zone',275 data['instance_details']['secondary_availability_zone'],276 ]277 278 _cmd_msg = '{0} {1} {2}'.format(_cmd, _cmd_script, ' '.join(args))279 try:280 sid = _create_server({281 'gid': data['db_details']['gid'],282 'name': data['instance_details']['name'],283 'db': 'postgres',284 'username': 'postgres',285 'port': 5432,286 'cloud_status': -1287 })288 289 p = BatchProcess(290 desc=CloudProcessDesc(sid, _cmd_msg, data['cloud'],291 data['instance_details']['name']),292 cmd=_cmd,293 args=args294 )295 296 # Set env variables for background process of deployment297 env = dict()298 google_obj = pickle.loads(session['google']['google_obj'])299 env['GOOGLE_CREDENTIALS'] = json.dumps(google_obj.credentials_json)300 301 if 'db_password' in data['db_details']:302 env['GOOGLE_DATABASE_PASSWORD'] = data['db_details']['db_password']303 304 p.set_env_variables(None, env=env)305 p.update_server_id(p.id, sid)306 p.start()307 308 return True, p, {'label': _label, 'sid': sid}309 except Exception as e:310 current_app.logger.exception(e)311 return False, None, str(e)312 313 314def clear_google_session():315 """Clear Google Session"""316 if 'google' in session:317 session.pop('google')318 319 320class Google:321 def __init__(self, client_config=None):322 # Google cloud sql api versions323 self._cloud_resource_manager_api_version = 'v1'324 self._sqladmin_api_version = 'v1'325 self._compute_api_version = 'v1'326 327 # Scope required for google cloud sql deployment328 self._scopes = ['https://www.googleapis.com/auth/cloud-platform',329 'https://www.googleapis.com/auth/sqlservice.admin']330 331 # Instance classed332 self._instance_classes = [{'label': 'Standard', 'value': 'standard'},333 {'label': 'High Memory', 'value': 'highmem'},334 {'label': 'Shared', 'value': 'shared'}]335 336 self._client_config = client_config337 self._credentials = None338 self.credentials_json = None339 self._project_id = None340 self._regions = []341 self._availability_zones = {}342 self._verification_successful = False343 self._verification_error = None344 self._redirect_url = None345 346 def get_auth_url(self, host_url):347 """348 Provides google authorisation url349 :param host_url: Base url for hosting application350 :return: authorisation url to complete authentication351 """352 auth_url = None353 error = None354 # reset below variable to get latest values in fresh355 # authentication call356 self._verification_successful = False357 self._verification_error = None358 try:359 self._redirect_url = host_url + 'google/callback'360 flow = InstalledAppFlow.from_client_config(361 client_config=self._client_config, scopes=self._scopes,362 redirect_uri=self._redirect_url)363 auth_url, state = flow.authorization_url(364 prompt='select_account', access_type='offline',365 include_granted_scopes='true')366 session["state"] = state367 except Exception as e:368 error = str(e)369 self._verification_error = error370 return auth_url, error371 372 def callback(self, flask_request):373 """374 Callback function on completion of google authorisation request375 :param flask_request:376 :return: Success or error message377 """378 try:379 authorization_response = flask_request.url380 if session['state'] != flask_request.args.get('state', None):381 self._verification_successful = False,382 self._verification_error = 'Invalid state parameter'383 flow = InstalledAppFlow.from_client_config(384 client_config=self._client_config, scopes=self._scopes,385 redirect_uri=self._redirect_url)386 flow.fetch_token(authorization_response=authorization_response)387 self._credentials = flow.credentials388 self.credentials_json = \389 self._credentials_to_dict(self._credentials)390 self._verification_successful = True391 return 'The authentication flow has completed. ' \392 'This window will be closed.'393 except AccessDeniedError as er:394 self._verification_successful = False395 self._verification_error = er.error396 if self._verification_error == 'access_denied':397 self._verification_error = 'Access denied.'398 return self._verification_error399 400 @staticmethod401 def _credentials_to_dict(credentials):402 return {'token': credentials.token,403 'refresh_token': credentials.refresh_token,404 'token_uri': credentials.token_uri,405 'client_id': credentials.client_id,406 'client_secret': credentials.client_secret,407 'scopes': credentials.scopes,408 'id_token': credentials.id_token}409 410 def verification_ack(self):411 """Check the Verification is done or not."""412 return self._verification_successful, self._verification_error413 414 def _get_credentials(self, scopes):415 """416 Provides google credentials for google cloud sql api calls417 :param scopes: Required scope of credentials418 :return: google credential object419 """420 if not self._credentials or not self._credentials.valid:421 if self._credentials and self._credentials.expired and \422 self._credentials.refresh_token and \423 self._credentials.has_scopes(scopes):424 self._credentials.refresh(Request())425 return self._credentials426 return self._credentials427 428 def get_projects(self):429 """430 List the google projects for authorised user431 :return:432 """433 projects = []434 credentials = self._get_credentials(self._scopes)435 service = discovery.build('cloudresourcemanager',436 self._cloud_resource_manager_api_version,437 credentials=credentials)438 req = service.projects().list()439 res = req.execute()440 for project in res.get('projects', []):441 projects.append({'label': project['projectId'],442 'value': project['projectId']})443 return projects444 445 def get_regions(self, project):446 """447 List regions for specified google cloud project448 :param project: google cloud project id.449 :return:450 """451 self._project_id = project452 credentials = self._get_credentials(self._scopes)453 service = discovery.build('compute',454 self._compute_api_version,455 credentials=credentials)456 try:457 req = service.regions().list(project=project)458 res = req.execute()459 except HttpError:460 self._regions = []461 return self._regions462 for item in res.get('items', []):463 region_name = item['name']464 self._regions.append({'label': region_name, 'value': region_name})465 region_zones = item.get('zones', [])466 region_zones = list(467 map(lambda region: region.split('/')[-1], region_zones))468 self._availability_zones[region_name] = region_zones469 return self._regions470 471 def get_availability_zones(self, region):472 """473 List availability zones in given google cloud region474 :param region: google cloud region475 :return:476 """477 az_list = []478 for az in self._availability_zones.get(region, []):479 az_list.append({'label': az, 'value': az})480 return az_list481 482 def get_instance_types(self, project, region):483 """484 Lists google cloud sql instance types.485 :param project:486 :param region:487 :return:488 """489 standard_instances = []490 shared_instances = []491 high_mem = []492 credentials = self._get_credentials(self._scopes)493 service = discovery.build('sqladmin',494 self._sqladmin_api_version,495 credentials=credentials)496 req = service.tiers().list(project=project)497 res = req.execute()498 for item in res.get('items', []):499 if region in item.get('region', []):500 if item['tier'].find('standard') != -1:501 vcpu = item['tier'].split('-')[-1]502 mem = round(int(item['RAM']) / (1024 * 1024))503 label = vcpu + ' vCPU, ' + str(round(mem / 1024)) + ' GB'504 value = 'db-custom-' + str(vcpu) + '-' + str(mem)505 standard_instances.append({'label': label, 'value': value})506 elif item['tier'].find('highmem') != -1:507 vcpu = item['tier'].split('-')[-1]508 mem = round(int(item['RAM']) / (1024 * 1024))509 label = vcpu + ' vCPU, ' + str(round(mem / 1024)) + ' GB'510 value = 'db-custom-' + str(vcpu) + '-' + str(mem)511 high_mem.append({'label': label, 'value': value})512 else:513 label = '1 vCPU, ' + str(514 round((int(item['RAM']) / 1073741824), 2)) + ' GB'515 value = item['tier']516 shared_instances.append({'label': label, 'value': value})517 instance_types = {'standard': standard_instances,518 'highmem': high_mem,519 'shared': shared_instances}520 return instance_types521 522 def get_database_versions(self):523 """524 Lists the PostgreSQL database versions525 :return:526 """527 pg_database_versions = []528 database_versions = []529 credentials = self._get_credentials(self._scopes)530 service = discovery.build('sqladmin',531 self._sqladmin_api_version,532 credentials=credentials)533 req = service.flags().list()534 res = req.execute()535 for item in res.get('items', []):536 if item.get('name', '') == 'max_parallel_workers':537 pg_database_versions = item.get('appliesTo', [])538 for version in pg_database_versions:539 label = (version.title().split('_')[0])[0:7] \540 + 'SQL ' + version.split('_')[1]541 database_versions.append({'label': label, 'value': version})542 return database_versions543