def create_or_update_server(user, server_dict):
group = server_dict.pop("group")
name = server_dict.pop("name")
password = server_dict.pop("password")
group_id = get_or_create_group_id(group, user)
base = {"user_id": user.id, "servergroup_id": group_id, "name": name}
server = Server.query.filter_by(**base).first()
if server is None:
server = Server(**base)
print(
"Created server {!r} in group {!r} for user {!r}".format(
name, group, user.email
)
)
db.session.add(server)
server.password = encrypt(password, user.password)
server.discovery_id = DISCOVERY_ID
for attr, value in server_dict.items():
setattr(server, attr, value)
commit(
"Error updating server {} in group {}".format(name, group),
"Successfully saved.",
)
return server
def create(self, gid):
"""Add a server node to the settings database"""
required_args = [
u'name', u'host', u'port', u'db', u'username', u'sslmode', u'role'
]
data = request.form if request.form else json.loads(
request.data.decode())
for arg in required_args:
if arg not in data:
return make_json_response(
status=410,
success=0,
errormsg=gettext(
"Couldn't find the required parameter (%s)." % arg))
try:
server = Server(
user_id=current_user.id,
servergroup_id=gid,
name=data[u'name'],
host=data[u'host'],
port=data[u'port'],
maintenance_db=data[u'db'],
username=data[u'username'],
ssl_mode=data[u'sslmode'],
comment=data[u'comment'] if u'comment' in data else None,
role=data[u'role'] if u'role' in data else None)
db.session.add(server)
db.session.commit()
return jsonify(node=self.blueprint.generate_browser_node(
"%d" % (server.id),
gid,
server.name,
"icon-server-not-connected",
True,
self.node_type,
connected=False,
server_type='pg' # Default server type
))
except Exception as e:
current_app.logger.exception(e)
return make_json_response(status=410,
success=0,
errormsg=e.message)
def _create_server(data):
"""Create Server"""
server = Server(
user_id=current_user.id,
servergroup_id=data.get('gid'),
name=data.get('name'),
maintenance_db=data.get('db'),
username=data.get('username'),
ssl_mode='prefer',
cloud_status=data.get('cloud_status'),
connect_timeout=30,
)
db.session.add(server)
db.session.commit()
return server.id
def add_server(user_id, servergroup_id, name, superuser, port,
discovery_id, comment):
# Create a server object if needed, and store it.
servers = Server.query.filter_by(
user_id=user_id, discovery_id=svr_discovery_id).order_by("id")
if servers.count() > 0:
return
svr = Server(user_id=user_id,
servergroup_id=servergroup_id,
name=name,
host='localhost',
port=port,
maintenance_db='postgres',
username=superuser,
ssl_mode='prefer',
comment=svr_comment,
discovery_id=discovery_id)
db.session.add(svr)
db.session.commit()
def create(self, gid):
"""Add a server node to the settings database"""
required_args = [
u'name', u'host', u'port', u'db', u'username', u'sslmode', u'role'
]
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
for arg in required_args:
if arg not in data:
return make_json_response(
status=410,
success=0,
errormsg=gettext(
"Could not find the required parameter (%s)." % arg))
if 'hostaddr' in data and data['hostaddr'] != '':
if not self.pat4.match(data['hostaddr']):
if not self.pat6.match(data['hostaddr']):
return make_json_response(
success=0,
status=400,
errormsg=gettext('Host address not valid'))
# To check ssl configuration
is_ssl, data = self.check_ssl_fields(data)
server = None
try:
server = Server(
user_id=current_user.id,
servergroup_id=data[u'gid'] if u'gid' in data else gid,
name=data[u'name'],
host=data[u'host'],
hostaddr=data[u'hostaddr'] if u'hostaddr' in data else None,
port=data[u'port'],
maintenance_db=data[u'db'],
username=data[u'username'],
ssl_mode=data[u'sslmode'],
comment=data[u'comment'] if u'comment' in data else None,
role=data[u'role'] if u'role' in data else None,
db_res=','.join(data[u'db_res'])
if u'db_res' in data else None,
sslcert=data['sslcert'] if is_ssl else None,
sslkey=data['sslkey'] if is_ssl else None,
sslrootcert=data['sslrootcert'] if is_ssl else None,
sslcrl=data['sslcrl'] if is_ssl else None,
sslcompression=1 if is_ssl and data['sslcompression'] else 0)
db.session.add(server)
db.session.commit()
connected = False
user = None
manager = None
if 'connect_now' in data and data['connect_now']:
manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
server.id)
manager.update(server)
conn = manager.connection()
have_password = False
if 'password' in data and data["password"] != '':
# login with password
have_password = True
passfile = None
password = data['password']
password = encrypt(password, current_user.password)
elif 'passfile' in data and data["passfile"] != '':
passfile = data['passfile']
setattr(server, 'passfile', passfile)
db.session.commit()
else:
# Attempt password less login
password = None
passfile = None
status, errmsg = conn.connect(password=password,
passfile=passfile,
server_types=ServerType.types())
if hasattr(str, 'decode') and errmsg is not None:
errmsg = errmsg.decode('utf-8')
if not status:
db.session.delete(server)
db.session.commit()
return make_json_response(
status=401,
success=0,
errormsg=gettext(
u"Unable to connect to server:\n\n%s" % errmsg))
else:
if 'save_password' in data and data['save_password'] and \
have_password and config.ALLOW_SAVE_PASSWORD:
setattr(server, 'password', password)
db.session.commit()
user = manager.user_info
connected = True
return jsonify(node=self.blueprint.generate_browser_node(
"%d" % server.id,
server.servergroup_id,
server.name,
'icon-{0}'.format(manager.server_type)
if manager and manager.server_type else "icon-pg",
True,
self.node_type,
user=user,
connected=connected,
server_type=manager.
server_type if manager and manager.server_type else 'pg'))
except Exception as e:
if server:
db.session.delete(server)
db.session.commit()
current_app.logger.exception(e)
return make_json_response(status=410, success=0, errormsg=str(e))
def load_database_servers(input_file, selected_servers,
load_user=current_user, from_setup=False):
"""Load server groups and servers.
"""
user = _does_user_exist(load_user, from_setup)
if user is None:
return False, USER_NOT_FOUND % load_user
# retrieve storage directory path
storage_manager_path = None
if not from_setup:
storage_manager_path = get_storage_directory(user)
# generate full path of file
file_path = unquote(input_file)
if storage_manager_path:
# generate full path of file
file_path = os.path.join(
storage_manager_path,
file_path.lstrip('/').lstrip('\\')
)
try:
with open(file_path) as f:
data = json.load(f)
except json.decoder.JSONDecodeError as e:
return _handle_error(gettext("Error parsing input file %s: %s" %
(file_path, e)), from_setup)
except Exception as e:
return _handle_error(gettext("Error reading input file %s: [%d] %s" %
(file_path, e.errno, e.strerror)), from_setup)
f.close()
user_id = user.id
# Counters
groups_added = 0
servers_added = 0
# Get the server groups
groups = ServerGroup.query.filter_by(user_id=user_id)
# Validate server data
error_msg = validate_json_data(data, user.has_role("Administrator"))
if error_msg is not None and from_setup:
print(ADD_SERVERS_MSG % (groups_added, servers_added))
return _handle_error(error_msg, from_setup)
for server in data["Servers"]:
if selected_servers is None or str(server) in selected_servers:
obj = data["Servers"][server]
# Get the group. Create if necessary
group_id = next(
(g.id for g in groups if g.name == obj["Group"]), -1)
if group_id == -1:
new_group = ServerGroup()
new_group.name = obj["Group"]
new_group.user_id = user_id
db.session.add(new_group)
try:
db.session.commit()
except Exception as e:
if from_setup:
print(ADD_SERVERS_MSG % (groups_added, servers_added))
return _handle_error(
gettext("Error creating server group '%s': %s" %
(new_group.name, e)), from_setup)
group_id = new_group.id
groups_added = groups_added + 1
groups = ServerGroup.query.filter_by(user_id=user_id)
# Create the server
new_server = Server()
new_server.name = obj["Name"]
new_server.servergroup_id = group_id
new_server.user_id = user_id
new_server.ssl_mode = obj["SSLMode"]
new_server.maintenance_db = obj["MaintenanceDB"]
new_server.host = obj.get("Host", None)
new_server.hostaddr = obj.get("HostAddr", None)
new_server.port = obj.get("Port", None)
new_server.username = obj.get("Username", None)
new_server.role = obj.get("Role", None)
new_server.ssl_mode = obj["SSLMode"]
new_server.comment = obj.get("Comment", None)
new_server.db_res = obj.get("DBRestriction", None)
new_server.passfile = obj.get("PassFile", None)
new_server.sslcert = obj.get("SSLCert", None)
new_server.sslkey = obj.get("SSLKey", None)
new_server.sslrootcert = obj.get("SSLRootCert", None)
new_server.sslcrl = obj.get("SSLCrl", None)
new_server.sslcompression = obj.get("SSLCompression", None)
new_server.bgcolor = obj.get("BGColor", None)
new_server.fgcolor = obj.get("FGColor", None)
new_server.service = obj.get("Service", None)
new_server.connect_timeout = obj.get("Timeout", None)
new_server.use_ssh_tunnel = obj.get("UseSSHTunnel", None)
new_server.tunnel_host = obj.get("TunnelHost", None)
new_server.tunnel_port = obj.get("TunnelPort", None)
new_server.tunnel_username = obj.get("TunnelUsername", None)
new_server.tunnel_authentication = \
obj.get("TunnelAuthentication", None)
new_server.shared = \
obj.get("Shared", None)
db.session.add(new_server)
try:
db.session.commit()
except Exception as e:
if from_setup:
print(ADD_SERVERS_MSG % (groups_added, servers_added))
return _handle_error(gettext("Error creating server '%s': %s" %
(new_server.name, e)), from_setup)
servers_added = servers_added + 1
msg = ADD_SERVERS_MSG % (groups_added, servers_added)
print(msg)
return True, msg
def add_to_pginstances(pg_arg):
server_id = None
try:
component_name = pg_arg.get("component")
component_port = pg_arg.get("port", 5432)
component_host = pg_arg.get("host", "localhost")
component_proj = pg_arg.get("project")
component_db = pg_arg.get("db", "postgres")
component_user = pg_arg.get("user", "postgres")
gid = pg_arg.get("gid")
sid = pg_arg.get("sid")
servergroup_id = 1
is_rds = pg_arg.get("rds")
is_new = True
discovery_id = "BigSQL PostgreSQL"
if is_rds:
discovery_id = "RDS"
servername = component_name
server_group_name = pg_arg.get("region", "AWS RDS")
rds_serverGroup = ServerGroup.query.filter_by(
user_id=current_user.id,
name=server_group_name).order_by("id")
if rds_serverGroup.count() > 0:
servergroup = rds_serverGroup.first()
servergroup_id = servergroup.id
else:
try:
sg = ServerGroup(user_id=current_user.id,
name=server_group_name)
db.session.add(sg)
db.session.commit()
servergroup_id = sg.id
except sqlite3.IntegrityError as e:
err_msg = str(e)
if err_msg.find("UNIQUE constraint failed") >= 0:
rds_serverGroup = ServerGroup.query.filter_by(
user_id=current_user.id,
name=server_group_name).order_by("id")
if rds_serverGroup.count() > 0:
servergroup = rds_serverGroup.first()
servergroup_id = servergroup.id
else:
print(err_msg)
result = {}
result['error'] = 1
result['msg'] = err_msg
return result
else:
if gid:
servername = component_name
servergroup_id = gid
if sid:
component_server = Server.query.filter_by(
id=sid,
user_id=current_user.id,
).first()
is_new = False
else:
servername = "{0}({1})".format(component_name,
component_host)
if component_host in ("localhost", ""):
component_host = "localhost"
servername = "{0}({1})".format(
component_name, component_host)
else:
import util
host_info = util.get_pgc_host(component_host)
component_host = host_info.get('host')
if component_host == '':
component_host = pg_arg.get("host", "localhost")
user_id = current_user.id
servergroups = ServerGroup.query.filter_by(
user_id=user_id).order_by("id")
if servergroups.count() > 0:
servergroup = servergroups.first()
servergroup_id = servergroup.id
else:
sg = ServerGroup(user_id=current_user.id,
name="Servers")
db.session.add(sg)
db.session.commit()
servergroup_id = sg.id
component_server = Server.query.filter_by(
name=servername,
host=component_host,
servergroup_id=servergroup_id,
port=component_port).first()
if component_server:
is_new = False
else:
is_new = True
if is_new:
svr = Server(user_id=current_user.id,
servergroup_id=servergroup_id,
name=servername,
host=component_host,
port=component_port,
maintenance_db=component_db,
username=component_user,
ssl_mode='prefer',
comment=component_proj,
discovery_id=discovery_id)
db_session.add(svr)
db_session.commit()
server_id = svr.id
else:
component_server.servergroup_id = servergroup_id
component_server.name = servername
component_server.host = component_host
component_server.port = component_port
component_server.maintenance_db = component_db
component_server.username = component_user
db_session.commit()
except Exception as e:
print("Failed while adding/updating pg instance in metadata :")
print(str(e))
pass
return server_id
def load_servers(args):
"""Load server groups and servers.
Args:
args (ArgParser): The parsed command line options
"""
# What user?
load_user = args.user if args.user is not None else config.DESKTOP_USER
# And the sqlite path
if args.sqlite_path is not None:
config.SQLITE_PATH = args.sqlite_path
print('----------')
print('Loading servers with:')
print('User:'******'SQLite pgAdmin config:', config.SQLITE_PATH)
print('----------')
try:
with open(args.load_servers) as f:
data = json.load(f)
except json.decoder.JSONDecodeError as e:
print("Error parsing input file %s: %s" %
(args.load_servers, e))
sys.exit(1)
except Exception as e:
print("Error reading input file %s: [%d] %s" %
(args.load_servers, e.errno, e.strerror))
sys.exit(1)
f.close()
app = create_app(config.APP_NAME + '-cli')
with app.app_context():
user = User.query.filter_by(email=load_user).first()
if user is None:
print("The specified user ID (%s) could not be found." %
load_user)
sys.exit(1)
user_id = user.id
# Counters
groups_added = 0
servers_added = 0
# Get the server groups
groups = ServerGroup.query.filter_by(user_id=user_id)
def print_summary():
print("Added %d Server Group(s) and %d Server(s)." %
(groups_added, servers_added))
err_msg = _validate_servers_data(data, user.has_role("Administrator"))
if err_msg is not None:
print(err_msg)
print_summary()
sys.exit(1)
for server in data["Servers"]:
obj = data["Servers"][server]
# Get the group. Create if necessary
group_id = next(
(g.id for g in groups if g.name == obj["Group"]), -1)
if group_id == -1:
new_group = ServerGroup()
new_group.name = obj["Group"]
new_group.user_id = user_id
db.session.add(new_group)
try:
db.session.commit()
except Exception as e:
print("Error creating server group '%s': %s" %
(new_group.name, e))
print_summary()
sys.exit(1)
group_id = new_group.id
groups_added = groups_added + 1
groups = ServerGroup.query.filter_by(user_id=user_id)
# Create the server
new_server = Server()
new_server.name = obj["Name"]
new_server.servergroup_id = group_id
new_server.user_id = user_id
new_server.ssl_mode = obj["SSLMode"]
new_server.maintenance_db = obj["MaintenanceDB"]
new_server.host = obj.get("Host", None)
new_server.hostaddr = obj.get("HostAddr", None)
new_server.port = obj.get("Port", None)
new_server.username = obj.get("Username", None)
new_server.role = obj.get("Role", None)
new_server.ssl_mode = obj["SSLMode"]
new_server.comment = obj.get("Comment", None)
new_server.db_res = obj.get("DBRestriction", None)
new_server.passfile = obj.get("PassFile", None)
new_server.sslcert = obj.get("SSLCert", None)
new_server.sslkey = obj.get("SSLKey", None)
new_server.sslrootcert = obj.get("SSLRootCert", None)
new_server.sslcrl = obj.get("SSLCrl", None)
new_server.sslcompression = obj.get("SSLCompression", None)
new_server.bgcolor = obj.get("BGColor", None)
new_server.fgcolor = obj.get("FGColor", None)
new_server.service = obj.get("Service", None)
new_server.connect_timeout = obj.get("Timeout", None)
new_server.use_ssh_tunnel = obj.get("UseSSHTunnel", None)
new_server.tunnel_host = obj.get("TunnelHost", None)
new_server.tunnel_port = obj.get("TunnelPort", None)
new_server.tunnel_username = obj.get("TunnelUsername", None)
new_server.tunnel_authentication = \
obj.get("TunnelAuthentication", None)
db.session.add(new_server)
try:
db.session.commit()
except Exception as e:
print("Error creating server '%s': %s" %
(new_server.name, e))
print_summary()
sys.exit(1)
servers_added = servers_added + 1
print_summary()
def load_servers(args):
"""Load server groups and servers.
Args:
args (ArgParser): The parsed command line options
"""
try:
with open(args.load_servers) as f:
data = json.load(f)
except json.decoder.JSONDecodeError as e:
print("Error parsing input file %s: %s" % (args.load_servers, e))
sys.exit(1)
except Exception as e:
print("Error reading input file %s: [%d] %s" %
(args.load_servers, e.errno, e.strerror))
sys.exit(1)
f.close()
app = create_app()
with app.app_context():
# What user?
if args.user is not None:
dump_user = args.user
else:
dump_user = config.DESKTOP_USER
user = User.query.filter_by(email=dump_user).first()
if user is None:
print("The specified user ID (%s) could not be found." % dump_user)
sys.exit(1)
user_id = user.id
# Counters
groups_added = 0
servers_added = 0
# Get the server group
groups = ServerGroup.query.all()
def print_summary():
print("Added %d Server Group(s) and %d Server(s)." %
(groups_added, servers_added))
# Loop through the servers...
if "Servers" not in data:
print("'Servers' attribute not found in file '%s'" %
args.load_servers)
print_summary()
sys.exit(1)
for server in data["Servers"]:
obj = data["Servers"][server]
def check_attrib(attrib):
if attrib not in obj:
print("'%s' attribute not found for server '%s'" %
(attrib, server))
print_summary()
sys.exit(1)
check_attrib("Name")
check_attrib("Group")
check_attrib("Port")
check_attrib("Username")
check_attrib("SSLMode")
check_attrib("MaintenanceDB")
if "Host" not in obj and \
"HostAddr" not in obj and \
"Service" not in obj:
print("'Host', 'HostAddr' or 'Service' attribute not found "
"for server '%s'" % server)
print_summary()
sys.exit(1)
# Get the group. Create if necessary
group_id = -1
for g in groups:
if g.name == obj["Group"]:
group_id = g.id
break
if group_id == -1:
new_group = ServerGroup()
new_group.name = obj["Group"]
new_group.user_id = user_id
db.session.add(new_group)
try:
db.session.commit()
except Exception as e:
print("Error creating server group '%s': %s" %
(new_group.name, e))
print_summary()
sys.exit(1)
group_id = new_group.id
groups_added = groups_added + 1
groups = ServerGroup.query.all()
# Create the server
new_server = Server()
new_server.name = obj["Name"]
new_server.servergroup_id = group_id
new_server.user_id = user_id
new_server.host = obj["Host"]
if "HostAddr" in obj:
new_server.hostaddr = obj["HostAddr"]
new_server.port = obj["Port"]
new_server.maintenance_db = obj["MaintenanceDB"]
new_server.username = obj["Username"]
if "Role" in obj:
new_server.role = obj["Role"]
new_server.ssl_mode = obj["SSLMode"]
if "Comment" in obj:
new_server.comment = obj["Comment"]
if "DBRestriction" in obj:
new_server.db_res = obj["DBRestriction"]
if "PassFile" in obj:
new_server.passfile = obj["PassFile"]
if "SSLCert" in obj:
new_server.sslcert = obj["SSLCert"]
if "SSLKey" in obj:
new_server.sslkey = obj["SSLKey"]
if "SSLRootCert" in obj:
new_server.sslrootcert = obj["SSLRootCert"]
if "SSLCrl" in obj:
new_server.sslcrl = obj["SSLCrl"]
if "SSLCompression" in obj:
new_server.sslcompression = obj["SSLCompression"]
if "BGColor" in obj:
new_server.bgcolor = obj["BGColor"]
if "FGColor" in obj:
new_server.fgcolor = obj["FGColor"]
if "Service" in obj:
new_server.service = obj["Service"]
if "Timeout" in obj:
new_server.connect_timeout = obj["Timeout"]
if "UseSSHTunnel" in obj:
new_server.use_ssh_tunnel = obj["UseSSHTunnel"]
if "TunnelHost" in obj:
new_server.tunnel_host = obj["TunnelHost"]
if "TunnelPort" in obj:
new_server.tunnel_port = obj["TunnelPort"]
if "TunnelUsername" in obj:
new_server.tunnel_username = obj["TunnelUsername"]
if "TunnelAuthentication" in obj:
new_server.tunnel_authentication = obj["TunnelAuthentication"]
db.session.add(new_server)
try:
db.session.commit()
except Exception as e:
print("Error creating server '%s': %s" % (new_server.name, e))
print_summary()
sys.exit(1)
servers_added = servers_added + 1
print_summary()
import os
from pgadmin import create_app
import config
from pgadmin.utils.crypto import encrypt
from pgadmin.model import db, User, Server
app = create_app(config.APP_NAME)
with app.app_context():
user = User.query.first()
key = user.password
i = 0
while True:
i = i + 1
s = Server()
sfx = str(i) if i > 1 else ''
s.name = os.getenv('SETUP_SERVER' + sfx + '_NAME')
s.host = os.getenv('SETUP_SERVER' + sfx + '_HOST')
s.port = os.getenv('SETUP_SERVER' + sfx + '_PORT')
s.username = os.getenv('SETUP_SERVER' + sfx + '_USER')
if not s.name or not s.host or not s.port or not s.username:
break
pwd = os.getenv('SETUP_SERVER' + sfx + '_PASS')
if pwd:
s.password = encrypt(pwd, key)
s.save_password = 1
s.user_id = user.id
s.servergroup_id = 1
s.maintenance_db = 'postgres'
s.ssl_mode = 'prefer'
db.session.add(s)
def create(self, gid):
"""Add a server node to the settings database"""
required_args = [
u'name', u'host', u'port', u'db', u'username', u'sslmode', u'role'
]
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
for arg in required_args:
if arg not in data:
return make_json_response(
status=410,
success=0,
errormsg=gettext(
"Could not find the required parameter (%s)." % arg))
server = None
try:
server = Server(
user_id=current_user.id,
servergroup_id=data[u'gid'] if u'gid' in data else gid,
name=data[u'name'],
host=data[u'host'],
port=data[u'port'],
maintenance_db=data[u'db'],
username=data[u'username'],
ssl_mode=data[u'sslmode'],
comment=data[u'comment'] if u'comment' in data else None,
role=data[u'role'] if u'role' in data else None)
db.session.add(server)
db.session.commit()
connected = False
icon = "icon-server-not-connected"
user = None
manager = None
if 'connect_now' in data and data['connect_now']:
manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(
server.id)
manager.update(server)
conn = manager.connection()
have_password = False
if 'password' in data and data["password"] != '':
# login with password
have_password = True
password = data['password']
password = encrypt(password, current_user.password)
else:
# Attempt password less login
password = None
status, errmsg = conn.connect(password=password,
server_types=ServerType.types())
if hasattr(str, 'decode') and errmsg is not None:
errmsg = errmsg.decode('utf-8')
if not status:
db.session.delete(server)
db.session.commit()
return make_json_response(
status=401,
success=0,
errormsg=gettext(
u"Unable to connect to server:\n\n%s" % errmsg))
else:
if 'save_password' in data and data[
'save_password'] and have_password:
setattr(server, 'password', password)
db.session.commit()
user = manager.user_info
connected = True
return jsonify(node=self.blueprint.generate_browser_node(
"%d" % server.id,
server.servergroup_id,
server.name,
'icon-{0}'.format(manager.server_type)
if manager and manager.server_type else "icon-pg",
True,
self.node_type,
user=user,
connected=connected,
server_type=manager.
server_type if manager and manager.server_type else 'pg'))
except Exception as e:
if server:
db.session.delete(server)
db.session.commit()
current_app.logger.exception(e)
return make_json_response(status=410, success=0, errormsg=str(e))
