Subject: [pgAdmin4][Patch] - RM 6158 - Logging into PostgreSQL servers with Kerberos Authentication

From: Khushboo Vashi <[email-masked]>
Date: Thu, 8 Apr 2021 12:20:40 +0530

Hi,

Please find the attached patch for RM 6158: Support Kerberos Authentication
- Phase 2.
This patch includes the support for logging into PostgreSQL servers with
Kerberos authentication.

Thanks,
Khushboo

---

From: Khushboo Vashi <[email-masked]>
Date: Wed, 14 Apr 2021 01:35:48 -0700

Hi,

Please find the attached patch with some minor improvements.

Thanks,
Khushboo

On Wed, Apr 7, 2021 at 11:50 PM Khushboo Vashi <
[email-masked]> wrote:

> Hi,
>
> Please find the attached patch for RM 6158: Support Kerberos
> Authentication - Phase 2.
diff --git a/web/config.py b/web/config.py
index 3e19a2858..e96d60c65 100644
--- a/web/config.py
+++ b/web/config.py
@@ -634,6 +634,9 @@ KRB_KTNAME = '<KRB5_KEYTAB_FILE>'
 
 KRB_AUTO_CREATE_USER = True
 
+KERBEROS_CCACHE_DIR = os.path.join(DATA_DIR, 'krbccache')
+
+
 ##########################################################################
 # Local config settings
 ##########################################################################
diff --git a/web/migrations/versions/cce2006fa107_.py b/web/migrations/versions/cce2006fa107_.py
new file mode 100644
index 000000000..0cd3d7da1
--- /dev/null
+++ b/web/migrations/versions/cce2006fa107_.py
@@ -0,0 +1,28 @@
+
+"""empty message
+
+Revision ID: cce2006fa107
+Revises: a39bd015b644
+Create Date: 2021-03-15 00:02:40.100252
+
+"""
+from alembic import op
+import sqlalchemy as sa
+from pgadmin.model import db
+
+# revision identifiers, used by Alembic.
+revision = 'cce2006fa107'
+down_revision = 'a39bd015b644'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    db.engine.execute(
+        'ALTER TABLE server ADD COLUMN kerberos_conn INTEGER DEFAULT 0'
+    )
+
+
+def downgrade():
+    # pgAdmin only upgrades, downgrade not implemented.
+    pass
diff --git a/web/pgadmin/authenticate/__init__.py b/web/pgadmin/authenticate/__init__.py
index 9166c2ffd..3bc64483e 100644
--- a/web/pgadmin/authenticate/__init__.py
+++ b/web/pgadmin/authenticate/__init__.py
@@ -13,10 +13,13 @@ import flask
 import pickle
 from flask import current_app, flash, Response, request, url_for,\
     render_template
-from flask_security import current_user
+from flask_babelex import gettext
+from flask_security import current_user, login_required
 from flask_security.views import _security, _ctx
 from flask_security.utils import config_value, get_post_logout_redirect, \
     get_post_login_redirect, logout_user
+from pgadmin.utils.ajax import make_json_response, internal_server_error
+import os
 
 from flask import session
 
@@ -34,7 +37,9 @@ class AuthenticateModule(PgAdminModule):
     def get_exposed_url_endpoints(self):
         return ['authenticate.login',
                 'authenticate.kerberos_login',
-                'authenticate.kerberos_logout']
+                'authenticate.kerberos_logout',
+                'authenticate.kerberos_update_ticket',
+                'authenticate.kerberos_validate_ticket']

 blueprint = AuthenticateModule(MODULE_NAME, __name__, static_url_path='')
@@ -55,6 +60,12 @@ def kerberos_login():
 @pgCSRFProtect.exempt
 def kerberos_logout():
     logout_user()
+    if 'KRB5CCNAME' in session:
+        # Remove the credential cache
+        cache_file_path = session['KRB5CCNAME'].split(":")[1]
+        if os.path.exists(cache_file_path):
+            os.remove(cache_file_path)
+
     return Response(render_template("browser/kerberos_logout.html",
                                     login_url=url_for('security.login'),
                                     ))
@@ -173,11 +184,13 @@ class AuthSourceManager():
             # OR When kerberos authentication failed while accessing pgadmin,
             # we need to break the loop as no need to authenticate further
             # even if the authentication sources set to multiple
-            if not status and (hasattr(msg, 'status') and
-                               msg.status == '401 UNAUTHORIZED') or \
-                (source.get_source_name() == KERBEROS and
-                 request.method == 'GET'):
-                break
+            if not status:
+                if (hasattr(msg, 'status') and
+                    msg.status == '401 UNAUTHORIZED') or\
+                        (source.get_source_name() ==
+                         KERBEROS and
+                         request.method == 'GET'):
+                    break
 
             if status:
                 self.set_source(source)
@@ -224,3 +237,58 @@ def init_app(app):
     AuthSourceRegistry.load_auth_sources()
 
     return auth_sources
+
+
[email-masked]("/kerberos/update_ticket",
+                 endpoint="kerberos_update_ticket", methods=["GET"])
[email-masked]
+@login_required
+def kerberos_update_ticket():
+    """
+    Update the kerberos ticket.
+    """
+    from werkzeug.datastructures import Headers
+    headers = Headers()
+
+    authorization = request.headers.get("Authorization", None)
+
+    if authorization is None:
+        # Send the Negotiate header to the client
+        # if Kerberos ticket is not found.
+        headers.add('WWW-Authenticate', 'Negotiate')
+        return Response("Unauthorised", 401, headers)
+    else:
+        source = get_auth_sources(KERBEROS)
+        auth_header = authorization.split()
+        in_token = auth_header[1]
+
+        # Validate the Kerberos ticket
+        status, context = source.negotiate_start(in_token)
+        if status:
+            return Response("Ticket updated successfully.")
+
+        return Response(context, 500)
+
+
[email-masked]("/kerberos/validate_ticket",
+                 endpoint="kerberos_validate_ticket", methods=["GET"])
[email-masked]
+@login_required
+def kerberos_validate_ticket():
+    """
+    Return the kerberos ticket lifetime left after getting the
+    ticket from the credential cache
+    """
+    import gssapi
+
+    try:
+        del_creds = gssapi.Credentials(store={'ccache': session['KRB5CCNAME']})
+        creds = del_creds.acquire(store={'ccache': session['KRB5CCNAME']})
+    except Exception as e:
+        current_app.logger.exception(e)
+        return internal_server_error(errormsg=str(e))
+
+    return make_json_response(
+        data={'ticket_lifetime': creds.lifetime},
+        status=200
+    )
diff --git a/web/pgadmin/authenticate/kerberos.py b/web/pgadmin/authenticate/kerberos.py
index 57aa1e0f0..2f8fd0d6e 100644
--- a/web/pgadmin/authenticate/kerberos.py
+++ b/web/pgadmin/authenticate/kerberos.py
@@ -10,7 +10,7 @@
 """A blueprint module implementing the Spnego/Kerberos authentication."""
 
 import base64
-from os import environ
+from os import environ, path
 
 from werkzeug.datastructures import Headers
 from flask_babelex import gettext
@@ -128,19 +128,37 @@ class KerberosAuthentication(BaseAuthentication):
         if out_token and not context.complete:
             return False, out_token
         if context.complete:
+            deleg_creds = context.delegated_creds
+            if not hasattr(deleg_creds, 'name'):
+                error_msg = gettext('Delegated credentials not supplied.')
+                current_app.logger.error(error_msg)
+                return False, Exception(error_msg)
+            try:
+                cache_file_path = path.join(
+                    config.KERBEROS_CCACHE_DIR, 'pgadmin_cache_{0}'.format(
+                        deleg_creds.name)
+                )
+                CCACHE = 'FILE:{0}'.format(cache_file_path)
+                store = {'ccache': CCACHE}
+                deleg_creds.store(store, overwrite=True, set_default=True)
+                session['KRB5CCNAME'] = CCACHE
+            except Exception as e:
+                current_app.logger.exception(e)
+                return False, e
+
             return True, context
         else:
             return False, None
 
     def negotiate_end(self, context):
-        # Free gss_cred_id_t
+        # Free Delegated Credentials
         del_creds = getattr(context, 'delegated_creds', None)
         if del_creds:
             deleg_creds = context.delegated_creds
             del(deleg_creds)
 
     def __auto_create_user(self, username):
-        """Add the ldap user to the internal SQLite database."""
+        """Add the kerberos user to the internal SQLite database."""
         username = str(username)
         if config.KRB_AUTO_CREATE_USER:
             user = User.query.filter_by(
diff --git a/web/pgadmin/authenticate/static/js/kerberos.js b/web/pgadmin/authenticate/static/js/kerberos.js
new file mode 100644
index 000000000..dffe1d4dc
--- /dev/null
+++ b/web/pgadmin/authenticate/static/js/kerberos.js
@@ -0,0 +1,55 @@
+import url_for from 'sources/url_for';
+
+function fetch_ticket() {
+  // Fetch the Kerberos Updated ticket through SPNEGO
+  return fetch(url_for('authenticate.kerberos_update_ticket')
+  )
+    .then(function(response){
+      if (response.status >= 200 && response.status < 300) {
+        return Promise.resolve(response);
+      } else {
+        return Promise.reject(new Error(response.statusText));
+      }
+    });
+}
+
+function fetch_ticket_lifetime () {
+  // Fetch the Kerberos ticket lifetime left
+
+  return fetch(url_for('authenticate.kerberos_validate_ticket')
+  )
+    .then(
+      function(response){
+        if (response.status >= 200 && response.status < 300) {
+          return response.json();
+        } else {
+          return Promise.reject(new Error(response.statusText));
+        }
+      }
+    )
+    .then(function(response){
+      let ticket_lifetime = response.data.ticket_lifetime;
+      if (ticket_lifetime > 0) {
+        return Promise.resolve(ticket_lifetime);
+      } else {
+        return Promise.reject();
+      }
+    });
+
+}
+
+function validate_kerberos_ticket() {
+  // Ping pgAdmin server every 10 seconds
+  // to fetch the Kerberos ticket lifetime left
+  return setInterval(function() {
+    let newPromise = fetch_ticket_lifetime();
+    newPromise.then(
+      function() {
+        return;
+      },
+      fetch_ticket
+    );
+  }, 10000);
+}
+
+export {fetch_ticket, validate_kerberos_ticket, fetch_ticket_lifetime};
diff --git a/web/pgadmin/browser/server_groups/servers/__init__.py b/web/pgadmin/browser/server_groups/servers/__init__.py
index bc0bd4611..a7e5288e5 100644
--- a/web/pgadmin/browser/server_groups/servers/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/__init__.py
@@ -253,7 +253,8 @@ class ServerModule(sg.ServerGroupPluginModule):
                 errmsg=errmsg,
                 user_id=server.user_id,
                 user_name=server.username,
-                shared=server.shared
+                shared=server.shared,
+                is_kerberos_conn=bool(server.kerberos_conn),
             )
 
     @property
@@ -546,7 +547,8 @@ class ServerNode(PGChildNodeView):
                     if server.tunnel_password is not None else False,
                     errmsg=errmsg,
                     user_name=server.username,
-                    shared=server.shared
+                    shared=server.shared,
+                    is_kerberos_conn=bool(server.kerberos_conn)
                 )
             )
 
@@ -613,7 +615,8 @@ class ServerNode(PGChildNodeView):
                 if server.tunnel_password is not None else False,
                 errmsg=errmsg,
                 shared=server.shared,
-                user_name=server.username
+                user_name=server.username,
+                is_kerberos_conn=bool(server.kerberos_conn)
             ),
         )
 
@@ -719,7 +722,8 @@ class ServerNode(PGChildNodeView):
             'tunnel_username': 'tunnel_username',
             'tunnel_authentication': 'tunnel_authentication',
             'tunnel_identity_file': 'tunnel_identity_file',
-            'shared': 'shared'
+            'shared': 'shared',
+            'kerberos_conn': 'kerberos_conn',
         }
 
         disp_lbl = {
@@ -983,7 +987,8 @@ class ServerNode(PGChildNodeView):
             'tunnel_username': tunnel_username,
             'tunnel_identity_file': server.tunnel_identity_file
             if server.tunnel_identity_file else None,
-            'tunnel_authentication': tunnel_authentication
+            'tunnel_authentication': tunnel_authentication,
+            'kerberos_conn': bool(server.kerberos_conn),
         }
 
         return ajax_response(response)
@@ -1070,7 +1075,8 @@ class ServerNode(PGChildNodeView):
                 tunnel_authentication=data.get('tunnel_authentication', 0),
                 tunnel_identity_file=data.get('tunnel_identity_file', None),
                 shared=data.get('shared', None),
-                passfile=data.get('passfile', None)
+                passfile=data.get('passfile', None),
+                kerberos_conn=1 if data.get('kerberos_conn', False) else 0,
             )
             db.session.add(server)
             db.session.commit()
@@ -1152,7 +1158,8 @@ class ServerNode(PGChildNodeView):
                     else 'pg',
                     version=manager.version
                     if manager and manager.version
-                    else None
+                    else None,
+                    is_kerberos_conn=bool(server.kerberos_conn),
                 )
             )
 
@@ -1346,7 +1353,7 @@ class ServerNode(PGChildNodeView):
                 except Exception as e:
                     current_app.logger.exception(e)
                     return internal_server_error(errormsg=str(e))
-        if 'password' not in data:
+        if 'password' not in data and server.kerberos_conn is False:
             conn_passwd = getattr(conn, 'password', None)
             if conn_passwd is None and not server.save_password and \
                     server.passfile is None and server.service is None:
@@ -1355,7 +1362,7 @@ class ServerNode(PGChildNodeView):
                 passfile = server.passfile
             else:
                 password = conn_passwd or server.password
-        else:
+        elif server.kerberos_conn is False:
             password = data['password'] if 'password' in data else None
             save_password = data['save_password']\
                 if 'save_password' in data else False
@@ -1398,6 +1405,9 @@ class ServerNode(PGChildNodeView):
                 "Could not connect to server(#{0}) - '{1}'.\nError: {2}"
                 .format(server.id, server.name, errmsg)
             )
+            if errmsg.find('Ticket expired') != -1:
+                return internal_server_error(errmsg)
+
             return self.get_response_for_password(server, 401, True,
                                                   True, errmsg)
         else:
@@ -1465,6 +1475,7 @@ class ServerNode(PGChildNodeView):
                     'is_password_saved': bool(server.save_password),
                     'is_tunnel_password_saved': True
                     if server.tunnel_password is not None else False,
+                    'is_kerberos_conn': bool(server.kerberos_conn),
                 }
             )
 
diff --git a/web/pgadmin/browser/server_groups/servers/databases/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/__init__.py
index 60af1de42..4b1d7308d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/__init__.py
@@ -490,6 +490,7 @@ class DatabaseView(PGChildNodeView):
                         did, errmsg
                     )
                 )
+
                 return internal_server_error(errmsg)
             else:
                 current_app.logger.info(
diff --git a/web/pgadmin/browser/server_groups/servers/databases/static/js/database.js b/web/pgadmin/browser/server_groups/servers/databases/static/js/database.js
index c53f04429..01ab89c50 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/static/js/database.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/static/js/database.js
@@ -10,9 +10,10 @@
 define('pgadmin.node.database', [
   'sources/gettext', 'sources/url_for', 'jquery', 'underscore',
   'sources/utils', 'sources/pgadmin', 'pgadmin.browser.utils',
-  'pgadmin.alertifyjs', 'pgadmin.backform', 'pgadmin.browser.collection',
+  'pgadmin.alertifyjs', 'pgadmin.backform',
+  'pgadmin.authenticate.kerberos', 'pgadmin.browser.collection',
   'pgadmin.browser.server.privilege', 'pgadmin.browser.server.variable',
-], function(gettext, url_for, $, _, pgadminUtils, pgAdmin, pgBrowser, Alertify, Backform) {
+], function(gettext, url_for, $, _, pgadminUtils, pgAdmin, pgBrowser, Alertify, Backform, Kerberos) {
 
   if (!pgBrowser.Nodes['coll-database']) {
     pgBrowser.Nodes['coll-database'] =
@@ -556,24 +557,39 @@ define('pgadmin.node.database', [
           onFailure = function(
             xhr, status, error, _model, _data, _tree, _item, _status
           ) {
-            if (!_status) {
-              tree.setInode(_item);
-              tree.addIcon(_item, {icon: 'icon-database-not-connected'});
-            }
-
-            Alertify.pgNotifier('error', xhr, error, function(msg) {
-              setTimeout(function() {
-                if (msg == 'CRYPTKEY_SET') {
+            if (xhr.status != 200 && xhr.responseText.search('Ticket expired') !== -1) {
+              tree.addIcon(_item, {icon: 'icon-server-connecting'});
+              let fetchTicket = Kerberos.fetch_ticket();
+              fetchTicket.then(
+                function() {
                   connect_to_database(_model, _data, _tree, _item, _wasConnected);
-                } else {
-                  Alertify.dlgServerPass(
-                    gettext('Connect to database'),
-                    msg, _model, _data, _tree, _item, _status,
-                    onSuccess, onFailure, onCancel
-                  ).resizeTo();
+                },
+                function(error) {
+                  tree.setInode(_item);
+                  tree.addIcon(_item, {icon: 'icon-database-not-connected'});
+                  Alertify.pgNotifier(error, xhr, gettext('Connect  to database.'));
                 }
-              }, 100);
-            });
+              );
+            } else {
+              if (!_status) {
+                tree.setInode(_item);
+                tree.addIcon(_item, {icon: 'icon-database-not-connected'});
+              }
+
+              Alertify.pgNotifier('error', xhr, error, function(msg) {
+                setTimeout(function() {
+                  if (msg == 'CRYPTKEY_SET') {
+                    connect_to_database(_model, _data, _tree, _item, _wasConnected);
+                  } else {
+                    Alertify.dlgServerPass(
+                      gettext('Connect to database'),
+                      msg, _model, _data, _tree, _item, _status,
+                      onSuccess, onFailure, onCancel
+                    ).resizeTo();
+                  }
+                }, 100);
+              });
+            }
           },
           onSuccess = function(
             res, model, _data, _tree, _item, _connected
@@ -640,6 +656,7 @@ define('pgadmin.node.database', [
           if (xhr.status === 410) {
             error = gettext('Error: Object not found - %s.', error);
           }
+
           return onFailure(
             xhr, status, error, obj, data, tree, item, wasConnected
           );
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 ab95d6d89..deee434bd 100644
--- a/web/pgadmin/browser/server_groups/servers/static/js/server.js
+++ b/web/pgadmin/browser/server_groups/servers/static/js/server.js
@@ -13,11 +13,12 @@ define('pgadmin.node.server', [
   'pgadmin.server.supported_servers', 'pgadmin.user_management.current_user',
   'pgadmin.alertifyjs', 'pgadmin.backform',
   'sources/browser/server_groups/servers/model_validation',
+  'pgadmin.authenticate.kerberos',
   'pgadmin.browser.server.privilege',
 ], function(
   gettext, url_for, $, _, Backbone, pgAdmin, pgBrowser,
   supported_servers, current_user, Alertify, Backform,
-  modelValidation
+  modelValidation, Kerberos
 ) {
 
   if (!pgBrowser.Nodes['server']) {
@@ -904,20 +905,32 @@ define('pgadmin.node.server', [
               }
             },
           }),
+        },{
+          id: 'kerberos_conn', label: gettext('Kerberos authentication?'), type: 'switch',
+          group: gettext('Connection'), 'options': {
+            'onText':  gettext('True'), 'offText':  gettext('False'), 'size': 'mini',
+          },
         },{
           id: 'password', label: gettext('Password'), type: 'password', maxlength: null,
-          group: gettext('Connection'), control: 'input', mode: ['create'], deps: ['connect_now'],
+          group: gettext('Connection'), control: 'input', mode: ['create'],
+          deps: ['connect_now', 'kerberos_conn'],
           visible: function(model) {
             return model.get('connect_now') && model.isNew();
           },
+          disabled: function(model) {
+            if (model.get('kerberos_conn'))
+              return true;
+
+            return false;
+          },
         },{
           id: 'save_password', controlLabel: gettext('Save password?'),
           type: 'checkbox', group: gettext('Connection'), mode: ['create'],
-          deps: ['connect_now'], visible: function(model) {
+          deps: ['connect_now', 'kerberos_conn'], visible: function(model) {
             return model.get('connect_now') && model.isNew();
           },
-          disabled: function() {
-            if (!current_user.allow_save_password)
+          disabled: function(model) {
+            if (!current_user.allow_save_password || model.get('kerberos_conn'))
               return true;
 
             return false;
@@ -1279,19 +1292,32 @@ define('pgadmin.node.server', [
             }
 
           }
-
-          Alertify.pgNotifier('error', xhr, error, function(msg) {
-            setTimeout(function() {
-              if (msg == 'CRYPTKEY_SET') {
+          if (_data.is_kerberos_conn === true || (xhr.status != 200 && xhr.responseText.search('Ticket expired') !== -1)) {
+            tree.addIcon(_item, {icon: 'icon-server-connecting'});
+            let fetchTicket = Kerberos.fetch_ticket();
+            fetchTicket.then(
+              function() {
                 connect_to_server(_node, _data, _tree, _item, _wasConnected);
-              } else {
-                Alertify.dlgServerPass(
-                  gettext('Connect to Server'),
-                  msg, _node, _data, _tree, _item, _wasConnected
-                ).resizeTo();
+              },
+              function() {
+                tree.addIcon(_item, {icon: 'icon-server-not-connected'});
+                Alertify.pgNotifier('Connection error', xhr, gettext('Connect to server.'));
               }
-            }, 100);
-          });
+            );
+          } else {
+            Alertify.pgNotifier('error', xhr, error, function(msg) {
+              setTimeout(function() {
+                if (msg == 'CRYPTKEY_SET') {
+                  connect_to_server(_node, _data, _tree, _item, _wasConnected);
+                } else {
+                  Alertify.dlgServerPass(
+                    gettext('Connect to Server'),
+                    msg, _node, _data, _tree, _item, _wasConnected
+                  ).resizeTo();
+                }
+              }, 100);
+            });
+          }
         },
         onSuccess = function(res, node, _data, _tree, _item, _wasConnected) {
           if (res && res.data) {
diff --git a/web/pgadmin/browser/static/js/browser.js b/web/pgadmin/browser/static/js/browser.js
index 4ffb5ee5a..bf44aa6f4 100644
--- a/web/pgadmin/browser/static/js/browser.js
+++ b/web/pgadmin/browser/static/js/browser.js
@@ -12,19 +12,22 @@ define('pgadmin.browser', [
   'sources/gettext', 'sources/url_for', 'require', 'jquery', 'underscore',
   'bootstrap', 'sources/pgadmin', 'pgadmin.alertifyjs', 'bundled_codemirror',
   'sources/check_node_visibility', './toolbar', 'pgadmin.help',
-  'sources/csrf', 'sources/utils', 'sources/window', 'pgadmin.browser.utils',
-  'wcdocker', 'jquery.contextmenu', 'jquery.aciplugin', 'jquery.acitree',
+  'sources/csrf', 'sources/utils', 'sources/window', 'pgadmin.authenticate.kerberos',
+  'pgadmin.browser.utils', 'wcdocker', 'jquery.contextmenu', 'jquery.aciplugin',
+  'jquery.acitree',
   'pgadmin.browser.preferences', 'pgadmin.browser.messages',
   'pgadmin.browser.menu', 'pgadmin.browser.panel', 'pgadmin.browser.layout',
   'pgadmin.browser.runtime', 'pgadmin.browser.error', 'pgadmin.browser.frame',
   'pgadmin.browser.node', 'pgadmin.browser.collection', 'pgadmin.browser.activity',
   'sources/codemirror/addon/fold/pgadmin-sqlfoldcode',
-  'pgadmin.browser.keyboard', 'sources/tree/pgadmin_tree_save_state','jquery.acisortable', 'jquery.acifragment',
+  'pgadmin.browser.keyboard', 'sources/tree/pgadmin_tree_save_state','jquery.acisortable',
+  'jquery.acifragment',
 ], function(
   tree,
   gettext, url_for, require, $, _,
   Bootstrap, pgAdmin, Alertify, codemirror,
-  checkNodeVisibility, toolBar, help, csrfToken, pgadminUtils, pgWindow
+  checkNodeVisibility, toolBar, help, csrfToken, pgadminUtils, pgWindow,
+  Kerberos
 ) {
   window.jQuery = window.$ = $;
   // Some scripts do export their object in the window only.
@@ -38,6 +41,8 @@ define('pgadmin.browser', [
 
   csrfToken.setPGCSRFToken(pgAdmin.csrf_token_header, pgAdmin.csrf_token);
 
+  Kerberos.validate_kerberos_ticket();
+
   var panelEvents = {};
   panelEvents[wcDocker.EVENT.VISIBILITY_CHANGED] = function() {
     if (this.isVisible()) {
diff --git a/web/pgadmin/browser/tests/test_kerberos_with_mocking.py b/web/pgadmin/browser/tests/test_kerberos_with_mocking.py
index f31e983ff..6b61dc1d0 100644
--- a/web/pgadmin/browser/tests/test_kerberos_with_mocking.py
+++ b/web/pgadmin/browser/tests/test_kerberos_with_mocking.py
@@ -12,6 +12,7 @@ from pgadmin.utils.route import BaseTestGenerator
 from regression.python_test_utils import test_utils as utils
 from pgadmin.authenticate.registry import AuthSourceRegistry
 from unittest.mock import patch, MagicMock
+from werkzeug.datastructures import Headers

 class KerberosLoginMockTestCase(BaseTestGenerator):
@@ -30,6 +31,11 @@ class KerberosLoginMockTestCase(BaseTestGenerator):
             auth_source=['kerberos'],
             auto_create_user=True,
             flag=2
+        )),
+        ('Spnego/Kerberos Update Ticket', dict(
+            auth_source=['kerberos'],
+            auto_create_user=True,
+            flag=3
         ))
     ]
 
@@ -54,8 +60,13 @@ class KerberosLoginMockTestCase(BaseTestGenerator):
                 self.skipTest(
                     "Can not run Kerberos Authentication in the Desktop mode."
                 )
-
             self.test_authorized()
+        elif self.flag == 3:
+            if app_config.SERVER_MODE is False:
+                self.skipTest(
+                    "Can not run Kerberos Authentication in the Desktop mode."
+                )
+            self.test_update_ticket()
 
     def test_unauthorized(self):
         """
@@ -73,13 +84,7 @@ class KerberosLoginMockTestCase(BaseTestGenerator):
         passed on to the routed method.
         """
 
-        class delCrads:
-            def __init__(self):
-                self.initiator_name = [email-masked]'
-        del_crads = delCrads()
-
-        AuthSourceRegistry.registry['kerberos'].negotiate_start = MagicMock(
-            return_value=[True, del_crads])
+        del_crads = self.mock_negotiate_start()
         res = self.tester.login(None,
                                 None,
                                 True,
@@ -89,6 +94,33 @@ class KerberosLoginMockTestCase(BaseTestGenerator):
         respdata = 'Gravatar image for %s' % del_crads.initiator_name
         self.assertTrue(respdata in res.data.decode('utf8'))
 
+    def mock_negotiate_start(self):
+        class delCrads:
+            def __init__(self):
+                self.initiator_name = [email-masked]'
+
+        del_crads = delCrads()
+
+        AuthSourceRegistry.registry['kerberos'].negotiate_start = MagicMock(
+            return_value=[True, del_crads])
+        return del_crads
+
+    def test_update_ticket(self):
+        # Response header should include the Negotiate header in the first call
+        response = self.tester.get('/authenticate/kerberos/update_ticket')
+        self.assertEqual(response.status_code, 401)
+        self.assertEqual(response.headers.get('www-authenticate'), 'Negotiate')
+
+        # When we send the Kerberos Ticket, it should return  success
+        del_crads = self.mock_negotiate_start()
+
+        krb_token = Headers({})
+        krb_token['Authorization'] = 'Negotiate CTOKEN'
+
+        response = self.tester.get('/authenticate/kerberos/update_ticket',
+                                   headers=krb_token)
+        self.assertEqual(response.status_code, 200)
+
     def tearDown(self):
         self.app.PGADMIN_EXTERNAL_AUTH_SOURCE = 'ldap'
 
diff --git a/web/pgadmin/misc/bgprocess/processes.py b/web/pgadmin/misc/bgprocess/processes.py
index ef6cfc3f2..25e0a2a9e 100644
--- a/web/pgadmin/misc/bgprocess/processes.py
+++ b/web/pgadmin/misc/bgprocess/processes.py
@@ -24,10 +24,11 @@ import logging
 from pgadmin.utils import u_encode, file_quote, fs_encoding, \
     get_complete_file_path, get_storage_directory, IS_WIN
 from pgadmin.browser.server_groups.servers.utils import does_server_exists
+from pgadmin.utils.constants import KERBEROS
 
 import pytz
 from dateutil import parser
-from flask import current_app
+from flask import current_app, session
 from flask_babelex import gettext as _
 from flask_security import current_user
 
@@ -278,13 +279,16 @@ class BatchProcess(object):
         env['PROCID'] = self.id
         env['OUTDIR'] = self.log_dir
         env['PGA_BGP_FOREGROUND'] = "1"
+        if config.SERVER_MODE and session and \
+                session['_auth_source_manager_obj']['current_source'] == \
+                KERBEROS:
+            env['KRB5CCNAME'] = session['KRB5CCNAME']
 
         if self.env:
             env.update(self.env)
 
         if cb is not None:
             cb(env)
-
         if os.name == 'nt':
             DETACHED_PROCESS = 0x00000008
             from subprocess import CREATE_NEW_PROCESS_GROUP
diff --git a/web/pgadmin/model/__init__.py b/web/pgadmin/model/__init__.py
index d1f498181..dfacf6322 100644
--- a/web/pgadmin/model/__init__.py
+++ b/web/pgadmin/model/__init__.py
@@ -29,7 +29,7 @@ from flask_sqlalchemy import SQLAlchemy
 #
 ##########################################################################
 
-SCHEMA_VERSION = 27
+SCHEMA_VERSION = 28
 
 ##########################################################################
 #
@@ -184,6 +184,7 @@ class Server(db.Model):
     tunnel_identity_file = db.Column(db.String(64), nullable=True)
     tunnel_password = db.Column(db.String(64), nullable=True)
     shared = db.Column(db.Boolean(), nullable=False)
+    kerberos_conn = db.Column(db.Boolean(), nullable=False)
 
     @property
     def serialize(self):
diff --git a/web/pgadmin/setup/data_directory.py b/web/pgadmin/setup/data_directory.py
index 2335b0790..7a3654b77 100644
--- a/web/pgadmin/setup/data_directory.py
+++ b/web/pgadmin/setup/data_directory.py
@@ -104,3 +104,19 @@ def create_app_data_directory(config):
                 getpass.getuser(),
                 config.APP_VERSION))
         exit(1)
+
+    # Create Kerberos Credential Cache directory (if not present).
+    try:
+        _create_directory_if_not_exists(config.KERBEROS_CCACHE_DIR)
+    except PermissionError as e:
+        print(FAILED_CREATE_DIR.format(config.KERBEROS_CCACHE_DIR, e))
+        print(
+            "HINT   : Create the directory {}, ensure it is writable by\n"
+            "         '{}', and try again, or, create a config_local.py file\n"
+            "         and override the KERBEROS_CCACHE_DIR setting per\n"
+            "         https://www.pgadmin.org/docs/pgadmin4/{}/config_py.html".
+            format(
+                config.KERBEROS_CCACHE_DIR,
+                getpass.getuser(),
+                config.APP_VERSION))
+        exit(1)
diff --git a/web/pgadmin/tools/backup/static/js/backup_dialog_wrapper.js b/web/pgadmin/tools/backup/static/js/backup_dialog_wrapper.js
index 4f89e5bb7..5e4db20a9 100644
--- a/web/pgadmin/tools/backup/static/js/backup_dialog_wrapper.js
+++ b/web/pgadmin/tools/backup/static/js/backup_dialog_wrapper.js
@@ -13,6 +13,7 @@ import gettext from '../../../../static/js/gettext';
 import url_for from '../../../../static/js/url_for';
 import _ from 'underscore';
 import {DialogWrapper} from '../../../../static/js/alertify/dialog_wrapper';
+import {fetch_ticket_lifetime} from  '../../../../authenticate/static/js/kerberos';
 
 export class BackupDialogWrapper extends DialogWrapper {
   constructor(dialogContainerSelector, dialogTitle, typeOfDialog,
@@ -165,10 +166,29 @@ export class BackupDialogWrapper extends DialogWrapper {
       );
 
       this.setExtraParameters(selectedTreeNode, treeInfo);
+      let backupDate = this.view.model.toJSON();
+
+      if(backupDate.type == 'globals' || backupDate.type == 'server') {
+        let newPromise = fetch_ticket_lifetime();
+        newPromise.then(
+          function(lifetime) {
+            if (lifetime < 1800 && lifetime  > 0) {
+              dialog.alertify.warning(
+                'You have '+ (Math.round(parseInt(lifetime)/60)).toString() +' minutes left on your ticket - if the dump takes longer than that, it may fail."'
+              );
+            }
+          },
+          function() {
+            dialog.alertify.warning(
+              gettext('Please renew your kerberos ticket, it has been expired.')
+            );
+          }
+        );
+      }
 
       axios.post(
         baseUrl,
-        this.view.model.toJSON()
+        backupDate
       ).then(function (res) {
         if (res.data.success) {
           dialog.alertify.success(gettext('Backup job created.'), 5);
diff --git a/web/pgadmin/tools/debugger/static/js/debugger.js b/web/pgadmin/tools/debugger/static/js/debugger.js
index f31a0fc00..460a200bb 100644
--- a/web/pgadmin/tools/debugger/static/js/debugger.js
+++ b/web/pgadmin/tools/debugger/static/js/debugger.js
@@ -13,11 +13,11 @@ define([
   'backbone', 'pgadmin.backgrid', 'codemirror', 'pgadmin.backform',
   'pgadmin.tools.debugger.ui', 'pgadmin.tools.debugger.utils',
   'tools/datagrid/static/js/show_query_tool', 'sources/utils',
-  'wcdocker', 'pgadmin.browser.frame',
+  'pgadmin.authenticate.kerberos', 'wcdocker', 'pgadmin.browser.frame',
 ], function(
   gettext, url_for, $, _, Alertify, pgAdmin, pgBrowser, Backbone, Backgrid,
   CodeMirror, Backform, get_function_arguments, debuggerUtils, showQueryTool,
-  pgadminUtils,
+  pgadminUtils, Kerberos
 ) {
   var pgTools = pgAdmin.Tools = pgAdmin.Tools || {},
     wcDocker = window.wcDocker;
@@ -472,8 +472,20 @@ define([
         .fail(function(xhr) {
           try {
             var err = JSON.parse(xhr.responseText);
-            if (err.success == 0) {
-              Alertify.alert(gettext('Debugger Error'), err.errormsg);
+            if (err.errormsg.search('Ticket expired') !== -1) {
+              let fetchTicket = Kerberos.fetch_ticket();
+              fetchTicket.then(
+                function() {
+                  self.start_global_debugger();
+                },
+                function(error) {
+                  Alertify.alert(gettext('Debugger Error'), error);
+                }
+              );
+            } else {
+              if (err.success == 0) {
+                Alertify.alert(gettext('Debugger Error'), err.errormsg);
+              }
             }
           } catch (e) {
             console.warn(e.stack || e);
diff --git a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
index b5503255c..631e9d0da 100644
--- a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
+++ b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
@@ -51,6 +51,7 @@ define('tools.querytool', [
   'sources/window',
   'sources/is_native',
   'sources/sqleditor/macro',
+  'pgadmin.authenticate.kerberos',
   'sources/../bundle/slickgrid',
   'pgadmin.file_manager',
   'slick.pgadmin.formatters',
@@ -65,7 +66,7 @@ define('tools.querytool', [
   GeometryViewer, historyColl, queryHist, querySources,
   keyboardShortcuts, queryToolActions, queryToolNotifications, Datagrid,
   modifyAnimation, calculateQueryRunTime, callRenderAfterPoll, queryToolPref, queryTxnStatus, csrfToken, panelTitleFunc,
-  pgWindow, isNative, MacroHandler) {
+  pgWindow, isNative, MacroHandler, Kerberos) {
   /* Return back, this has been called more than once */
   if (pgAdmin.SqlEditor)
     return pgAdmin.SqlEditor;
@@ -2441,9 +2442,23 @@ define('tools.querytool', [
               pgBrowser.report_error(gettext('Error fetching rows - %s.', xhr.statusText), xhr.responseJSON.errormsg, undefined, self.close.bind(self));
             }
           } else {
-            pgBrowser.Events.trigger(
-              'pgadmin:query_tool:connected_fail:' + self.transId, xhr, error
-            );
+            if (xhr.responseText.search('Ticket expired') !== -1) {
+              let fetchTicket = Kerberos.fetch_ticket();
+              fetchTicket.then(
+                function() {
+                  self.initTransaction();
+                },
+                function(error) {
+                  pgBrowser.Events.trigger(
+                    'pgadmin:query_tool:connected_fail:' + self.transId, xhr, error
+                  );
+                }
+              );
+            } else {
+              pgBrowser.Events.trigger(
+                'pgadmin:query_tool:connected_fail:' + self.transId, xhr, error
+              );
+            }
           }
         });
       },
diff --git a/web/pgadmin/utils/driver/psycopg2/connection.py b/web/pgadmin/utils/driver/psycopg2/connection.py
index de9547322..43f6e6de0 100644
--- a/web/pgadmin/utils/driver/psycopg2/connection.py
+++ b/web/pgadmin/utils/driver/psycopg2/connection.py
@@ -18,11 +18,13 @@ import select
 import datetime
 from collections import deque
 import psycopg2
-from flask import g, current_app
+import threading
+from flask import g, current_app, session
 from flask_babelex import gettext
 from flask_security import current_user
 from pgadmin.utils.crypto import decrypt, encrypt
 from psycopg2.extensions import encodings
+from os import environ
 
 import config
 from pgadmin.model import User
@@ -38,6 +40,9 @@ from .encoding import get_encoding, configure_driver_encodings
 from pgadmin.utils import csv
 from pgadmin.utils.master_password import get_crypt_key
 from io import StringIO
+from pgadmin.utils.constants import KERBEROS
+
+lock = threading.Lock()
 
 _ = gettext
 
@@ -313,6 +318,12 @@ class Connection(BaseConnection):
             os.environ['PGAPPNAME'] = '{0} - {1}'.format(
                 config.APP_NAME, conn_id)
 
+            if config.SERVER_MODE and \
+                    session['_auth_source_manager_obj']['current_source'] == \
+                    KERBEROS and 'KRB5CCNAME' in session:
+                lock.acquire()
+                environ['KRB5CCNAME'] = session['KRB5CCNAME']
+
             pg_conn = psycopg2.connect(
                 host=manager.local_bind_host if manager.use_ssh_tunnel
                 else manager.host,
@@ -340,7 +351,13 @@ class Connection(BaseConnection):
             if self.async_ == 1:
                 self._wait(pg_conn)
 
+            if config.SERVER_MODE and \
+                    session['_auth_source_manager_obj']['current_source'] == \
+                    KERBEROS:
+                environ['KRB5CCNAME'] = ''
+
         except psycopg2.Error as e:
+            environ['KRB5CCNAME'] = ''
             manager.stop_ssh_tunnel()
             if e.pgerror:
                 msg = e.pgerror
@@ -358,6 +375,11 @@ class Connection(BaseConnection):
                 )
             )
             return False, msg
+        finally:
+            if config.SERVER_MODE and \
+                    session['_auth_source_manager_obj']['current_source'] == \
+                    KERBEROS and lock.locked():
+                lock.release()
 
         # Overwrite connection notice attr to support
         # more than 50 notices at a time
@@ -1435,7 +1457,6 @@ Failed to reset the connection to the server due to following error:
         Args:
             conn: connection object
         """
-
         while True:
             state = conn.poll()
             if state == psycopg2.extensions.POLL_OK:
diff --git a/web/webpack.shim.js b/web/webpack.shim.js
index 96d5b27f6..00daa12ec 100644
--- a/web/webpack.shim.js
+++ b/web/webpack.shim.js
@@ -174,6 +174,7 @@ var webpackShimConfig = {
     'pgadmin.backgrid': path.join(__dirname, './pgadmin/static/js/backgrid.pgadmin'),
 
     'pgadmin.about': path.join(__dirname, './pgadmin/about/static/js/about'),
+    'pgadmin.authenticate.kerberos': path.join(__dirname, './pgadmin/authenticate/static/js/kerberos'),
     'pgadmin.browser': path.join(__dirname, './pgadmin/browser/static/js/browser'),
     'pgadmin.browser.bgprocess': path.join(__dirname, './pgadmin/misc/bgprocess/static/js/bgprocess'),
     'pgadmin.browser.collection': path.join(__dirname, './pgadmin/browser/static/js/collection'),

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 26 Apr 2021 12:42:32 +0530

Hi Khushboo

I have applied your patch and started testing it in different
scenarios. Following
are the GUI review comments:

   - Update the comments about Kerberos support for AUTHENTICATION_SOURCES
   in config.py.
   - You will have to create a migration file again. Getting "Error:
   Multiple head revisions are present for given argument"
   - Increase the height of the server dialog as after adding "Kerberos
   Authentication?" switch Connection tab showing scroll bars.
   - Desktop/Server mode Getting No such file or directory:
   '/var/lib/pgadmin/krbccache'. KERBEROS_CCACHE_DIR should only be created
   in Server Mode and AUTHENTICATION_SOURCES is 'kerberos'.
   - Server Dialog "Kerberos Authentication?" switch control should be
   enabled only in Server Mode and AUTHENTICATION_SOURCES is 'kerberos'.
   - "Kerberos Authentication?" switch should be disabled when the server
   is connected.
   - In Desktop mode AUTHENTICATION_SOURCES must be '*internal*' doesn't
   matter what mode is provided in *config.py *or* config_local.py*. In
   fact, we should create a flag '*authentication_mode*' which will be set
   after the valid authentication source has been detected/connected. *For
   example,* the user has provided  AUTHENTICATION_SOURCES = ['kerberos',
   'internal'], it is unable to connect using kerberos and then the user has
   provided a valid email and password so we will set
'*authentication_mode*' to
   'internal' and the rest of the logic will be based on that flag.

   - Connect to any database server and check backend logs following error
   is visible:
      - KeyError: 'KRB5CCNAME'  *Solution*: It should not call
      "kerberos_validate_ticket()" function until AUTHENTICATION_SOURCES is
      'kerberos' and Server Mode is true.

*AUTHENTICATION_SOURCES = ['kerberos']:*

   - Kerberos is not set up: Open pgAdmin page, enter email and password
   two message box popped up one with valid Kerberos error and the second one
   with "None" as a string.
   - Similarly, if AUTHENTICATION_SOURCES = ['kerberos', 'internal'] and it
   is failed to connect using kerberos, then provide an email, and the wrong
   password two message boxes popped up one with Kerberos error and another
   with Password error.
   - In the User Management dialog 'kerberos' should not be visible in the
   authentication source dropdown. As there is no point creating kerberos user
   from there.
   - Add local server(without kerberos) to the browser tree, set "Kerberos
   Authentication?" to True, try to connect by providing the password it
   always returns "fe_sendauth: no password supplied" error. If possible can
   we identify and change the error message?
   - Add database server where kerberos authentication is ON, make changes
   in pg_hba.conf with the wrong user name, then try to connect to the
   database server. The server tries to connect and the spinner is visible and
   never stops. It should raise a proper error message. There are some other
   scenarios where entries in pg_hba.conf is wrong.
   - *Suggestion 1*: As per current implementation even if  "Kerberos
   Authentication?" is set to false the user can connect to the database
   server by providing any password or blank password. It is difficult for the
   user to identify it is connected using GSSAPI. I would suggest providing
   the control in the properties dialog which tells the database server is
   connected using GSSAPI.
   - *Suggestion 2*: If it is possible to detect that the database server
   is connected using Kerberos then we should disable the 'Username' control
   as for Kerberos both the users (pgadmin user and database user ) must be
   the same.

*Note:- *pgAdmin on OSX not working with Kerberos authentication. Failed
with error "Your GSSAPI implementation does not have support for
manipulating credential stores directly" Need to document this behavior.

*Code review still remains, which I'll be started after the above fixes.*

-- 
*Thanks & Regards*
*Akshay Joshi*
*pgAdmin Hacker | Principal Software Architect*
*EDB Postgres <http://edbpostgres.com>*

*Mobile: +91 976-788-8246*

---

From: Khushboo Vashi <[email-masked]>
Date: Mon, 3 May 2021 14:50:56 +0530

Hi Akshay,

Please find the attached updated patch.

Thanks,
Khushboo

On Mon, Apr 26, 2021 at 12:42 PM Akshay Joshi <[email-masked]>
wrote:

> Hi Khushboo
>
> I have applied your patch and started testing it in different scenarios. Following
> Done.

> Done.

> This is the default behaviour of all the dialogues, for example: Table
Advanced tab

>    '/var/lib/pgadmin/krbccache'. KERBEROS_CCACHE_DIR should only be
>    created in Server Mode and AUTHENTICATION_SOURCES is 'kerberos'.
>
> Done

> Done

> Even if the user changes the setting when the server is connected, the
effect will take place only on reconnection, so I think we can leave it as
it is.

>    fact, we should create a flag '*authentication_mode*' which will be
>    set after the valid authentication source has been detected/connected. *For
>    provided a valid email and password so we will set '
>    *authentication_mode*' to 'internal' and the rest of the logic will be
>    based on that flag.
>
> This was already taken care of.

>
>    -
>
>
>    - Connect to any database server and check backend logs following
>    error is visible:
> Fixed.

> Fixed

>
>    - Similarly, if AUTHENTICATION_SOURCES = ['kerberos', 'internal'] and
>    it is failed to connect using kerberos, then provide an email, and the
>    wrong password two message boxes popped up one with Kerberos error and
>    another with Password error.
>
> Somehow, I couldn't find the fix for this issue, for now we can ignore
this as this will not affect the login process.

>
>    - In the User Management dialog 'kerberos' should not be visible in
>    the authentication source dropdown. As there is no point creating kerberos
>    user from there.
>
> We have provided an option to add manual users for Kerberos also the same
as LDAP.

>
>    - Add local server(without kerberos) to the browser tree, set
>    "Kerberos Authentication?" to True, try to connect by providing the
>    password it always returns "fe_sendauth: no password supplied" error. If
>    possible can we identify and change the error message?
>
> Fixed

>
>    - Add database server where kerberos authentication is ON, make
>    changes in pg_hba.conf with the wrong user name, then try to connect to the
> Fixed

> I have removed the old implementation in which the user was able to
connect the PostgresQL even if a user has not selected "Kerberos
Authentication" but we have a valid kerberos ticket and pg_hba is
configured to support it. So, now users can get the idea about the
connection through The "Kerberos authentication" flag displayed on the
properties tab.

> manipulating credential stores directly" Need to document this behavior.

Thanks,
khushboo

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 3 May 2021 16:11:54 +0530

Thanks, patch applied.

On Mon, May 3, 2021 at 2:50 PM Khushboo Vashi <
[email-masked]> wrote:

> On Mon, Apr 26, 2021 at 12:42 PM Akshay Joshi <
> [email-masked]> wrote:
>
>> Hi Khushboo
>>
>>    - "Kerberos Authentication?" switch should be disabled when the
>>    server is connected.
>>
>>    example,* the user has provided  AUTHENTICATION_SOURCES =
>>    ['kerberos', 'internal'], it is unable to connect using kerberos and then
>>    the user has provided a valid email and password so we will set '
>>    *authentication_mode*' to 'internal' and the rest of the logic will
>>    be based on that flag.
>>
>>    - *Suggestion 2*: If it is possible to detect that the database
>>    server is connected using Kerberos then we should disable the 'Username'
>>    control as for Kerberos both the users (pgadmin user and database user )
>>    must be the same.
>>
>>
>>>> This patch includes the support for logging into PostgreSQL servers
>>>> with Kerberos authentication.
>>>>
>>>> Thanks,
>>>> Khushboo
>>>>
>>>>
>>
>> --
>> *Thanks & Regards*
>> *Akshay Joshi*