Coverage for /Users/akshayjoshi/Developement/pgAdmin4/web/pgadmin/browser/server_groups/servers/__init__.py : 55%
![Show keyboard shortcuts](keybd_closed.png)
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
104 )
106 if is_connected:
107 return 'icon-{0}{1}'.format(
108 manager.server_type, server_background_color
109 )
110 elif server.shared and config.SERVER_MODE:
111 return 'icon-shared-server-not-connected{0}'.format(
112 server_background_color
113 )
114 else:
115 return 'icon-server-not-connected{0}'.format(
116 server_background_color
117 )
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
257 )
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
370 )
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'
465 ]
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
484 )
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
550 )
551 )
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)
578 )
579 )
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 ),
618 )
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?'
655 )
656 )
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.")
687 )
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'
723 }
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')
734 }
736 idx = 0
737 data = request.form if request.form else json.loads(
738 request.data, encoding='utf-8'
739 )
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')
749 )
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.')
765 )
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
774 )
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
798 )
799 )
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])
844 )
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
891 )
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()
909 )
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
987 }
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',
998 ]
1000 data = request.form if request.form else json.loads(
1001 request.data, encoding='utf-8'
1002 )
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'
1016 ])
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)
1025 )
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')
1033 )
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)
1074 )
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()
1113 )
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)
1123 )
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
1156 )
1157 )
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)
1169 )
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
1189 )
1190 )
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."
1200 )
1201 )
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()
1221 ),
1222 200, {'Content-Type': MIMETYPE_APP_JS}
1223 )
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
1252 }
1253 )
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)
1273 )
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")
1293 )
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()
1387 )
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)
1400 )
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,
1468 }
1469 )
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
1492 }
1493 )
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.")
1509 )
1510 else:
1511 return make_json_response(data={
1512 'status': True,
1513 'result': gettext('Server configuration reloaded.')
1514 })
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
1544 )
1545 )
1546 if not status:
1547 return internal_server_error(
1548 errormsg=str(res)
1549 )
1551 return make_json_response(
1552 data={
1553 'status': 1,
1554 'result': gettext(
1555 'Named restore point created: {0}').format(
1556 restore_point_name)
1557 })
1559 except Exception as e:
1560 current_app.logger.error(gettext(
1561 'Named restore point creation failed ({0})').format(
1562 str(e))
1563 )
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)."
1608 )
1609 )
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)."
1620 )
1621 )
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."
1629 )
1630 )
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."
1685 )
1686 )
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()
1703 )
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)
1720 )
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)
1730 )
1731 return make_json_response(
1732 success=1,
1733 info=gettext('WAL replay paused'),
1734 data={'in_recovery': True, 'wal_pause': pause}
1735 )
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'
1740 )
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()
1787 )
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.')
1795 )
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}),
1804 )
1805 except Exception as e:
1806 current_app.logger.error(
1807 'Cannot able to fetch pgpass status'
1808 )
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
1837 )
1838 )
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,
1853 )
1854 )
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()
1872 )
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.")
1883 )
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))
1903 )
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}
1911 )
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()
1928 )
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))
1936 )
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}
1944 )
1947SchemaDiffRegistry(blueprint.node_type, ServerNode)
1948ServerNode.register_node_view(blueprint)