Index: main/trunk/greenstone3/src/java/org/greenstone/gsdl3/GoogleSigninJDBCRealm.java
===================================================================
--- main/trunk/greenstone3/src/java/org/greenstone/gsdl3/GoogleSigninJDBCRealm.java	(revision 35346)
+++ main/trunk/greenstone3/src/java/org/greenstone/gsdl3/GoogleSigninJDBCRealm.java	(revision 35346)
@@ -0,0 +1,449 @@
+/*
+ *    GoogleSigninJDBCRealm.java
+ *    Copyright (C) 2021 New Zealand Digital Library, http://www.nzdl.org
+ *
+ *    This program is free software; you can redistribute it and/or modify
+ *    it under the terms of the GNU General Public License as published by
+ *    the Free Software Foundation; either version 2 of the License, or
+ *    (at your option) any later version.
+ *
+ *    This program is distributed in the hope that it will be useful,
+ *    but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *    GNU General Public License for more details.
+ *
+ *    You should have received a copy of the GNU General Public License
+ *    along with this program; if not, write to the Free Software
+ *    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+ */
+
+package org.greenstone.gsdl3;
+
+import java.security.Principal;
+import java.security.GeneralSecurityException;
+import java.security.SecureRandom;
+import java.sql.Connection;
+import java.sql.Driver;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.Enumeration;
+import java.util.HashMap;
+import java.util.Hashtable;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
+import javax.servlet.http.HttpSession;
+
+import org.apache.catalina.realm.JDBCRealm;
+import org.apache.catalina.realm.GenericPrincipal;
+import org.apache.catalina.LifecycleException;
+import org.apache.juli.logging.Log;
+import org.apache.juli.logging.LogFactory;
+import org.apache.tomcat.util.ExceptionUtils;
+
+import com.google.api.client.http.HttpTransport;
+import com.google.api.client.http.javanet.NetHttpTransport;
+import com.google.api.client.json.JsonFactory;
+import com.google.api.client.json.gson.GsonFactory;
+import com.google.api.client.googleapis.auth.oauth2.GoogleIdToken;
+import com.google.api.client.googleapis.auth.oauth2.GoogleIdToken.Payload;
+import com.google.api.client.googleapis.auth.oauth2.GoogleIdTokenVerifier;
+
+
+import org.greenstone.gsdl3.util.GSParams;
+
+
+// Custome Realm class desgin loosely based off (in order) details in:
+//   https://dzone.com/articles/how-to-implement-a-new-realm-in-tomcat
+//   https://blog.krybot.com/a?ID=01300-14edb945-73b0-433b-8e80-c6870e350cf2
+//   https://developers.redhat.com/blog/2017/06/20/how-to-implement-a-new-realm-in-tomcat
+
+// Example MBeans XML descriptor file, see:
+//   https://alvinalexander.com/java/jwarehouse/apache-tomcat-6.0.16/java/org/apache/catalina/realm/mbeans-descriptors.xml.shtml
+// the one used in Greenstone is based off the JDBCRealm entry in mbeans-decriptors.xml found in the Tomcat source code
+//
+
+// In terms of addin in DEBUG statements, you need to trigger this through
+//   tomcat/conf/logging.properies:
+// Otherwise even the 'old faithful' approach of printing all debug statements
+// to STDERR goes nowhere!
+//
+// Helpful details at:
+//  https://stackoverflow.com/questions/30333709/how-to-debug-tomcat-ldap-realm-queries
+//
+// The key thing to add in to tomcat/conf/logging.properies is the following, and then
+//   System.err.println() statements will turn up in tomcat/logs/catalina.out.
+//   For simple debugging opting to use STDERR should be sufficient for most situations,
+//   PLUS it has the added bonus of avoiding the issue of needing to add in the jar file(s)
+//   for logging Tomcat uses into the Greenstone compile area
+
+/* Added to 'logging.properties'
+
+############################################################
+# Facility specific properties.
+# Provides extra control for each logger.
+############################################################
+# This would turn on trace-level for everything
+# the possible levels are: SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST or ALL
+#org.apache.catalina.level = ALL
+#org.apache.catalina.handlers = 2localhost.org.apache.juli.FileHandler
+org.apache.catalina.realm.level = ALL
+org.apache.catalina.realm.useParentHandlers = true
+org.apache.catalina.authenticator.level = ALL
+org.apache.catalina.authenticator.useParentHandlers = true
+
+org.apache.catalina.core.ContainerBase.[Catalina].[localhost].level = INFO
+org.apache.catalina.core.ContainerBase.[Catalina].[localhost].handlers = 2localhost.org.apache.juli.FileHandler
+
+*/
+
+	    
+public class GoogleSigninJDBCRealm extends JDBCRealm
+{
+
+    public static String GOOGLESIGNIN_USERNAME_BRIDGE = "googlesignin";
+    
+    // MBean related components
+
+    /**
+     * The column name used for the user's email address
+     */
+    protected String userEmailCol = null;
+
+    /**
+     * @return the column name used for the user's email address
+     */
+    public String getUserEmailCol() {
+        return userEmailCol;
+    }
+
+    /**
+     * Set the column name used for the user's email address
+     *
+     * @param userEmailCol The column name used for the user's email address
+     */
+    public void setUserEmailCol( String userEmailCol ) {
+      this.userEmailCol = userEmailCol;
+    }
+
+    /**
+     * The Google Client API ID to use when trying verify users (e.g., via a Google ID Token)
+     */
+    protected String googlesigninClientId = null;
+
+    /**
+     * @return the Google Client API ID to use when trying verify users (e.g., via a Google ID Token)
+     */
+    public String getGooglesigninClientId() {
+        return googlesigninClientId;
+    }
+
+    /**
+     * Set the Google Client API ID to use when trying verify users (e.g., via a Google ID Token)
+     *
+     * @param googlesigninClientId The Google Client API ID
+     */
+    public void setGooglesigninClientId( String googlesigninClientId ) {
+      this.googlesigninClientId = googlesigninClientId;
+    }
+    
+    
+    /**
+     * The PreparedStatement to use for mapping an email address  to a username
+     */
+    protected PreparedStatement preparedEmailToUsername = null;
+
+
+    protected static GoogleIdTokenVerifier google_id_token_verifier = null;
+
+    /**
+     * Prepare for the beginning of active use of the public methods of this
+     * component and implement the requirements of
+     * {@link org.apache.catalina.util.LifecycleBase#startInternal()}.
+     *
+     * @exception LifecycleException if this component detects a fatal error
+     *  that prevents this component from being used
+     */
+    @Override
+    protected void startInternal() throws LifecycleException
+    {
+	super.startInternal();
+	
+	initGoogleIdTokenVerifier(googlesigninClientId);	
+    }
+
+    // **** XXXX !!!!
+    protected static void initGoogleIdTokenVerifier(String googlesignin_client_id)
+    {
+	// Based on:
+	//   https://developers.google.com/identity/sign-in/web/backend-auth
+	// With some additional details cribbed from
+	//   https://stackoverflow.com/questions/10835365/authenticate-programmatically-to-google-with-oauth2
+		
+	//containerLog.debug("**** GoogleSigninJDBCRealm::initGoogleIdTokenVerifier():" + googlesignin_client_id);
+	System.err.println("**** GoogleSigninJDBCRealm::initGoogleIdTokenVerifier() googlesignin_client_id=" + googlesignin_client_id);
+	//GoogleSigninJDBCRealm.googlesignin_client_id = googlesignin_client_id;
+	
+	HttpTransport transport = new NetHttpTransport();
+	JsonFactory jsonFactory = new GsonFactory();
+	
+	List<String> googlesignin_client_ids = Collections.singletonList(googlesignin_client_id);
+	
+	google_id_token_verifier = new GoogleIdTokenVerifier.Builder(transport, jsonFactory)
+	    //.setAudience(Collections.singletonList(googlesignin_client_id))
+	    .setAudience(googlesignin_client_ids)
+	    //.setAuthorizedParty(Collections.singletonList(googlesignin_clent_id))
+	    .build();
+    }
+
+    /*
+    protected GoogleSigninJDBCRealm()
+    {
+	if (google_id_token_verifier == null) {
+	    initGoogleIdTokenVerifier();
+	}
+    }
+    */
+
+    
+    /**
+     * Return a PreparedStatement configured to perform the SELECT required
+     * to retrieve username for the specified user email address.
+     *
+     * @param dbConnection The database connection to be used
+     * @param emailAddress Email address for which username should be retrieved
+     * @return the prepared statement
+     * @exception SQLException if a database error occurs
+     */
+    protected PreparedStatement emailToUsername(Connection dbConnection,
+						String emailAddress)
+	throws SQLException
+    {
+        if (preparedEmailToUsername == null) {
+            StringBuilder sb = new StringBuilder("SELECT ");
+            sb.append(userNameCol);
+            sb.append(" FROM ");
+            sb.append(userTable);
+            sb.append(" WHERE ");
+            sb.append(userEmailCol);
+            sb.append(" = ?");
+
+            if(containerLog.isDebugEnabled()) {
+                containerLog.debug("emailToUsername query: " + sb.toString());
+            }
+
+            preparedEmailToUsername =
+                dbConnection.prepareStatement(sb.toString());
+        }
+
+        if (emailAddress == null) {
+            preparedEmailToUsername.setNull(1,java.sql.Types.VARCHAR);
+        } else {
+            preparedEmailToUsername.setString(1, emailAddress);
+        }
+
+        return preparedEmailToUsername;
+    }
+
+
+    /**
+     * Get the username for the specified email address
+     * @param email_address The email address
+     * @return the username associated with the given principal's email address
+     */
+    protected synchronized String getUsernameFromEmail(String email_address) {
+
+        // Look up the username
+        String dbUsername = null;
+
+        // Number of tries is the number of attempts to connect to the database
+        // during this login attempt (if we need to open the database)
+        // This needs rewritten with better pooling support, the existing code
+        // needs signature changes since the Prepared statements needs cached
+        // with the connections.
+        // The code below will try twice if there is an SQLException so the
+        // connection may try to be opened again. On normal conditions (including
+        // invalid login - the above is only used once.
+        int numberOfTries = 2;
+        while (numberOfTries > 0) {
+            try {
+                // Ensure that we have an open database connection
+                open();
+
+                PreparedStatement stmt = emailToUsername(dbConnection, email_address);
+                try (ResultSet rs = stmt.executeQuery()) {
+                    if (rs.next()) {
+                        dbUsername = rs.getString(1);
+                    }
+
+                    dbConnection.commit();
+
+                    if (dbUsername != null) {
+                        dbUsername = dbUsername.trim();
+                    }
+
+                    return dbUsername;
+                }
+            } catch (SQLException e) {
+                // Log the problem for posterity
+                containerLog.error(sm.getString("jdbcRealm.exception"), e);
+            }
+
+            // Close the connection so that it gets reopened next time
+            if (dbConnection != null) {
+                close(dbConnection);
+            }
+
+            numberOfTries--;
+        }
+
+        return null;
+    }
+    
+    protected String mapFromGoogleEmailToGreenstoneUser(String google_email)	
+    {
+	String greenstone_username = null;
+
+	return greenstone_username;
+    }
+	    
+    public String getGreenstoneUsernameFromGoogleTokenId(String googlesignin_id_token)
+    {	
+	System.err.println("**** GoogleSigninJDBCRealm::getGreenstoneUsernameFromGoogleTokenId():" + googlesignin_id_token);
+	
+	String greenstone_username = null;
+	
+	if (googlesignin_id_token != null) {
+	    try {		
+		GoogleIdToken idToken = google_id_token_verifier.verify(googlesignin_id_token);
+		
+		if (idToken != null) {
+		    Payload payload = idToken.getPayload();
+		    		    
+		    // Get profile information from payload
+		    String google_user        = payload.getSubject();
+		    String google_user_email  = payload.getEmail();
+		    boolean verified          = Boolean.valueOf(payload.getEmailVerified());
+		    
+		    //String name       = (String) payload.get("name");
+		    //String pictureUrl = (String) payload.get("picture");
+		    //String locale     = (String) payload.get("locale");
+		    //String familyName = (String) payload.get("family_name");
+		    //String givenName  = (String) payload.get("given_name");
+		    
+
+		    if (verified) {
+			greenstone_username = getUsernameFromEmail(google_user_email);
+			if (greenstone_username == null) {
+			    System.err.println("Google login successful with verified email address '"+google_user_email+"' HOWEVER no matching email entry fround in Greenstone JDBC UserTable");
+			}
+		    }		    
+		    else {
+			System.err.println("Google login successful, but email address '"+google_user_email+"' not verified by Google => Reject login attempt for Greenstone");
+		    }		    
+		    
+		}
+		else {
+		    System.err.println("Could not verify Google ID token: '" + googlesignin_id_token + "'");
+		}
+	    }
+	    catch (Exception e) {
+		System.err.println("Exception thrown when verifing Google ID token: '" + googlesignin_id_token + "'");
+		e.printStackTrace();
+		
+	    }
+	}
+	else {
+	    System.err.println("***** No googlesignin_id_token detected.  No Google Signin check to do");
+	}
+	
+	System.err.println("***** End of getGoogleSinginInfo()");
+	
+	return greenstone_username;
+    }
+
+    // **** !!!! XXXX
+    
+    @Override
+    public Principal authenticate(String username, String credentials)
+    {
+	Principal principal = null;
+	
+	if (username.equals(GOOGLESIGNIN_USERNAME_BRIDGE)) {
+	    System.err.println("GoogleSigninJDBCRealm::authenticate(): detected googlesignin");
+
+	    // System.err.println("***** google_id_token_verifier = " + google_id_token_verifier);
+	    //System.err.println("GoogleSigninJDBCReal::authenticate(): username=" + username);
+	    //System.err.println("GoogleSigninJDBCReal::authenticate(): credentials=" + credentials);
+	    
+	    // Google Client Token ID has been passed in as 'credentials'
+	    String google_user_email = getGreenstoneUsernameFromGoogleTokenId(credentials);
+
+	    if (google_user_email != null) {
+		String google_username = google_user_email.replaceAll("@.*$","");
+		System.err.println("**** Using the following username derived from verified Google email address as Greenstone3 username = '" + google_username + "'");
+		
+		principal = super.getPrincipal(google_username);
+	    }
+	    else {
+		System.err.println("GoogleSigninJDBCRealm::authenticate(): failed to match 'google_id_token' to valid Greenstone user account");
+	    }
+	}
+	else {
+	    // Regular Greenstone3 User Login case
+	    principal = super.authenticate(username,credentials);
+	}
+		
+	return principal;
+    }
+
+
+    /**
+     * Close the specified database connection.
+     *
+     * @param dbConnection The connection to be closed
+     */
+    protected void close(Connection dbConnection) {
+
+        // Do nothing if the database connection is already closed
+        if (dbConnection == null) {
+            return;
+        }
+
+        // Close our prepared statements (if any)
+        try {
+            preparedEmailToUsername.close();
+        } catch (Throwable f) {
+            ExceptionUtils.handleThrowable(f);
+        }
+        this.preparedEmailToUsername = null;
+
+	super.close(dbConnection);
+    }
+
+    /*
+    @Override
+    protected Principal getPrincipal(String string)
+    {
+	List<String> roles = new ArrayList<String>();
+
+	roles.add("TomcatAdmin");  // Adding role "TomcatAdmin" role to the user
+	//logger.info("Realm: "+this);
+	System.err.println("Realm: "+this);
+
+	Principal principal = new GenericPrincipal(username, password, roles);
+	//logger.info("Principal: "+principal);
+	System.err.println("Principal: "+principal);
+
+	return principal;
+    }
+    */
+    
+}
