Hi,
PFA patch to allow user to provide custom path for SSL certificates and
also allow user to pass .pgpass file when making server connection.
RM#2649
RM#2650
*SSL certificates options reference:*
https://www.postgresql.org/docs/10/static/libpq-connect.html
https://www.postgresql.org/docs/9.1/static/ssl-tcp.html
*.pgpass file reference:*
https://www.postgresql.org/docs/10/static/libpq-pgpass.html
--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
[image: https://community.postgresrocks.net/]
<https://community.postgresrocks.net/>
diff --git a/web/migrations/versions/ef590e979b0d_.py
b/web/migrations/versions/ef590e979b0d_.py
new file mode 100644
index 0000000..be219c5
--- /dev/null
+++ b/web/migrations/versions/ef590e979b0d_.py
@@ -0,0 +1,46 @@
+
+"""empty message
+
+Revision ID: ef590e979b0d
+Revises: d85a62333272
+Create Date: 2017-08-23 18:37:14.836988
+
+"""
+from alembic import op
+import sqlalchemy as sa
+from pgadmin.model import db
+
+# revision identifiers, used by Alembic.
+revision = 'ef590e979b0d'
+down_revision = 'd85a62333272'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+ db.engine.execute(
+ 'ALTER TABLE server ADD COLUMN passfile TEXT'
+ )
+ db.engine.execute(
+ 'ALTER TABLE server ADD COLUMN sslcert TEXT'
+ )
+ db.engine.execute(
+ 'ALTER TABLE server ADD COLUMN sslkey TEXT'
+ )
+ db.engine.execute(
+ 'ALTER TABLE server ADD COLUMN sslrootcert TEXT'
+ )
+ db.engine.execute(
+ 'ALTER TABLE server ADD COLUMN sslcrl TEXT'
+ )
+ db.engine.execute(
+ 'ALTER TABLE server ADD COLUMN sslcompression '
+ 'INTEGER default 0'
+ )
+ db.engine.execute(
+ 'UPDATE server SET sslcompression=0'
+ )
+
+
+def downgrade():
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/__init__.py
b/web/pgadmin/browser/server_groups/servers/__init__.py
index a74436e..d21a8f6 100644
--- a/web/pgadmin/browser/server_groups/servers/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/__init__.py
@@ -230,7 +230,29 @@ class ServerNode(PGChildNodeView):
'((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*$'
pat4 = re.compile(EXP_IP4)
pat6 = re.compile(EXP_IP6)
+ SSL_MODES = ['require', 'verify-ca', 'verify-full']
+ def check_ssl_fields(self, data):
+ """
+ This function will allow us to check and set defaults for
+ SSL fields
+
+ Args:
+ data: Response data
+
+ Returns:
+ Flag and Data
+ """
+ flag = False
+ if 'sslmode' in data and data['sslmode'] in self.SSL_MODES:
+ flag = True
+ ssl_fields = [
+ 'sslcert', 'sslkey', 'sslrootcert', 'sslcrl', 'sslcompression'
+ ]
+ for field in ssl_fields:
+ if field not in data:
+ data[field] = None
+ return flag, data
def nodes(self, gid):
res = []
@@ -381,7 +403,13 @@ class ServerNode(PGChildNodeView):
'gid': 'servergroup_id',
'comment': 'comment',
'role': 'role',
- 'db_res': 'db_res'
+ 'db_res': 'db_res',
+ 'passfile': 'passfile',
+ 'sslcert': 'sslcert',
+ 'sslkey': 'sslkey',
+ 'sslrootcert': 'sslrootcert',
+ 'sslcrl': 'sslcrl',
+ 'sslcompression': 'sslcompression'
}
disp_lbl = {
@@ -411,8 +439,6 @@ class ServerNode(PGChildNodeView):
errormsg=gettext('Host address not valid')
)
-
-
manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
conn = manager.connection()
connected = conn.connected()
@@ -430,7 +456,12 @@ class ServerNode(PGChildNodeView):
for arg in config_param_map:
if arg in data:
- setattr(server, config_param_map[arg], data[arg])
+ value = data[arg]
+ # sqlite3 do not have boolean type so we need to convert
+ # it manually to integer
+ if arg == 'sslcompression':
+ value = 1 if value else 0
+ setattr(server, config_param_map[arg], value)
idx += 1
if idx == 0:
@@ -532,6 +563,8 @@ class ServerNode(PGChildNodeView):
conn = manager.connection()
connected = conn.connected()
+ is_ssl = True if server.ssl_mode in self.SSL_MODES else False
+
return ajax_response(
response={
'id': server.id,
@@ -549,7 +582,14 @@ class ServerNode(PGChildNodeView):
'version': manager.ver,
'sslmode': server.ssl_mode,
'server_type': manager.server_type if connected else 'pg',
- 'db_res': server.db_res.split(',') if server.db_res else None
+ 'db_res': server.db_res.split(',') if server.db_res else None,
+ 'passfile': server.passfile if server.passfile else None,
+ 'sslcert': server.sslcert if is_ssl else None,
+ 'sslkey': server.sslkey if is_ssl else None,
+ 'sslrootcert': server.sslrootcert if is_ssl else None,
+ 'sslcrl': server.sslcrl if is_ssl else None,
+ 'sslcompression': True if is_ssl and server.sslcompression
+ else False
}
)
@@ -588,6 +628,9 @@ class ServerNode(PGChildNodeView):
errormsg=gettext('Host address not valid')
)
+ # To check ssl configuration
+ is_ssl, data = self.check_ssl_fields(data)
+
server = None
try:
@@ -603,7 +646,12 @@ class ServerNode(PGChildNodeView):
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
+ 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()
@@ -621,14 +669,21 @@ class ServerNode(PGChildNodeView):
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:
@@ -774,6 +829,7 @@ class ServerNode(PGChildNodeView):
) if request.data else {}
password = None
+ passfile = None
save_password = False
# Connect the Server
@@ -782,7 +838,8 @@ class ServerNode(PGChildNodeView):
if 'password' not in data:
conn_passwd = getattr(conn, 'password', None)
- if conn_passwd is None and server.password is None:
+ if conn_passwd is None and server.password is None and \
+ server.passfile is None:
# Return the password template in case password is not
# provided, or password has not been saved earlier.
return make_json_response(
@@ -795,6 +852,8 @@ class ServerNode(PGChildNodeView):
_=gettext
)
)
+ elif server.passfile and server.passfile != '':
+ passfile = server.passfile
else:
password = conn_passwd or server.password
else:
@@ -815,6 +874,7 @@ class ServerNode(PGChildNodeView):
try:
status, errmsg = conn.connect(
password=password,
+ passfile=passfile,
server_types=ServerType.types()
)
except Exception as e:
diff --git a/web/pgadmin/browser/server_groups/servers/static/js/server.js
b/web/pgadmin/browser/server_groups/servers/static/js/server.js
index ea25ab8..c3dd4df 100644
--- a/web/pgadmin/browser/server_groups/servers/static/js/server.js
+++ b/web/pgadmin/browser/server_groups/servers/static/js/server.js
@@ -611,7 +611,13 @@ define('pgadmin.node.server', [
connect_now: true,
password: undefined,
save_password: false,
- db_res: ''
+ db_res: '',
+ passfile: undefined,
+ sslcompression: false,
+ sslcert: undefined,
+ sslkey: undefined,
+ sslrootcert: undefined,
+ sslcrl: undefined
},
// Default values!
initialize: function(attrs, args) {
@@ -698,6 +704,48 @@ define('pgadmin.node.server', [
{label: 'Verify-CA', value: 'verify-ca'},
{label: 'Verify-Full', value: 'verify-full'}
]
+ },{
+ id: 'passfile', label: gettext('Password File'), type: 'text',
+ group: gettext('Advanced'), mode: ['edit', 'create'],
+ disabled: 'isConnected', control: Backform.FileControl,
+ dialog_type: 'select_file', supp_types: ['*']
+ },{
+ id: 'sslcert', label: gettext('Client certificate'), type: 'text',
+ group: gettext('SSL Configuration'), mode: ['edit', 'create'],
+ disabled: 'isSSL', control: Backform.FileControl,
+ dialog_type: 'select_file', supp_types: ['*'],
+ deps: ['sslmode']
+ },{
+ id: 'sslkey', label: gettext('Client certificate key'), type: 'text',
+ group: gettext('SSL Configuration'), mode: ['edit', 'create'],
+ disabled: 'isSSL', control: Backform.FileControl,
+ dialog_type: 'select_file', supp_types: ['*'],
+ deps: ['sslmode']
+ },{
+ id: 'sslrootcert', label: gettext('Root certificate'), type: 'text',
+ group: gettext('SSL Configuration'), mode: ['edit', 'create'],
+ disabled: 'isSSL', control: Backform.FileControl,
+ dialog_type: 'select_file', supp_types: ['*'],
+ deps: ['sslmode']
+ },{
+ id: 'sslcrl', label: gettext('Certificate revocation list'), type:
'text',
+ group: gettext('SSL Configuration'), mode: ['edit', 'create'],
+ disabled: 'isSSL', control: Backform.FileControl,
+ dialog_type: 'select_file', supp_types: ['*'],
+ deps: ['sslmode']
+ },{
+ id: 'sslcompression', label: gettext('SSL compression?'), type:
'switch',
+ mode: ['edit', 'create'], group: gettext('SSL Configuration'),
+ 'options': { 'onText': 'True', 'offText': 'False',
+ 'onColor': 'success', 'offColor': 'danger', 'size': 'small'},
+ deps: ['sslmode'], disabled: 'isSSL'
+ },{
+ id: 'passfile', label: gettext('Password File'), type: 'text',
+ group: gettext('Advanced'), mode: ['properties'],
+ visible: function(m) {
+ var passfile = m.get('passfile');
+ return !_.isUndefined(passfile) && !_.isNull(passfile);
+ }
}],
validate: function() {
var err = {},
@@ -790,6 +838,14 @@ define('pgadmin.node.server', [
},
isConnected: function(model) {
return model.get('connected');
+ },
+ isSSL: function(model) {
+ var ssl_mode = model.get('sslmode');
+ // If server is not connected and have required SSL option
+ if(model.get('connected')) {
+ return true;
+ }
+ return _.indexOf(['require', 'verify-ca', 'verify-full'], ssl_mode)
== -1;
}
}),
connection_lost: function(i, resp) {
diff --git a/web/pgadmin/model/__init__.py b/web/pgadmin/model/__init__.py
index e1e1d4a..eb9dab3 100644
--- a/web/pgadmin/model/__init__.py
+++ b/web/pgadmin/model/__init__.py
@@ -129,8 +129,15 @@ class Server(db.Model):
backref=db.backref('server', cascade="all,
delete-orphan"),
lazy='joined')
db_res = db.Column(db.Text(), nullable=True)
-
-
+ passfile = db.Column(db.Text(), nullable=True)
+ sslcert = db.Column(db.Text(), nullable=True)
+ sslkey = db.Column(db.Text(), nullable=True)
+ sslrootcert = db.Column(db.Text(), nullable=True)
+ sslcrl = db.Column(db.Text(), nullable=True)
+ sslcompression =db.Column(db.Integer(),
+ db.CheckConstraint(
+ 'sslcompression >= 0 AND sslcompression <= 1'
+ ), nullable=False)
class ModulePreference(db.Model):
@@ -220,4 +227,4 @@ class Keys(db.Model):
"""Define the keys table."""
__tablename__ = 'keys'
name = db.Column(db.String(), nullable=False, primary_key=True)
- value = db.Column(db.String(), nullable=False)
\ No newline at end of file
+ value = db.Column(db.String(), nullable=False)
diff --git a/web/pgadmin/utils/driver/psycopg2/__init__.py
b/web/pgadmin/utils/driver/psycopg2/__init__.py
index 7cda5ef..a2826a1 100644
--- a/web/pgadmin/utils/driver/psycopg2/__init__.py
+++ b/web/pgadmin/utils/driver/psycopg2/__init__.py
@@ -295,9 +295,11 @@ class Connection(BaseConnection):
pg_conn = None
password = None
+ passfile = None
mgr = self.manager
encpass = kwargs['password'] if 'password' in kwargs else None
+ passfile = kwargs['passfile'] if 'passfile' in kwargs else None
if encpass is None:
encpass = self.password or getattr(mgr, 'password', None)
@@ -350,7 +352,13 @@ class Connection(BaseConnection):
user=user,
password=password,
async=self.async,
- sslmode=mgr.ssl_mode
+ passfile=passfile,
+ sslmode=mgr.ssl_mode,
+ sslcert=mgr.sslcert,
+ sslkey=mgr.sslkey,
+ sslrootcert=mgr.sslrootcert,
+ sslcrl=mgr.sslcrl,
+ sslcompression=True if mgr.sslcompression else False
)
# If connection is asynchronous then we will have to wait
@@ -1192,7 +1200,14 @@ Failed to execute query (execute_void) for the server
#{server_id} - {conn_id}
port=mgr.port,
database=self.db,
user=mgr.user,
- password=password
+ password=password,
+ passfile=mgr.passfile,
+ sslmode=mgr.ssl_mode,
+ sslcert=mgr.sslcert,
+ sslkey=mgr.sslkey,
+ sslrootcert=mgr.sslrootcert,
+ sslcrl=mgr.sslcrl,
+ sslcompression=True if mgr.sslcompression else False
)
except psycopg2.Error as e:
@@ -1461,7 +1476,15 @@ Failed to reset the connection to the server due to
following error:
port=self.manager.port,
database=self.db,
user=self.manager.user,
- password=password
+ password=password,
+ passfile=self.manager.passfile,
+ sslmode=self.manager.ssl_mode,
+ sslcert=self.manager.sslcert,
+ sslkey=self.manager.sslkey,
+ sslrootcert=self.manager.sslrootcert,
+ sslcrl=self.manager.sslcrl,
+ sslcompression=True if self.manager.sslcompression
+ else False
)
# Get the cursor and run the query
@@ -1616,6 +1639,12 @@ class ServerManager(object):
self.db_info = dict()
self.server_types = None
self.db_res = server.db_res
+ self.passfile = server.passfile
+ self.sslcert = server.sslcert
+ self.sslkey = server.sslkey
+ self.sslrootcert = server.sslrootcert
+ self.sslcrl = server.sslcrl
+ self.sslcompression = True if server.sslcompression else False
for con in self.connections:
self.connections[con]._release()
