Coverage for /Users/akshayjoshi/Developement/pgAdmin4/web/pgadmin/browser/server_groups/servers/__init__.py: 55%

Hide keyboard shortcuts

Hot-keys on this page

r m x p toggle line displays

j k next/prev highlighted chunk

0 (zero) top of page

1 (one) first highlighted chunk

1##########################################################################

2#

3# pgAdmin 4 - PostgreSQL Tools

4#

5# Copyright (C) 2013 - 2021, The pgAdmin Development Team

6# This software is released under the PostgreSQL Licence

7#

8##########################################################################

10import simplejson as json

11import pgadmin.browser.server_groups as sg

12from flask import render_template, request, make_response, jsonify, \

13 current_app, url_for, session

14from flask_babelex import gettext

15from flask_security import current_user, login_required

16from pgadmin.browser.server_groups.servers.types import ServerType

17from pgadmin.browser.utils import PGChildNodeView

18from pgadmin.utils.ajax import make_json_response, bad_request, forbidden, \

19 make_response as ajax_response, internal_server_error, unauthorized, gone

20from pgadmin.utils.crypto import encrypt, decrypt, pqencryptpassword

21from pgadmin.utils.menu import MenuItem

22from pgadmin.tools.sqleditor.utils.query_history import QueryHistory

24import config

25from config import PG_DEFAULT_DRIVER

26from pgadmin.model import db, Server, ServerGroup, User, SharedServer

27from pgadmin.utils.driver import get_driver

28from pgadmin.utils.master_password import get_crypt_key

29from pgadmin.utils.exception import CryptKeyMissing

30from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry

31from psycopg2 import Error as psycopg2_Error, OperationalError

32from pgadmin.browser.server_groups.servers.utils import is_valid_ipaddress

33from pgadmin.utils.constants import UNAUTH_REQ, MIMETYPE_APP_JS, \

34 SERVER_CONNECTION_CLOSED

35from sqlalchemy import or_

36from pgadmin.utils.preferences import Preferences

39def has_any(data, keys):

40 """

41 Checks any one of the keys present in the data given

42 """

43 if data is None and not isinstance(data, dict):

44 return False

46 if keys is None and not isinstance(keys, list):

47 return False

49 for key in keys:

50 if key in data:

51 return True

53 return False

56def recovery_state(connection, postgres_version):

57 recovery_check_sql = render_template(

58 "connect/sql/#{0}#/check_recovery.sql".format(postgres_version))

60 status, result = connection.execute_dict(recovery_check_sql)

61 if status and 'rows' in result and len(result['rows']) > 0:

62 in_recovery = result['rows'][0]['inrecovery']

63 wal_paused = result['rows'][0]['isreplaypaused']

64 else:

65 in_recovery = None

66 wal_paused = None

67 return status, result, in_recovery, wal_paused

70def get_preferences():

71 """

72 Get preferences setting

73 :return: whether to hide shared server or not.

74 """

75 hide_shared_server = None

76 if config.SERVER_MODE:

77 pref = Preferences.module('browser')

78 hide_shared_server = pref.preference('hide_shared_server').get()

80 return hide_shared_server

83def server_icon_and_background(is_connected, manager, server):

84 """

86 Args:

87 is_connected: Flag to check if server is connected

88 manager: Connection manager

89 server: Sever object

91 Returns:

92 Server Icon CSS class

93 """

94 server_background_color = ''

95 if server and server.bgcolor:

96 server_background_color = ' {0}'.format(

97 server.bgcolor

98 )

99 # If user has set font color also

100 if server.fgcolor:

101 server_background_color = '{0} {1}'.format(

102 server_background_color,

103 server.fgcolor

106 if is_connected:

107 return 'icon-{0}{1}'.format(

108 manager.server_type, server_background_color

110 elif server.shared and config.SERVER_MODE:

111 return 'icon-shared-server-not-connected{0}'.format(

112 server_background_color

114 else:

115 return 'icon-server-not-connected{0}'.format(

116 server_background_color

120class ServerModule(sg.ServerGroupPluginModule):

121 _NODE_TYPE = "server"

122 LABEL = gettext("Servers")

124 @property

125 def node_type(self):

126 return self._NODE_TYPE

128 @property

129 def script_load(self):

130 """

131 Load the module script for server, when any of the server-group node is

132 initialized.

133 """

134 return sg.ServerGroupModule.node_type

136 @staticmethod

137 def get_shared_server_properties(server, sharedserver):

138 """

139 Return shared server properties

140 :param server:

141 :param sharedserver:

142 :return: shared server

143 """

144 server.bgcolor = sharedserver.bgcolor

145 server.fgcolor = sharedserver.fgcolor

146 server.name = sharedserver.name

147 server.role = sharedserver.role

148 server.use_ssh_tunnel = sharedserver.use_ssh_tunnel

149 server.tunnel_host = sharedserver.tunnel_host

150 server.tunnel_port = sharedserver.tunnel_port

151 server.tunnel_authentication = sharedserver.tunnel_authentication

152 server.tunnel_username = sharedserver.tunnel_username

153 server.tunnel_password = sharedserver.tunnel_password

154 server.save_password = sharedserver.save_password

155 server.passfile = sharedserver.passfile

156 server.servergroup_id = sharedserver.servergroup_id

157 server.sslcert = sharedserver.sslcert

158 server.username = sharedserver.username

159 server.server_owner = sharedserver.server_owner

161 return server

163 def get_servers(self, all_servers, hide_shared_server, gid):

164 """

165 This function creates list of servers which needs to display

166 in browser tree

167 :param all_servers:

168 :param hide_shared_server:

169 :param gid:

170 :return: list of servers

171 """

172 servers = []

173 for server in all_servers:

174 if server.discovery_id and \

175 not server.shared and \

176 config.SERVER_MODE and \

177 len(SharedServer.query.filter_by(

178 user_id=current_user.id,

179 name=server.name).all()) > 0 and not hide_shared_server:

180 continue

182 if server.shared and server.user_id != current_user.id:

184 shared_server = self.get_shared_server(server, gid)

186 if hide_shared_server:

187 # Don't include shared server if hide shared server is

188 # set to true.

189 continue

191 server = self.get_shared_server_properties(server,

192 shared_server)

193 servers.append(server)

195 return servers

197 @login_required

198 def get_nodes(self, gid):

199 """Return a JSON document listing the server groups for the user"""

201 hide_shared_server = get_preferences()

202 servers = Server.query.filter(

203 or_(Server.user_id == current_user.id, Server.shared),

204 Server.servergroup_id == gid)

206 driver = get_driver(PG_DEFAULT_DRIVER)

207 servers = self.get_servers(servers, hide_shared_server, gid)

209 for server in servers:

210 connected = False

211 manager = None

212 errmsg = None

213 was_connected = False

214 in_recovery = None

215 wal_paused = None

216 server_type = 'pg'

217 user_info = None

218 try:

219 manager = driver.connection_manager(server.id)

220 conn = manager.connection()

221 was_connected = conn.wasConnected

222 connected = conn.connected()

223 if connected:

224 server_type = manager.server_type

225 user_info = manager.user_info

226 except CryptKeyMissing:

227 # show the nodes at least even if not able to connect.

228 pass

229 except psycopg2_Error as e:

230 current_app.logger.exception(e)

231 errmsg = str(e)

233 yield self.generate_browser_node(

234 "%d" % (server.id),

235 gid,

236 server.name,

237 server_icon_and_background(connected, manager, server),

238 True,

239 self.node_type,

240 connected=connected,

241 server_type=server_type,

242 version=manager.version,

243 db=manager.db,

244 user=user_info,

245 in_recovery=in_recovery,

246 wal_pause=wal_paused,

247 host=server.host,

248 port=server.port,

249 is_password_saved=bool(server.save_password),

250 is_tunnel_password_saved=True

251 if server.tunnel_password is not None else False,

252 was_connected=was_connected,

253 errmsg=errmsg,

254 user_id=server.user_id,

255 user_name=server.username,

256 shared=server.shared

259 @property

260 def jssnippets(self):

261 return []

263 @property

264 def csssnippets(self):

265 """

266 Returns a snippet of css to include in the page

267 """

268 snippets = [render_template("css/servers.css")]

270 for submodule in self.submodules:

271 snippets.extend(submodule.csssnippets)

273 for st in ServerType.types():

274 snippets.extend(st.csssnippets)

276 return snippets

278 def get_own_javascripts(self):

279 scripts = []

281 scripts.extend([{

282 'name': 'pgadmin.browser.server.privilege',

283 'path': url_for('%s.static' % self.name, filename='js/privilege'),

284 'when': self.node_type,

285 'is_template': False,

286 'deps': ['pgadmin.browser.node.ui']

287 }, {

288 'name': 'pgadmin.browser.server.variable',

289 'path': url_for('%s.static' % self.name, filename='js/variable'),

290 'when': self.node_type,

291 'is_template': False

292 }, {

293 'name': 'pgadmin.server.supported_servers',

294 'path': url_for('browser.index') + 'server/supported_servers',

295 'is_template': True,

296 'when': self.node_type

297 }])

298 scripts.extend(sg.ServerGroupPluginModule.get_own_javascripts(self))

300 return scripts

302 def register(self, app, options, first_registration=False):

303 """

304 Override the default register function to automagically register

305 sub-modules at once.

306 """

307 if first_registration:

308 driver = get_driver(PG_DEFAULT_DRIVER, app)

309 app.jinja_env.filters['qtLiteral'] = driver.qtLiteral

310 app.jinja_env.filters['qtIdent'] = driver.qtIdent

311 app.jinja_env.filters['qtTypeIdent'] = driver.qtTypeIdent

312 app.jinja_env.filters['hasAny'] = has_any

314 super(ServerModule, self).register(app, options, first_registration)

316 # We do not have any preferences for server node.

317 def register_preferences(self):

318 """

319 register_preferences

320 Override it so that - it does not register the show_node preference for

321 server type.

322 """

323 ServerType.register_preferences()

325 def get_exposed_url_endpoints(self):

326 return ['NODE-server.connect_id']

328 @staticmethod

329 def create_shared_server(data, gid):

330 """

331 Create shared server

332 :param data:

333 :param gid:

334 :return: None

335 """

337 shared_server = None

338 try:

339 db.session.rollback()

340 user = User.query.filter_by(id=data.user_id).first()

341 shared_server = SharedServer(

342 user_id=current_user.id,

343 server_owner=user.username,

344 servergroup_id=gid,

345 name=data.name,

346 host=data.host,

347 hostaddr=data.hostaddr,

348 port=data.port,

349 maintenance_db=None,

350 username=None,

351 save_password=0,

352 ssl_mode=data.ssl_mode,

353 comment=None,

354 role=data.role,

355 sslcert=None,

356 sslkey=None,

357 sslrootcert=None,

358 sslcrl=None,

359 bgcolor=data.bgcolor if data.bgcolor else None,

360 fgcolor=data.fgcolor if data.fgcolor else None,

361 service=data.service if data.service else None,

362 connect_timeout=0,

363 use_ssh_tunnel=data.use_ssh_tunnel,

364 tunnel_host=data.tunnel_host,

365 tunnel_port=22,

366 tunnel_username=None,

367 tunnel_authentication=0,

368 tunnel_identity_file=None,

369 shared=True

371 db.session.add(shared_server)

372 db.session.commit()

373 except Exception as e:

374 if shared_server:

375 db.session.delete(shared_server)

376 db.session.commit()

378 current_app.logger.exception(e)

379 return internal_server_error(errormsg=str(e))

381 @staticmethod

382 def get_shared_server(server, gid):

383 """

384 return the shared server

385 :param server:

386 :param gid:

387 :return: shared_server

388 """

389 shared_server = SharedServer.query.filter_by(

390 name=server.name, user_id=current_user.id,

391 servergroup_id=gid).first()

393 if shared_server is None:

394 ServerModule.create_shared_server(server, gid)

396 shared_server = SharedServer.query.filter_by(

397 name=server.name, user_id=current_user.id,

398 servergroup_id=gid).first()

400 return shared_server

403class ServerMenuItem(MenuItem):

404 def __init__(self, **kwargs):

405 kwargs.setdefault("type", ServerModule.node_type)

406 super(ServerMenuItem, self).__init__(**kwargs)

409blueprint = ServerModule(__name__)

412class ServerNode(PGChildNodeView):

413 node_type = ServerModule._NODE_TYPE

414 node_label = "Server"

416 parent_ids = [{'type': 'int', 'id': 'gid'}]

417 ids = [{'type': 'int', 'id': 'sid'}]

418 operations = dict({

419 'obj': [

420 {'get': 'properties', 'delete': 'delete', 'put': 'update'},

421 {'get': 'list', 'post': 'create'}

422 ],

423 'nodes': [{'get': 'node'}, {'get': 'nodes'}],

424 'sql': [{'get': 'sql'}],

425 'msql': [{'get': 'modified_sql'}],

426 'stats': [{'get': 'statistics'}],

427 'dependency': [{'get': 'dependencies'}],

428 'dependent': [{'get': 'dependents'}],

429 'children': [{'get': 'children'}],

430 'supported_servers.js': [{}, {}, {'get': 'supported_servers'}],

431 'reload':

432 [{'get': 'reload_configuration'}],

433 'restore_point':

434 [{'post': 'create_restore_point'}],

435 'connect': [{

436 'get': 'connect_status', 'post': 'connect', 'delete': 'disconnect'

437 }],

438 'change_password': [{'post': 'change_password'}],

439 'wal_replay': [{

440 'delete': 'pause_wal_replay', 'put': 'resume_wal_replay'

441 }],

442 'check_pgpass': [{'get': 'check_pgpass'}],

443 'clear_saved_password': [{'put': 'clear_saved_password'}],

444 'clear_sshtunnel_password': [{'put': 'clear_sshtunnel_password'}]

445 })

446 SSL_MODES = ['prefer', 'require', 'verify-ca', 'verify-full']

448 def check_ssl_fields(self, data):

449 """

450 This function will allow us to check and set defaults for

451 SSL fields

453 Args:

454 data: Response data

456 Returns:

457 Flag and Data

458 """

459 flag = False

461 if 'sslmode' in data and data['sslmode'] in self.SSL_MODES:

462 flag = True

463 ssl_fields = [

464 'sslcert', 'sslkey', 'sslrootcert', 'sslcrl', 'sslcompression'

466 # Required SSL fields for SERVER mode from user

467 required_ssl_fields_server_mode = ['sslcert', 'sslkey']

469 for field in ssl_fields:

470 if field in data:

471 continue

472 elif config.SERVER_MODE and \

473 field in required_ssl_fields_server_mode:

474 # In Server mode,

475 # we will set dummy SSL certificate file path which will

476 # prevent using default SSL certificates from web servers

478 # Set file manager directory from preference

479 import os

480 file_extn = '.key' if field.endswith('key') else '.crt'

481 dummy_ssl_file = os.path.join(

482 '<STORAGE_DIR>', '.postgresql',

483 'postgresql' + file_extn

485 data[field] = dummy_ssl_file

486 # For Desktop mode, we will allow to default

487 else:

488 data[field] = None

490 return flag, data

492 @login_required

493 def nodes(self, gid):

494 res = []

495 """

496 Return a JSON document listing the servers under this server group

497 for the user.

498 """

499 servers = Server.query.filter(

500 or_(Server.user_id == current_user.id,

501 Server.shared),

502 Server.servergroup_id == gid)

504 driver = get_driver(PG_DEFAULT_DRIVER)

506 for server in servers:

507 if server.shared and server.user_id != current_user.id:

508 shared_server = ServerModule.get_shared_server(server, gid)

509 server = \

510 ServerModule.get_shared_server_properties(server,

511 shared_server)

512 manager = driver.connection_manager(server.id)

513 conn = manager.connection()

514 connected = conn.connected()

515 errmsg = None

516 in_recovery = None

517 wal_paused = None

518 server_type = 'pg'

519 if connected:

520 server_type = manager.server_type

521 status, result, in_recovery, wal_paused =\

522 recovery_state(conn, manager.version)

523 if not status:

524 connected = False

525 manager.release()

526 errmsg = "{0} : {1}".format(server.name, result)

528 res.append(

529 self.blueprint.generate_browser_node(

530 "%d" % (server.id),

531 gid,

532 server.name,

533 server_icon_and_background(connected, manager, server),

534 True,

535 self.node_type,

536 connected=connected,

537 server_type=server_type,

538 version=manager.version,

539 db=manager.db,

540 host=server.host,

541 user=manager.user_info if connected else None,

542 in_recovery=in_recovery,

543 wal_pause=wal_paused,

544 is_password_saved=bool(server.save_password),

545 is_tunnel_password_saved=True

546 if server.tunnel_password is not None else False,

547 errmsg=errmsg,

548 user_name=server.username,

549 shared=server.shared

553 if not len(res):

554 return gone(errormsg=gettext(

555 'The specified server group with id# {0} could not be found.'

556 ))

558 return make_json_response(result=res)

560 @login_required

561 def node(self, gid, sid):

562 """Return a JSON document listing the server groups for the user"""

563 server = Server.query.filter_by(id=sid).first()

565 if server.shared and server.user_id != current_user.id:

566 shared_server = ServerModule.get_shared_server(server, gid)

567 server = ServerModule.get_shared_server_properties(server,

568 shared_server)

570 if server is None:

571 return make_json_response(

572 status=410,

573 success=0,

574 errormsg=gettext(

575 gettext(

576 "Could not find the server with id# {0}."

577 ).format(sid)

581 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(server.id)

582 conn = manager.connection()

583 connected = conn.connected()

584 errmsg = None

585 in_recovery = None

586 wal_paused = None

587 if connected:

588 status, result, in_recovery, wal_paused =\

589 recovery_state(conn, manager.version)

590 if not status:

591 connected = False

592 manager.release()

593 errmsg = "{0} : {1}".format(server.name, result)

595 return make_json_response(

596 result=self.blueprint.generate_browser_node(

597 "%d" % (server.id),

598 gid,

599 server.name,

600 server_icon_and_background(connected, manager, server),

601 True,

602 self.node_type,

603 connected=connected,

604 server_type=manager.server_type if connected else 'pg',

605 version=manager.version,

606 db=manager.db,

607 user=manager.user_info if connected else None,

608 in_recovery=in_recovery,

609 wal_pause=wal_paused,

610 host=server.host,

611 is_password_saved=bool(server.save_password),

612 is_tunnel_password_saved=True

613 if server.tunnel_password is not None else False,

614 errmsg=errmsg,

615 shared=server.shared,

616 user_name=server.username

617 ),

620 def delete_shared_server(self, server_name, gid):

621 """

622 Delete the shared server

623 :param server_name:

624 :return:

625 """

626 try:

627 shared_server = SharedServer.query.filter_by(name=server_name,

628 servergroup_id=gid)

629 for s in shared_server:

630 get_driver(PG_DEFAULT_DRIVER).delete_manager(s.id)

631 db.session.delete(s)

632 db.session.commit()

634 except Exception as e:

635 current_app.logger.exception(e)

636 return make_json_response(

637 success=0,

638 errormsg=e.message)

640 @login_required

641 def delete(self, gid, sid):

642 """Delete a server node in the settings database."""

643 servers = Server.query.filter_by(user_id=current_user.id, id=sid)

644 server_name = None

646 # TODO:: A server, which is connected, cannot be deleted

647 if servers is None:

648 return make_json_response(

649 status=410,

650 success=0,

651 errormsg=gettext(

652 'The specified server could not be found.\n'

653 'Does the user have permission to access the '

654 'server?'

657 else:

658 try:

659 for s in servers:

660 server_name = s.name

661 get_driver(PG_DEFAULT_DRIVER).delete_manager(s.id)

662 db.session.delete(s)

663 db.session.commit()

664 self.delete_shared_server(server_name, gid)

665 QueryHistory.clear_history(current_user.id, sid)

667 except Exception as e:

668 current_app.logger.exception(e)

669 return make_json_response(

670 success=0,

671 errormsg=e.message)

673 return make_json_response(success=1,

674 info=gettext("Server deleted"))

676 @login_required

677 def update(self, gid, sid):

678 """Update the server settings"""

679 server = Server.query.filter_by(id=sid).first()

680 sharedserver = None

682 if server is None:

683 return make_json_response(

684 status=410,

685 success=0,

686 errormsg=gettext("Could not find the required server.")

689 if config.SERVER_MODE and server.shared and \

690 server.user_id != current_user.id:

691 sharedserver = ServerModule.get_shared_server(server, gid)

693 # Not all parameters can be modified, while the server is connected

694 config_param_map = {

695 'name': 'name',

696 'host': 'host',

697 'hostaddr': 'hostaddr',

698 'port': 'port',

699 'db': 'maintenance_db',

700 'username': 'username',

701 'sslmode': 'ssl_mode',

702 'gid': 'servergroup_id',

703 'comment': 'comment',

704 'role': 'role',

705 'db_res': 'db_res',

706 'passfile': 'passfile',

707 'sslcert': 'sslcert',

708 'sslkey': 'sslkey',

709 'sslrootcert': 'sslrootcert',

710 'sslcrl': 'sslcrl',

711 'sslcompression': 'sslcompression',

712 'bgcolor': 'bgcolor',

713 'fgcolor': 'fgcolor',

714 'service': 'service',

715 'connect_timeout': 'connect_timeout',

716 'use_ssh_tunnel': 'use_ssh_tunnel',

717 'tunnel_host': 'tunnel_host',

718 'tunnel_port': 'tunnel_port',

719 'tunnel_username': 'tunnel_username',

720 'tunnel_authentication': 'tunnel_authentication',

721 'tunnel_identity_file': 'tunnel_identity_file',

722 'shared': 'shared'

725 disp_lbl = {

726 'name': gettext('name'),

727 'hostaddr': gettext('Host name/address'),

728 'port': gettext('Port'),

729 'db': gettext('Maintenance database'),

730 'username': gettext('Username'),

731 'sslmode': gettext('SSL Mode'),

732 'comment': gettext('Comments'),

733 'role': gettext('Role')

736 idx = 0

737 data = request.form if request.form else json.loads(

738 request.data, encoding='utf-8'

740 if 'db_res' in data:

741 data['db_res'] = ','.join(data['db_res'])

743 hostaddr = data.get('hostaddr')

744 if hostaddr and not is_valid_ipaddress(hostaddr):

745 return make_json_response(

746 success=0,

747 status=400,

748 errormsg=gettext('Host address not valid')

751 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

752 conn = manager.connection()

753 connected = conn.connected()

755 self._server_modify_disallowed_when_connected(

756 connected, data, disp_lbl)

758 idx = self._set_valid_attr_value(gid, data, config_param_map, server,

759 sharedserver)

761 if idx == 0:

762 return make_json_response(

763 success=0,

764 errormsg=gettext('No parameters were changed.')

767 try:

768 db.session.commit()

769 except Exception as e:

770 current_app.logger.exception(e)

771 return make_json_response(

772 success=0,

773 errormsg=e.message

776 # When server is connected, we don't require to update the connection

777 # manager. Because - we don't allow to change any of the parameters,

778 # which will affect the connections.

779 if not conn.connected():

780 manager.update(server)

782 return jsonify(

783 node=self.blueprint.generate_browser_node(

784 "%d" % (server.id), server.servergroup_id,

785 server.name,

786 server_icon_and_background(

787 connected, manager, sharedserver)

788 if server.shared and server.user_id != current_user.id

789 else server_icon_and_background(

790 connected, manager, server),

791 True,

792 self.node_type,

793 connected=connected,

794 shared=server.shared,

795 user_id=server.user_id,

796 user=manager.user_info if connected else None,

797 server_type='pg' # default server type

801 @staticmethod

802 def _update_server_details(server, sharedserver,

803 config_param_map, arg, value):

804 if server.shared and server.user_id != current_user.id:

805 setattr(sharedserver, config_param_map[arg], value)

806 else:

807 setattr(server, config_param_map[arg], value)

809 def _set_valid_attr_value(self, gid, data, config_param_map, server,

810 sharedserver):

812 idx = 0

813 for arg in config_param_map:

814 if arg in data:

815 value = data[arg]

816 # sqlite3 do not have boolean type so we need to convert

817 # it manually to integer

818 if 'shared' in data and not data['shared']:

819 # Delete the shared server from DB if server

820 # owner uncheck shared property

821 self.delete_shared_server(server.name, gid)

822 if arg == 'sslcompression':

823 value = 1 if value else 0

824 self._update_server_details(server, sharedserver,

825 config_param_map, arg, value)

826 idx += 1

828 return idx

830 def _server_modify_disallowed_when_connected(

831 self, connected, data, disp_lbl):

833 if connected:

834 for arg in (

835 'hostaddr', 'db', 'sslmode',

836 'role', 'service'

837 ):

838 if arg in data:

839 return forbidden(

840 errmsg=gettext(

841 "'{0}' is not allowed to modify, "

842 "when server is connected."

843 ).format(disp_lbl[arg])

846 @login_required

847 def list(self, gid):

848 """

849 Return list of attributes of all servers.

850 """

851 servers = Server.query.filter(

852 or_(Server.user_id == current_user.id,

853 Server.shared),

854 Server.servergroup_id == gid).order_by(Server.name)

855 sg = ServerGroup.query.filter_by(

856 id=gid

857 ).first()

858 res = []

860 driver = get_driver(PG_DEFAULT_DRIVER)

862 for server in servers:

863 if server.shared and server.user_id != current_user.id:

864 shared_server = ServerModule.get_shared_server(server, gid)

865 server = \

866 ServerModule.get_shared_server_properties(server,

867 shared_server)

868 manager = driver.connection_manager(server.id)

869 conn = manager.connection()

870 connected = conn.connected()

872 res.append({

873 'id': server.id,

874 'name': server.name,

875 'host': server.host,

876 'port': server.port,

877 'db': server.maintenance_db,

878 'username': server.username,

879 'gid': server.servergroup_id,

880 'group-name': sg.name,

881 'comment': server.comment,

882 'role': server.role,

883 'connected': connected,

884 'version': manager.ver,

885 'server_type': manager.server_type if connected else 'pg',

886 'db_res': server.db_res.split(',') if server.db_res else None

887 })

889 return ajax_response(

890 response=res

893 @login_required

894 def properties(self, gid, sid):

895 """Return list of attributes of a server"""

897 sslcert = None

898 sslkey = None

899 sslrootcert = None

900 sslcrl = None

901 server = Server.query.filter_by(

902 id=sid).first()

904 if server is None:

905 return make_json_response(

906 status=410,

907 success=0,

908 errormsg=self.not_found_error_msg()

910 server_owner = None

911 sg = ServerGroup.query.filter_by(

912 id=server.servergroup_id

913 ).first()

915 driver = get_driver(PG_DEFAULT_DRIVER)

917 manager = driver.connection_manager(sid)

918 conn = manager.connection()

919 connected = conn.connected()

921 if server.shared and server.user_id != current_user.id:

922 shared_server = ServerModule.get_shared_server(server, gid)

923 server = ServerModule.get_shared_server_properties(server,

924 shared_server)

925 server_owner = server.server_owner

927 is_ssl = True if server.ssl_mode in self.SSL_MODES else False

929 if is_ssl:

930 sslcert = server.sslcert

931 sslkey = server.sslkey

932 sslrootcert = server.sslrootcert

933 sslcrl = server.sslcrl

935 use_ssh_tunnel = 0

936 tunnel_host = None

937 tunnel_port = 22

938 tunnel_username = None

939 tunnel_authentication = 0

941 if server.use_ssh_tunnel:

942 use_ssh_tunnel = server.use_ssh_tunnel

943 tunnel_host = server.tunnel_host

944 tunnel_port = server.tunnel_port

945 tunnel_username = server.tunnel_username

946 tunnel_authentication = server.tunnel_authentication

948 response = {

949 'id': server.id,

950 'name': server.name,

951 'server_owner': server_owner,

952 'user_id': server.user_id,

953 'host': server.host,

954 'hostaddr': server.hostaddr,

955 'port': server.port,

956 'db': server.maintenance_db,

957 'shared': server.shared if config.SERVER_MODE else None,

958 'username': server.username,

959 'gid': str(server.servergroup_id),

960 'group-name': sg.name,

961 'comment': server.comment,

962 'role': server.role,

963 'connected': connected,

964 'version': manager.ver,

965 'sslmode': server.ssl_mode,

966 'server_type': manager.server_type if connected else 'pg',

967 'bgcolor': server.bgcolor,

968 'fgcolor': server.fgcolor,

969 'db_res': server.db_res.split(',') if server.db_res else None,

970 'passfile': server.passfile if server.passfile else None,

971 'sslcert': sslcert,

972 'sslkey': sslkey,

973 'sslrootcert': sslrootcert,

974 'sslcrl': sslcrl,

975 'sslcompression': True if is_ssl and server.sslcompression

976 else False,

977 'service': server.service if server.service else None,

978 'connect_timeout':

979 server.connect_timeout if server.connect_timeout else 0,

980 'use_ssh_tunnel': use_ssh_tunnel,

981 'tunnel_host': tunnel_host,

982 'tunnel_port': tunnel_port,

983 'tunnel_username': tunnel_username,

984 'tunnel_identity_file': server.tunnel_identity_file

985 if server.tunnel_identity_file else None,

986 'tunnel_authentication': tunnel_authentication

989 return ajax_response(response)

991 @login_required

992 def create(self, gid):

993 """Add a server node to the settings database"""

994 required_args = [

995 'name',

996 'db',

997 'sslmode',

1000 data = request.form if request.form else json.loads(

1001 request.data, encoding='utf-8'

1004 # Get enc key

1005 crypt_key_present, crypt_key = get_crypt_key()

1006 if not crypt_key_present:

1007 raise CryptKeyMissing

1009 # Some fields can be provided with service file so they are optional

1010 if 'service' in data and not data['service']:

1011 required_args.extend([

1012 'host',

1013 'port',

1014 'username',

1015 'role'

1017 for arg in required_args:

1018 if arg not in data:

1019 return make_json_response(

1020 status=410,

1021 success=0,

1022 errormsg=gettext(

1023 "Could not find the required parameter ({})."

1024 ).format(arg)

1027 hostaddr = data.get('hostaddr')

1028 if hostaddr and not is_valid_ipaddress(data['hostaddr']):

1029 return make_json_response(

1030 success=0,

1031 status=400,

1032 errormsg=gettext('Not a valid Host address')

1035 # To check ssl configuration

1036 is_ssl, data = self.check_ssl_fields(data)

1038 server = None

1040 try:

1041 server = Server(

1042 user_id=current_user.id,

1043 servergroup_id=data.get('gid', gid),

1044 name=data.get('name'),

1045 host=data.get('host', None),

1046 hostaddr=hostaddr,

1047 port=data.get('port'),

1048 maintenance_db=data.get('db', None),

1049 username=data.get('username'),

1050 save_password=1 if data.get('save_password', False) and

1051 config.ALLOW_SAVE_PASSWORD else 0,

1052 ssl_mode=data.get('sslmode'),

1053 comment=data.get('comment', None),

1054 role=data.get('role', None),

1055 db_res=','.join(data['db_res'])

1056 if 'db_res' in data else None,

1057 sslcert=data.get('sslcert', None),

1058 sslkey=data.get('sslkey', None),

1059 sslrootcert=data.get('sslrootcert', None),

1060 sslcrl=data.get('sslcrl', None),

1061 sslcompression=1 if is_ssl and data['sslcompression'] else 0,

1062 bgcolor=data.get('bgcolor', None),

1063 fgcolor=data.get('fgcolor', None),

1064 service=data.get('service', None),

1065 connect_timeout=data.get('connect_timeout', 0),

1066 use_ssh_tunnel=data.get('use_ssh_tunnel', 0),

1067 tunnel_host=data.get('tunnel_host', None),

1068 tunnel_port=data.get('tunnel_port', 22),

1069 tunnel_username=data.get('tunnel_username', None),

1070 tunnel_authentication=data.get('tunnel_authentication', 0),

1071 tunnel_identity_file=data.get('tunnel_identity_file', None),

1072 shared=data.get('shared', None),

1073 passfile=data.get('passfile', None)

1075 db.session.add(server)

1076 db.session.commit()

1077 connected = False

1078 user = None

1079 manager = None

1081 if 'connect_now' in data and data['connect_now']:

1082 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(

1083 server.id)

1084 manager.update(server)

1085 conn = manager.connection()

1087 have_password = False

1088 have_tunnel_password = False

1089 password = None

1090 passfile = None

1091 tunnel_password = ''

1092 if 'password' in data and data["password"] != '':

1093 # login with password

1094 have_password = True

1095 password = data['password']

1096 password = encrypt(password, crypt_key)

1097 elif 'passfile' in data and data["passfile"] != '':

1098 passfile = data['passfile']

1099 setattr(server, 'passfile', passfile)

1100 db.session.commit()

1102 if 'tunnel_password' in data and data["tunnel_password"] != '':

1103 have_tunnel_password = True

1104 tunnel_password = data['tunnel_password']

1105 tunnel_password = \

1106 encrypt(tunnel_password, crypt_key)

1108 status, errmsg = conn.connect(

1109 password=password,

1110 passfile=passfile,

1111 tunnel_password=tunnel_password,

1112 server_types=ServerType.types()

1114 if not status:

1115 db.session.delete(server)

1116 db.session.commit()

1117 return make_json_response(

1118 status=401,

1119 success=0,

1120 errormsg=gettext(

1121 "Unable to connect to server:\n\n{}"

1122 ).format(errmsg)

1124 else:

1125 if 'save_password' in data and data['save_password'] and \

1126 have_password and config.ALLOW_SAVE_PASSWORD:

1127 setattr(server, 'password', password)

1128 db.session.commit()

1130 if 'save_tunnel_password' in data and \

1131 data['save_tunnel_password'] and \

1132 have_tunnel_password and \

1133 config.ALLOW_SAVE_TUNNEL_PASSWORD:

1134 setattr(server, 'tunnel_password', tunnel_password)

1135 db.session.commit()

1137 user = manager.user_info

1138 connected = True

1140 return jsonify(

1141 node=self.blueprint.generate_browser_node(

1142 "%d" % server.id, server.servergroup_id,

1143 server.name,

1144 server_icon_and_background(connected, manager, server),

1145 True,

1146 self.node_type,

1147 user=user,

1148 connected=connected,

1149 shared=server.shared,

1150 server_type=manager.server_type

1151 if manager and manager.server_type

1152 else 'pg',

1153 version=manager.version

1154 if manager and manager.version

1155 else None

1159 except Exception as e:

1160 if server:

1161 db.session.delete(server)

1162 db.session.commit()

1164 current_app.logger.exception(e)

1165 return make_json_response(

1166 status=410,

1167 success=0,

1168 errormsg=str(e)

1171 @login_required

1172 def sql(self, gid, sid):

1173 return make_json_response(data='')

1175 @login_required

1176 def modified_sql(self, gid, sid):

1177 return make_json_response(data='')

1179 @login_required

1180 def statistics(self, gid, sid):

1181 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1182 conn = manager.connection()

1184 if conn.connected():

1185 status, res = conn.execute_dict(

1186 render_template(

1187 "/servers/sql/#{0}#/stats.sql".format(manager.version),

1188 conn=conn, _=gettext

1192 if not status:

1193 return internal_server_error(errormsg=res)

1195 return make_json_response(data=res)

1197 return make_json_response(

1198 info=gettext(

1199 "Server has no active connection for generating statistics."

1203 @login_required

1204 def dependencies(self, gid, sid):

1205 return make_json_response(data='')

1207 @login_required

1208 def dependents(self, gid, sid):

1209 return make_json_response(data='')

1211 def supported_servers(self, **kwargs):

1212 """

1213 This property defines (if javascript) exists for this node.

1214 Override this property for your own logic.

1215 """

1217 return make_response(

1218 render_template(

1219 "servers/supported_servers.js",

1220 server_types=ServerType.types()

1222 200, {'Content-Type': MIMETYPE_APP_JS}

1225 def connect_status(self, gid, sid):

1226 """Check and return the connection status."""

1227 server = Server.query.filter_by(id=sid).first()

1228 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1229 conn = manager.connection()

1230 connected = conn.connected()

1231 in_recovery = None

1232 wal_paused = None

1233 errmsg = None

1234 if connected:

1235 status, result, in_recovery, wal_paused =\

1236 recovery_state(conn, manager.version)

1238 if not status:

1239 connected = False

1240 manager.release()

1241 errmsg = "{0} : {1}".format(server.name, result)

1243 return make_json_response(

1244 data={

1245 'icon': server_icon_and_background(connected, manager, server),

1246 'connected': connected,

1247 'in_recovery': in_recovery,

1248 'wal_pause': wal_paused,

1249 'server_type': manager.server_type if connected else "pg",

1250 'user': manager.user_info if connected else None,

1251 'errmsg': errmsg

1255 def connect(self, gid, sid, user_name=None):

1256 """

1257 Connect the Server and return the connection object.

1258 Verification Process before Connection:

1259 Verify requested server.

1261 Check the server password is already been stored in the

1262 database or not.

1263 If Yes, connect the server and return connection.

1264 If No, Raise HTTP error and ask for the password.

1266 In case of 'Save Password' request from user, excrypted Pasword

1267 will be stored in the respected server database and

1268 establish the connection OR just connect the server and do not

1269 store the password.

1270 """

1271 current_app.logger.info(

1272 'Connection Request for server#{0}'.format(sid)

1275 # Fetch Server Details

1276 server = Server.query.filter_by(id=sid).first()

1277 shared_server = None

1278 if server.shared and server.user_id != current_user.id:

1279 shared_server = ServerModule.get_shared_server(server, gid)

1280 server = ServerModule.get_shared_server_properties(server,

1281 shared_server)

1282 if server is None:

1283 return bad_request(self.not_found_error_msg())

1285 # Return if username is blank and the server is shared

1286 if server.username is None and not server.service and \

1287 server.shared:

1288 return make_json_response(

1289 status=200,

1290 success=0,

1291 errormsg=gettext(

1292 u"Please enter the server details to connect")

1294 if current_user and hasattr(current_user, 'id'):

1295 # Fetch User Details.

1296 user = User.query.filter_by(id=current_user.id).first()

1297 if user is None:

1298 return unauthorized(gettext(UNAUTH_REQ))

1299 else:

1300 return unauthorized(gettext(UNAUTH_REQ))

1302 data = {}

1303 if request.form:

1304 data = request.form

1305 elif request.data:

1306 data = json.loads(request.data, encoding='utf-8')

1308 password = None

1309 passfile = None

1310 tunnel_password = None

1311 save_password = False

1312 save_tunnel_password = False

1313 prompt_password = False

1314 prompt_tunnel_password = False

1316 # Connect the Server

1317 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1318 if not manager.connection().connected():

1319 manager.update(server)

1320 conn = manager.connection()

1322 # Get enc key

1323 crypt_key_present, crypt_key = get_crypt_key()

1324 if not crypt_key_present:

1325 raise CryptKeyMissing

1327 # If server using SSH Tunnel

1328 if server.use_ssh_tunnel:

1329 if 'tunnel_password' not in data:

1330 if server.tunnel_password is None:

1331 prompt_tunnel_password = True

1332 else:

1333 tunnel_password = server.tunnel_password

1334 else:

1335 tunnel_password = data['tunnel_password'] \

1336 if 'tunnel_password' in data else ''

1337 save_tunnel_password = data['save_tunnel_password'] \

1338 if tunnel_password and 'save_tunnel_password' in data \

1339 else False

1340 # Encrypt the password before saving with user's login

1341 # password key.

1342 try:

1343 tunnel_password = encrypt(tunnel_password, crypt_key) \

1344 if tunnel_password is not None else \

1345 server.tunnel_password

1346 except Exception as e:

1347 current_app.logger.exception(e)

1348 return internal_server_error(errormsg=str(e))

1349 if 'password' not in data:

1350 conn_passwd = getattr(conn, 'password', None)

1351 if conn_passwd is None and not server.save_password and \

1352 server.passfile is None and server.service is None:

1353 prompt_password = True

1354 elif server.passfile and server.passfile != '':

1355 passfile = server.passfile

1356 else:

1357 password = conn_passwd or server.password

1358 else:

1359 password = data['password'] if 'password' in data else None

1360 save_password = data['save_password']\

1361 if 'save_password' in data else False

1363 # Encrypt the password before saving with user's login

1364 # password key.

1365 try:

1366 password = encrypt(password, crypt_key) \

1367 if password is not None else server.password

1368 except Exception as e:

1369 current_app.logger.exception(e)

1370 return internal_server_error(errormsg=str(e))

1372 # Check do we need to prompt for the database server or ssh tunnel

1373 # password or both. Return the password template in case password is

1374 # not provided, or password has not been saved earlier.

1375 if prompt_password or prompt_tunnel_password:

1376 return self.get_response_for_password(server, 428, prompt_password,

1377 prompt_tunnel_password,

1378 user=user_name)

1380 status = True

1381 try:

1382 status, errmsg = conn.connect(

1383 password=password,

1384 passfile=passfile,

1385 tunnel_password=tunnel_password,

1386 server_types=ServerType.types()

1388 except OperationalError as e:

1389 return internal_server_error(errormsg=str(e))

1390 except Exception as e:

1391 current_app.logger.exception(e)

1392 return self.get_response_for_password(

1393 server, 401, True, True, getattr(e, 'message', str(e)))

1395 if not status:

1397 current_app.logger.error(

1398 "Could not connect to server(#{0}) - '{1}'.\nError: {2}"

1399 .format(server.id, server.name, errmsg)

1401 return self.get_response_for_password(server, 401, True,

1402 True, errmsg)

1403 else:

1404 if save_password and config.ALLOW_SAVE_PASSWORD:

1405 try:

1406 # If DB server is running in trust mode then password may

1407 # not be available but we don't need to ask password

1408 # every time user try to connect

1409 # 1 is True in SQLite as no boolean type

1410 setattr(server, 'save_password', 1)

1411 if server.shared and server.user_id != current_user.id:

1412 setattr(shared_server, 'save_password', 1)

1413 else:

1414 setattr(server, 'save_password', 1)

1416 # Save the encrypted password using the user's login

1417 # password key, if there is any password to save

1418 if password:

1419 if server.shared and server.user_id != current_user.id:

1420 setattr(shared_server, 'password', password)

1421 else:

1422 setattr(server, 'password', password)

1423 db.session.commit()

1424 except Exception as e:

1425 # Release Connection

1426 current_app.logger.exception(e)

1427 manager.release(database=server.maintenance_db)

1428 conn = None

1430 return internal_server_error(errormsg=e.message)

1432 if save_tunnel_password and config.ALLOW_SAVE_TUNNEL_PASSWORD:

1433 try:

1434 # Save the encrypted tunnel password.

1435 setattr(server, 'tunnel_password', tunnel_password)

1436 db.session.commit()

1437 except Exception as e:

1438 # Release Connection

1439 current_app.logger.exception(e)

1440 manager.release(database=server.maintenance_db)

1441 conn = None

1443 return internal_server_error(errormsg=e.message)

1445 current_app.logger.info('Connection Established for server: \

1446 %s - %s' % (server.id, server.name))

1447 # Update the recovery and wal pause option for the server

1448 # if connected successfully

1449 _, _, in_recovery, wal_paused =\

1450 recovery_state(conn, manager.version)

1452 return make_json_response(

1453 success=1,

1454 info=gettext("Server connected."),

1455 data={

1456 'icon': server_icon_and_background(True, manager, server),

1457 'connected': True,

1458 'server_type': manager.server_type,

1459 'type': manager.server_type,

1460 'version': manager.version,

1461 'db': manager.db,

1462 'user': manager.user_info,

1463 'in_recovery': in_recovery,

1464 'wal_pause': wal_paused,

1465 'is_password_saved': bool(server.save_password),

1466 'is_tunnel_password_saved': True

1467 if server.tunnel_password is not None else False,

1471 def disconnect(self, gid, sid):

1472 """Disconnect the Server."""

1474 server = Server.query.filter_by(id=sid).first()

1475 if server is None:

1476 return bad_request(self.not_found_error_msg())

1478 # Release Connection

1479 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1481 status = manager.release()

1483 if not status:

1484 return unauthorized(gettext("Server could not be disconnected."))

1485 else:

1486 return make_json_response(

1487 success=1,

1488 info=gettext("Server disconnected."),

1489 data={

1490 'icon': server_icon_and_background(False, manager, server),

1491 'connected': False

1495 def reload_configuration(self, gid, sid):

1496 """Reload the server configuration"""

1498 # Reload the server configurations

1499 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1500 conn = manager.connection()

1502 if conn.connected():

1503 # Execute the command for reload configuration for the server

1504 status, rid = conn.execute_scalar("SELECT pg_reload_conf();")

1506 if not status:

1507 return internal_server_error(

1508 gettext("Could not reload the server configuration.")

1510 else:

1511 return make_json_response(data={

1512 'status': True,

1513 'result': gettext('Server configuration reloaded.')

1516 else:

1517 return make_json_response(data={

1518 'status': False,

1519 'result': SERVER_CONNECTION_CLOSED})

1521 def create_restore_point(self, gid, sid):

1522 """

1523 This method will creates named restore point

1525 Args:

1526 gid: Server group ID

1527 sid: Server ID

1529 Returns:

1530 None

1531 """

1532 try:

1533 data = request.form

1534 restore_point_name = data['value'] if data else None

1535 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1536 conn = manager.connection()

1538 # Execute SQL to create named restore point

1539 if conn.connected():

1540 if restore_point_name:

1541 status, res = conn.execute_scalar(

1542 "SELECT pg_create_restore_point('{0}');".format(

1543 restore_point_name

1546 if not status:

1547 return internal_server_error(

1548 errormsg=str(res)

1551 return make_json_response(

1552 data={

1553 'status': 1,

1554 'result': gettext(

1555 'Named restore point created: {0}').format(

1556 restore_point_name)

1559 except Exception as e:

1560 current_app.logger.error(gettext(

1561 'Named restore point creation failed ({0})').format(

1562 str(e))

1564 return internal_server_error(errormsg=str(e))

1566 def change_password(self, gid, sid):

1567 """

1568 This function is used to change the password of the

1569 Database Server.

1571 Args:

1572 gid: Group id

1573 sid: Server id

1574 """

1575 try:

1576 data = json.loads(request.form['data'], encoding='utf-8')

1577 crypt_key = get_crypt_key()[1]

1579 # Fetch Server Details

1580 server = Server.query.filter_by(id=sid).first()

1581 if server is None:

1582 return bad_request(self.not_found_error_msg())

1584 # Fetch User Details.

1585 user = User.query.filter_by(id=current_user.id).first()

1586 if user is None:

1587 return unauthorized(gettext(UNAUTH_REQ))

1589 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1590 conn = manager.connection()

1591 is_passfile = False

1593 # If there is no password found for the server

1594 # then check for pgpass file

1595 if not server.password and not manager.password and \

1596 server.passfile and manager.passfile and \

1597 server.passfile == manager.passfile:

1598 is_passfile = True

1600 # Check for password only if there is no pgpass file used

1601 if not is_passfile and data and \

1602 ('password' not in data or data['password'] == ''):

1603 return make_json_response(

1604 status=400,

1605 success=0,

1606 errormsg=gettext(

1607 "Could not find the required parameter(s)."

1611 if data and ('newPassword' not in data or

1612 data['newPassword'] == '' or

1613 'confirmPassword' not in data or

1614 data['confirmPassword'] == ''):

1615 return make_json_response(

1616 status=400,

1617 success=0,

1618 errormsg=gettext(

1619 "Could not find the required parameter(s)."

1623 if data['newPassword'] != data['confirmPassword']:

1624 return make_json_response(

1625 status=200,

1626 success=0,

1627 errormsg=gettext(

1628 "Passwords do not match."

1632 # Check against old password only if no pgpass file

1633 if not is_passfile:

1634 decrypted_password = decrypt(manager.password, crypt_key)

1636 if isinstance(decrypted_password, bytes):

1637 decrypted_password = decrypted_password.decode()

1639 password = data['password']

1641 # Validate old password before setting new.

1642 if password != decrypted_password:

1643 return unauthorized(gettext("Incorrect password."))

1645 # Hash new password before saving it.

1646 if manager.sversion >= 100000:

1647 password = conn.pq_encrypt_password_conn(data['newPassword'],

1648 manager.user)

1649 if password is None:

1650 # Unable to encrypt the password so used the

1651 # old method of encryption

1652 password = pqencryptpassword(data['newPassword'],

1653 manager.user)

1654 else:

1655 password = pqencryptpassword(data['newPassword'], manager.user)

1657 SQL = render_template(

1658 "/servers/sql/#{0}#/change_password.sql".format(

1659 manager.version),

1660 conn=conn, _=gettext,

1661 user=manager.user, encrypted_password=password)

1663 status, res = conn.execute_scalar(SQL)

1665 if not status:

1666 return internal_server_error(errormsg=res)

1668 # Store password in sqlite only if no pgpass file

1669 if not is_passfile:

1670 password = encrypt(data['newPassword'], crypt_key)

1671 # Check if old password was stored in pgadmin4 sqlite database.

1672 # If yes then update that password.

1673 if server.password is not None and config.ALLOW_SAVE_PASSWORD:

1674 setattr(server, 'password', password)

1675 db.session.commit()

1676 # Also update password in connection manager.

1677 manager.password = password

1678 manager.update_session()

1680 return make_json_response(

1681 status=200,

1682 success=1,

1683 info=gettext(

1684 "Password changed successfully."

1688 except Exception as e:

1689 return internal_server_error(errormsg=str(e))

1691 def wal_replay(self, sid, pause=True):

1692 """

1693 Utility function for wal_replay for resume/pause.

1694 """

1695 server = Server.query.filter_by(

1696 user_id=current_user.id, id=sid

1697 ).first()

1699 if server is None:

1700 return make_json_response(

1701 success=0,

1702 errormsg=self.not_found_error_msg()

1705 try:

1706 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1707 conn = manager.connection()

1709 # Execute SQL to pause or resume WAL replay

1710 if conn.connected():

1711 if pause:

1712 sql = "SELECT pg_xlog_replay_pause();"

1713 if manager.version >= 100000:

1714 sql = "SELECT pg_wal_replay_pause();"

1716 status, res = conn.execute_scalar(sql)

1717 if not status:

1718 return internal_server_error(

1719 errormsg=str(res)

1721 else:

1722 sql = "SELECT pg_xlog_replay_resume();"

1723 if manager.version >= 100000:

1724 sql = "SELECT pg_wal_replay_resume();"

1726 status, res = conn.execute_scalar(sql)

1727 if not status:

1728 return internal_server_error(

1729 errormsg=str(res)

1731 return make_json_response(

1732 success=1,

1733 info=gettext('WAL replay paused'),

1734 data={'in_recovery': True, 'wal_pause': pause}

1736 return gone(errormsg=gettext('Please connect the server.'))

1737 except Exception as e:

1738 current_app.logger.error(

1739 'WAL replay pause/resume failed'

1741 return internal_server_error(errormsg=str(e))

1743 def resume_wal_replay(self, gid, sid):

1744 """

1745 This method will resume WAL replay

1747 Args:

1748 gid: Server group ID

1749 sid: Server ID

1751 Returns:

1752 None

1753 """

1754 return self.wal_replay(sid, False)

1756 def pause_wal_replay(self, gid, sid):

1757 """

1758 This method will pause WAL replay

1760 Args:

1761 gid: Server group ID

1762 sid: Server ID

1764 Returns:

1765 None

1766 """

1767 return self.wal_replay(sid, True)

1769 def check_pgpass(self, gid, sid):

1770 """

1771 This function is used to check whether server is connected

1772 using pgpass file or not

1774 Args:

1775 gid: Group id

1776 sid: Server id

1777 """

1778 is_pgpass = False

1779 server = Server.query.filter_by(

1780 user_id=current_user.id, id=sid

1781 ).first()

1783 if server is None:

1784 return make_json_response(

1785 success=0,

1786 errormsg=self.not_found_error_msg()

1789 try:

1790 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)

1791 conn = manager.connection()

1792 if not conn.connected():

1793 return gone(

1794 errormsg=gettext('Please connect the server.')

1797 if (not server.password or not manager.password) and \

1798 server.passfile and manager.passfile and \

1799 server.passfile == manager.passfile:

1800 is_pgpass = True

1801 return make_json_response(

1802 success=1,

1803 data=dict({'is_pgpass': is_pgpass}),

1805 except Exception as e:

1806 current_app.logger.error(

1807 'Cannot able to fetch pgpass status'

1809 return internal_server_error(errormsg=str(e))

1811 def get_response_for_password(self, server, status, prompt_password=False,

1812 prompt_tunnel_password=False, errmsg=None,

1813 user=None):

1815 if server.use_ssh_tunnel:

1816 return make_json_response(

1817 success=0,

1818 status=status,

1819 result=render_template(

1820 'servers/tunnel_password.html',

1821 server_label=server.name,

1822 username=server.username,

1823 tunnel_username=server.tunnel_username,

1824 tunnel_host=server.tunnel_host,

1825 tunnel_identity_file=server.tunnel_identity_file,

1826 errmsg=errmsg,

1827 _=gettext,

1828 service=server.service,

1829 prompt_tunnel_password=prompt_tunnel_password,

1830 prompt_password=prompt_password,

1831 allow_save_password=True if

1832 config.ALLOW_SAVE_PASSWORD and

1833 session['allow_save_password'] else False,

1834 allow_save_tunnel_password=True if

1835 config.ALLOW_SAVE_TUNNEL_PASSWORD and

1836 session['allow_save_password'] else False

1839 else:

1840 return make_json_response(

1841 success=0,

1842 status=status,

1843 result=render_template(

1844 'servers/password.html',

1845 server_label=server.name,

1846 username=user if user else server.username,

1847 errmsg=errmsg,

1848 service=server.service,

1849 _=gettext,

1850 allow_save_password=True if

1851 config.ALLOW_SAVE_PASSWORD and

1852 session['allow_save_password'] else False,

1856 def clear_saved_password(self, gid, sid):

1857 """

1858 This function is used to remove database server password stored into

1859 the pgAdmin's db file.

1861 :param gid:

1862 :param sid:

1863 :return:

1864 """

1865 try:

1866 server = Server.query.filter_by(id=sid).first()

1867 shared_server = None

1868 if server is None:

1869 return make_json_response(

1870 success=0,

1871 info=self.not_found_error_msg()

1874 if server.shared and server.user_id != current_user.id:

1875 shared_server = SharedServer.query.filter_by(

1876 name=server.name, user_id=current_user.id,

1877 servergroup_id=gid).first()

1879 if shared_server is None:

1880 return make_json_response(

1881 success=0,

1882 info=gettext("Could not find the required server.")

1884 server = ServerModule. \

1885 get_shared_server_properties(server, shared_server)

1887 if server.shared and server.user_id != current_user.id:

1888 setattr(shared_server, 'save_password', None)

1889 else:

1890 setattr(server, 'save_password', None)

1892 # If password was saved then clear the flag also

1893 # 0 is False in SQLite db

1894 if server.save_password:

1895 if server.shared and server.user_id != current_user.id:

1896 setattr(shared_server, 'save_password', 0)

1897 else:

1898 setattr(server, 'save_password', 0)

1899 db.session.commit()

1900 except Exception as e:

1901 current_app.logger.error(

1902 "Unable to clear saved password.\nError: {0}".format(str(e))

1905 return internal_server_error(errormsg=str(e))

1907 return make_json_response(

1908 success=1,

1909 info=gettext("The saved password cleared successfully."),

1910 data={'is_password_saved': False}

1913 def clear_sshtunnel_password(self, gid, sid):

1914 """

1915 This function is used to remove sshtunnel password stored into

1916 the pgAdmin's db file.

1918 :param gid:

1919 :param sid:

1920 :return:

1921 """

1922 try:

1923 server = Server.query.filter_by(id=sid).first()

1924 if server is None:

1925 return make_json_response(

1926 success=0,

1927 info=self.not_found_error_msg()

1930 setattr(server, 'tunnel_password', None)

1931 db.session.commit()

1932 except Exception as e:

1933 current_app.logger.error(

1934 "Unable to clear ssh tunnel password."

1935 "\nError: {0}".format(str(e))

1938 return internal_server_error(errormsg=str(e))

1940 return make_json_response(

1941 success=1,

1942 info=gettext("The saved password cleared successfully."),

1943 data={'is_tunnel_password_saved': False}

1947SchemaDiffRegistry(blueprint.node_type, ServerNode)

1948ServerNode.register_node_view(blueprint)