From 64fdb2d3bc5a92c7c0e32cc2d0616fc7760ee7b6 Mon Sep 17 00:00:00 2001 From: Patrick Schmitz Date: Fri, 14 Dec 2012 11:33:40 -0800 Subject: [PATCH] CSPACE-5761 Added support to create databases as needed for repos on init. Sets up users, etc. Tested on Postgres, NOT TESTED on MySQL (but draws on the commands from the existing sql scripts, so should work). --- .../src/main/webapp/META-INF/context.xml | 24 +- services/common/pom.xml | 6 + .../services/common/ServiceMain.java | 206 +++++++++++++++++- .../services/common/storage/JDBCTools.java | 7 +- src/main/resources/db/mysql/init_nuxeo_db.sql | 15 +- .../resources/db/postgresql/init_nuxeo_db.sql | 11 +- 6 files changed, 255 insertions(+), 14 deletions(-) diff --git a/services/JaxRsServiceProvider/src/main/webapp/META-INF/context.xml b/services/JaxRsServiceProvider/src/main/webapp/META-INF/context.xml index de1031dcb..e17b748c0 100644 --- a/services/JaxRsServiceProvider/src/main/webapp/META-INF/context.xml +++ b/services/JaxRsServiceProvider/src/main/webapp/META-INF/context.xml @@ -82,8 +82,28 @@ + + + + ${spring.security.version} provided + + org.apache.tomcat + dbcp + 6.0.33 + provided + diff --git a/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java b/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java index 2a9cab010..1bb8c5460 100644 --- a/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java +++ b/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java @@ -34,6 +34,7 @@ import org.collectionspace.services.common.storage.DatabaseProductType; import org.collectionspace.services.config.ClientType; import org.collectionspace.services.config.ServiceConfig; import org.collectionspace.services.config.service.ServiceBindingType; +import org.collectionspace.services.config.tenant.RepositoryDomainType; import org.collectionspace.services.config.tenant.TenantBindingType; import org.collectionspace.services.config.types.PropertyItemType; import org.collectionspace.services.config.types.PropertyType; @@ -41,6 +42,8 @@ import org.collectionspace.services.nuxeo.client.java.NuxeoConnectorEmbedded; import org.collectionspace.services.nuxeo.client.java.TenantRepository; import org.jboss.resteasy.spi.ResteasyProviderFactory; +import org.apache.tomcat.dbcp.dbcp.BasicDataSource; + import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -147,9 +150,9 @@ public class ServiceMain { System.out.println("Resuming cspace services initialization."); } - setDataSources(); setServerRootDir(); readConfig(); + setDataSources(); propagateConfiguredProperties(); // // Start up and initialize our embedded Nuxeo server instance @@ -343,7 +346,11 @@ public class ServiceMain { * our instance of embedded Nuxeo, we can find our datasources. Therefore, we need to preserve the datasources in these * static members. */ - private void setDataSources() throws NamingException { + private void setDataSources() throws NamingException, Exception { + final String DB_EXISTS_QUERY_PSQL = + "SELECT 1 AS result FROM pg_database WHERE datname=?"; + final String DB_EXISTS_QUERY_MYSQL = + "SELECT 1 AS result FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=?"; // // As a side-effect of calling JDBCTools.getDataSource(...), the DataSource instance will be // cached in a static hash map of the JDBCTools class. This will speed up lookups as well as protect our @@ -351,10 +358,205 @@ public class ServiceMain { // DataSource cspaceDataSource = JDBCTools.getDataSource(JDBCTools.CSPACE_REPOSITORY_NAME); DataSource nuxeoDataSource = JDBCTools.getDataSource(JDBCTools.NUXEO_REPOSITORY_NAME); + DataSource nuxeoMgrDataSource = JDBCTools.getDataSource(JDBCTools.NUXEO_MANAGER_DATASOURCE_NAME); + DataSource nuxeoReaderDataSource = JDBCTools.getDataSource(JDBCTools.NUXEO_READER_DATASOURCE_NAME); + + // We need to fetch the user name and password from the nuxeoDataSource, to do grants below + org.apache.tomcat.dbcp.dbcp.BasicDataSource tomcatDataSource = + (org.apache.tomcat.dbcp.dbcp.BasicDataSource)nuxeoDataSource; + // Get the template URL value from the JNDI datasource and substitute the databaseName + String nuxeoUser = tomcatDataSource.getUsername(); + String nuxeoPW = tomcatDataSource.getPassword(); + // HACK - this should come from another DataSource + tomcatDataSource = + (org.apache.tomcat.dbcp.dbcp.BasicDataSource)nuxeoReaderDataSource; + // Get the template URL value from the JNDI datasource and substitute the databaseName + String readerUser = tomcatDataSource.getUsername(); + String readerPW = tomcatDataSource.getPassword(); + // // Set our AuthN's datasource to be the cspaceDataSource // AuthN.setDataSource(cspaceDataSource); + + // Get the NuxeoDS info and create the necessary databases. + // Consider the tenant bindings to find and get the data sources for each tenant. + // There may be only one, one per tenant, or something in between. + DatabaseProductType dbType = JDBCTools.getDatabaseProductType(); // only returns PG or MYSQL + String dbExistsQuery = (dbType==DatabaseProductType.POSTGRESQL)? + DB_EXISTS_QUERY_PSQL : DB_EXISTS_QUERY_MYSQL; + + Hashtable tenantBindings = + tenantBindingConfigReader.getTenantBindings(); + HashSet nuxeoDBsChecked = new HashSet(); + PreparedStatement pstmt = null; + Statement stmt = null; + Connection conn = null; + + try { + conn = nuxeoMgrDataSource.getConnection(); + // First check and create the roles as needed. (nuxeo and reader) + + + pstmt = conn.prepareStatement(dbExistsQuery); // create a statement + stmt = conn.createStatement(); + + for (TenantBindingType tenantBinding : tenantBindings.values()) { + String tId = tenantBinding.getId(); + String tName = tenantBinding.getName(); + List repoDomainList = tenantBinding.getRepositoryDomain(); + for (RepositoryDomainType repoDomain : repoDomainList) { + String repoName = repoDomain.getName(); + String dbName = /* repoDomain.getRepositoryName()?? */ "nuxeo"; + if(nuxeoDBsChecked.contains(dbName)) { + if (logger.isDebugEnabled()) { + logger.debug("Another user of db: "+dbName+": Repo: "+repoName+" and tenant: " + +tName+" (id:"+tId+")"); + } + } else { + if (logger.isDebugEnabled()) { + logger.debug("Need to prepare db: "+dbName+" for Repo: "+repoName+" and tenant: " + +tName+" (id:"+tId+")"); + } + + pstmt.setString(1, dbName); // set dbName param + ResultSet rs = pstmt.executeQuery(); + // extract data from the ResultSet + boolean dbExists = rs.next(); + rs.close(); + if(dbExists) { + if (logger.isDebugEnabled()) { + logger.debug("Database: "+dbName+" already exists."); + } + } else { + // Create the user as needed + createUserIfNotExists(conn, dbType, nuxeoUser, nuxeoPW); + createUserIfNotExists(conn, dbType, readerUser, readerPW); + // Create the database + createDatabaseWithRights(conn, dbType, dbName, nuxeoUser, nuxeoPW, readerUser, readerPW); + } + nuxeoDBsChecked.add(dbName); + } + } // Loop on repos for tenant + } // Loop on tenants + } catch(SQLException se) { + //Handle errors for JDBC + se.printStackTrace(); + } catch(Exception e) { + //Handle errors for Class.forName + e.printStackTrace(); + } finally { //close resources + try { + if(stmt!=null) { + stmt.close(); + } + } catch(SQLException se2) { + // nothing we can do + } + try{ + if(conn!=null) { + conn.close(); + } + }catch(SQLException se){ + se.printStackTrace(); + } + } + } + + private void createUserIfNotExists(Connection conn, DatabaseProductType dbType, + String username, String userPW) throws Exception { + PreparedStatement pstmt = null; + Statement stmt = null; + final String USER_EXISTS_QUERY_PSQL = + "SELECT 1 AS result FROM pg_roles WHERE rolname=?"; + String userExistsQuery; + if(dbType==DatabaseProductType.POSTGRESQL) { + userExistsQuery = USER_EXISTS_QUERY_PSQL; + } else { + throw new UnsupportedOperationException("CreateUserIfNotExists only supports PSQL - MySQL NYI!"); + } + try { + pstmt = conn.prepareStatement(userExistsQuery); // create a statement + pstmt.setString(1, username); // set dbName param + ResultSet rs = pstmt.executeQuery(); + // extract data from the ResultSet + boolean userExists = rs.next(); + rs.close(); + if(userExists) { + if (logger.isDebugEnabled()) { + logger.debug("User: "+username+" already exists."); + } + } else { + stmt = conn.createStatement(); + String sql = "CREATE ROLE "+username+" WITH PASSWORD '"+userPW+"' LOGIN"; + stmt.executeUpdate(sql); + // Really should do the grants as well. + if (logger.isDebugEnabled()) { + logger.debug("Created Users: '"+username+"' and 'reader'"); + } + } + } catch(Exception e) { + logger.error("createUserIfNotExists failed on exception: " + e.getLocalizedMessage()); + throw e; // propagate + } finally { //close resources + try { + if(pstmt!=null) { + pstmt.close(); + } + if(stmt!=null) { + stmt.close(); + } + } catch(SQLException se) { + // nothing we can do + } + } + } + + private void createDatabaseWithRights(Connection conn, DatabaseProductType dbType, String dbName, + String ownerName, String ownerPW, String readerName, String readerPW) throws Exception { + Statement stmt = null; + try { + stmt = conn.createStatement(); + if(dbType==DatabaseProductType.POSTGRESQL) { + // Postgres does not need passwords. + String sql = "CREATE DATABASE "+dbName+" ENCODING 'UTF8' OWNER "+ownerName; + stmt.executeUpdate(sql); + sql = "GRANT CONNECT ON DATABASE nuxeo TO "+readerName; + stmt.executeUpdate(sql); + if (logger.isDebugEnabled()) { + logger.debug("Created db: '"+dbName+"' with owner: '"+ownerName+"'"); + logger.debug(" Granted connect rights on: '"+dbName+"' to reader: '"+readerName+"'"); + } + // Note that select rights for reader must be granted after Nuxeo startup. + } else if(dbType==DatabaseProductType.MYSQL) { + String sql = "CREATE database "+dbName+" DEFAULT CHARACTER SET utf8"; + stmt.executeUpdate(sql); + sql = "GRANT ALL PRIVILEGES ON "+dbName+".* TO '"+ownerName+"'@'localhost' IDENTIFIED BY '" + +ownerPW+"' WITH GRANT OPTION"; + stmt.executeUpdate(sql); + sql = "GRANT SELECT ON "+dbName+".* TO '"+readerName+"'@'localhost' IDENTIFIED BY '" + +readerPW+"' WITH GRANT OPTION"; + stmt.executeUpdate(sql); + if (logger.isDebugEnabled()) { + logger.debug("Created db: '"+dbName+"' with owner: '"+ownerName+"'"); + logger.debug(" Granted SELECT rights on: '"+dbName+"' to reader: '"+readerName+"'"); + } + } else { + throw new UnsupportedOperationException("createDatabaseWithRights only supports PSQL - MySQL NYI!"); + } + } catch(Exception e) { + logger.error("createDatabaseWithRights failed on exception: " + e.getLocalizedMessage()); + throw e; // propagate + } finally { //close resources + try { + if(stmt!=null) { + stmt.close(); + } + } catch(SQLException se) { + // nothing we can do + } + } + } private void setServerRootDir() { diff --git a/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java b/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java index ab5909642..82ba8f66b 100644 --- a/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java +++ b/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java @@ -43,6 +43,9 @@ public class JDBCTools { public static HashMap cachedDataSources = new HashMap(); public static String CSPACE_REPOSITORY_NAME = "CspaceDS"; public static String NUXEO_REPOSITORY_NAME = "NuxeoDS"; + public static String NUXEO_MANAGER_DATASOURCE_NAME = "NuxeoMgrDS"; + public static String NUXEO_READER_DATASOURCE_NAME = "NuxeoReaderDS"; + public static String NUXEO_USER_NAME = "nuxeo"; // // Private constants // @@ -212,7 +215,9 @@ public class JDBCTools { if(DBProductName==null) { Connection conn = null; try { - conn = getConnection(getDefaultRepositoryName()); //FIXME: REM - getDefaultRepositoryName returns the Nuxeo repo name -we should be using the "cspace" repo name + // Nuxeo database may not yet exist, so use teh cspace db, which must exist. + //conn = getConnection(getDefaultRepositoryName()); //FIXME: REM - getDefaultRepositoryName returns the Nuxeo repo name -we should be using the "cspace" repo name + conn = getConnection(CSPACE_REPOSITORY_NAME); DBProductName = conn.getMetaData().getDatabaseProductName(); } catch (Exception e) { } finally { diff --git a/src/main/resources/db/mysql/init_nuxeo_db.sql b/src/main/resources/db/mysql/init_nuxeo_db.sql index d482a9bff..9e36793c1 100644 --- a/src/main/resources/db/mysql/init_nuxeo_db.sql +++ b/src/main/resources/db/mysql/init_nuxeo_db.sql @@ -2,23 +2,28 @@ -- recreate nuxeo database -- DROP database IF EXISTS nuxeo; -CREATE database nuxeo DEFAULT CHARACTER SET utf8; + +-- All the rest of what is commented out below is now handled at startup +-- by the services web-app + +-- CREATE database nuxeo DEFAULT CHARACTER SET utf8; -- -- grant privileges to users on nuxeo database -- -GRANT ALL PRIVILEGES ON nuxeo.* TO '@DB_NUXEO_USER@'@'localhost' IDENTIFIED BY '@DB_NUXEO_PASSWORD@' WITH GRANT OPTION; +-- GRANT ALL PRIVILEGES ON nuxeo.* TO '@DB_NUXEO_USER@'@'localhost' IDENTIFIED BY '@DB_NUXEO_PASSWORD@' WITH GRANT OPTION; -- -- Grant privileges to read-only user on Nuxeo, for reporting. -- -GRANT SELECT ON nuxeo.* TO 'reader'@'localhost' IDENTIFIED BY 'read'; +-- GRANT SELECT ON nuxeo.* TO 'reader'@'localhost' IDENTIFIED BY 'read'; -- -- Grant privileges to remote read-only users on Nuxeo, for reporting. -- These should be changed to reflect your domain. Avoid specifying -- 'reader'@'%' (while simple and flexible, this is a potential security hole). -- -GRANT SELECT ON nuxeo.* TO 'reader'@'%.berkeley.edu' IDENTIFIED BY 'read'; -GRANT SELECT ON nuxeo.* TO 'reader'@'%.movingimage.us' IDENTIFIED BY 'read'; +-- GRANT SELECT ON nuxeo.* TO 'reader'@'%.berkeley.edu' IDENTIFIED BY 'read'; +-- GRANT SELECT ON nuxeo.* TO 'reader'@'%.movingimage.us' IDENTIFIED BY 'read'; + FLUSH PRIVILEGES; diff --git a/src/main/resources/db/postgresql/init_nuxeo_db.sql b/src/main/resources/db/postgresql/init_nuxeo_db.sql index 92648440b..c0b999c17 100644 --- a/src/main/resources/db/postgresql/init_nuxeo_db.sql +++ b/src/main/resources/db/postgresql/init_nuxeo_db.sql @@ -4,18 +4,21 @@ DROP database IF EXISTS nuxeo; DROP USER IF EXISTS nuxeo; DROP USER IF EXISTS reader; -CREATE ROLE @DB_NUXEO_USER@ WITH PASSWORD '@DB_NUXEO_PASSWORD@' LOGIN; -CREATE ROLE reader WITH PASSWORD 'read' LOGIN; +-- All the rest of what is commented out below is now handled at startup +-- by the services web-app + +-- CREATE ROLE @DB_NUXEO_USER@ WITH PASSWORD '@DB_NUXEO_PASSWORD@' LOGIN; +-- CREATE ROLE reader WITH PASSWORD 'read' LOGIN; -- -- recreate nuxeo database -- -CREATE DATABASE nuxeo ENCODING 'UTF8' OWNER @DB_NUXEO_USER@; +-- CREATE DATABASE nuxeo ENCODING 'UTF8' OWNER @DB_NUXEO_USER@; -- -- Grant privileges to read-only user on Nuxeo, for reporting. -- -GRANT CONNECT ON DATABASE nuxeo TO reader; +-- GRANT CONNECT ON DATABASE nuxeo TO reader; -- GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader; -- This must be run by hand, after the system has already started up, -- 2.47.3