<commit_before>/*
 * Copyright 2015 University of Oxford
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package uk.ac.ox.it.ords.api.database.structure.resources;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.fail;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import javax.ws.rs.core.Response;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import uk.ac.ox.it.ords.api.database.structure.dto.ColumnRequest;
import uk.ac.ox.it.ords.api.database.structure.dto.DatabaseRequest;
import uk.ac.ox.it.ords.api.database.structure.model.OrdsPhysicalDatabase;
import uk.ac.ox.it.ords.api.database.structure.permissions.DatabaseStructurePermissionSets;
import uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.HibernateUtils;
import uk.ac.ox.it.ords.security.model.Permission;
import uk.ac.ox.it.ords.security.model.UserRole;

public class OdbcTest extends AbstractDatabaseTest {

	@Test
	public void createOdbcRole() throws Exception{
		
		//
		// First create a database
		//
		loginUsingSSO("<EMAIL>","<EMAIL>");
		DatabaseRequest dbr = this.buildDatabaseRequest(null, 99, "localhost");
		Response response = getClient().path("/0/MAIN").post(dbr);
		assertEquals(201, response.getStatus());
		OrdsPhysicalDatabase db = (OrdsPhysicalDatabase)response.readEntity(OrdsPhysicalDatabase.class);
		assertNotNull(db);
		
		//
		// Add a table
		//
		int dbID = db.getPhysicalDatabaseId();
		
		// Create a table
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/false").post(null);
		assertEquals(201, response.getStatus());
		
		// build a column
		ColumnRequest column1 = this.buildColumnRequest("testColumn", "varchar", null, true, false);
		// Create a column
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/column/testColumn/false").post(column1);
		assertEquals(201, response.getStatus());
		
		logout();
		
		//
		// Add Pinga as a user for this database
		//
		this.addViewer("<EMAIL>", dbID);
		
		//
		// Now lets add an ODBC role - this time Pinga is logging in and asking for access
		//
		loginUsingSSO("<EMAIL>","");

		Response odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/").post(null);
		assertEquals(200, odbcResponse.getStatus());
		String password = odbcResponse.readEntity(String.class);
		System.out.println(password);
		
		//
		// So, do we now have rights for pinga? Easiest way is to see if we can connect and run a query. It won't
		// return anything as its an empty table, but we'll get an Exception if the role isn't permitted.
		//
		CachedRowSet results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
		assertFalse(results.first());
		
		//
		// This should fail as pinga should only have viewer access
		//
		try {
			results = runSQLStatement("insert into \"testTable\" values ('testColumn' = 'banana')", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop pinga
		//
		logout();
		loginUsingSSO("<EMAIL>","<EMAIL>");
		odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/pingapenguinscom_ords").delete();
		assertEquals(200, odbcResponse.getStatus());
		
		//
		// This should now fail as pinga is no longer allowed access
		//
		try {
			results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop the DB
		//
		Response deleteResponse = getClient().path("/"+dbID+"/MAIN").delete();
		assertEquals(200, deleteResponse.getStatus());
	}
	
	protected CachedRowSet runSQLStatement(String statement, String server,
			String databaseName, String user, String password) throws Exception {
		Connection connection = null;
		Properties connectionProperties = new Properties();
		PreparedStatement preparedStatement = null;
		connectionProperties.put("user", user);
		connectionProperties.put("password", password);
		String connectionURL = "jdbc:postgresql://" + server + "/"
				+ databaseName;
		try {
			connection = DriverManager.getConnection(connectionURL,
					connectionProperties);
			
			preparedStatement = connection.prepareStatement(statement);

			if (statement.toLowerCase().startsWith("select")) {
				ResultSet result = preparedStatement.executeQuery();
				CachedRowSet rowSet = RowSetProvider.newFactory()
						.createCachedRowSet();
				rowSet.populate(result);
				return rowSet;
			} else {
				preparedStatement.execute();
				return null;
			}
		}
		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}
	
	private String calculateInstanceName ( OrdsPhysicalDatabase db, String instance ){
		return 	(instance + "_" + db.getPhysicalDatabaseId() + "_" + db.getLogicalDatabaseId()).toLowerCase();
	}
	
	private void addViewer(String principal, int databaseid){

		Session session = HibernateUtils.getSessionFactory().getCurrentSession();
		Transaction transaction = session.beginTransaction();

		for (String permission : DatabaseStructurePermissionSets.getPermissionsForViewer(99)){
			Permission permissionObject = new Permission();
			permissionObject.setRole("viewer_"+databaseid);
			permissionObject.setPermission(permission);
			session.save(permissionObject);
		}
		
		UserRole pinga = new UserRole();
		pinga.setPrincipalName(principal);
		pinga.setRole("viewer_"+databaseid);
		session.save(pinga);
		
		transaction.commit();
	}
}<commit_msg>Added test cases for re-requesting access (new passwords) and contributor-level access<commit_after>/*
 * Copyright 2015 University of Oxford
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package uk.ac.ox.it.ords.api.database.structure.resources;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import javax.ws.rs.core.Response;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import uk.ac.ox.it.ords.api.database.structure.dto.ColumnRequest;
import uk.ac.ox.it.ords.api.database.structure.dto.DatabaseRequest;
import uk.ac.ox.it.ords.api.database.structure.model.OrdsPhysicalDatabase;
import uk.ac.ox.it.ords.api.database.structure.permissions.DatabaseStructurePermissionSets;
import uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.HibernateUtils;
import uk.ac.ox.it.ords.security.model.Permission;
import uk.ac.ox.it.ords.security.model.UserRole;

/**
 * Tests for ODBC services
 * Make sure you've set up PostgreSQL to require passwords in pg_hba.conf, e.g.:
 * host    all             all             127.0.0.1/32            md5
 * @author scottbw
 *
 */
public class OdbcTest extends AbstractDatabaseTest {

	@Test
	public void createOdbcRole() throws Exception{
		
		//
		// First create a database
		//
		loginUsingSSO("<EMAIL>","<EMAIL>");
		DatabaseRequest dbr = this.buildDatabaseRequest(null, 99, "localhost");
		Response response = getClient().path("/0/MAIN").post(dbr);
		assertEquals(201, response.getStatus());
		OrdsPhysicalDatabase db = (OrdsPhysicalDatabase)response.readEntity(OrdsPhysicalDatabase.class);
		assertNotNull(db);
		
		//
		// Add a table
		//
		int dbID = db.getPhysicalDatabaseId();
		
		// Create a table
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/false").post(null);
		assertEquals(201, response.getStatus());
		
		// build a column
		ColumnRequest column1 = this.buildColumnRequest("testColumn", "varchar", null, true, false);
		// Create a column
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/column/testColumn/false").post(column1);
		assertEquals(201, response.getStatus());
		
		logout();
		
		//
		// Add Pinga as a user for this database
		//
		this.addViewer("<EMAIL>", dbID);
		
		//
		// Now lets add an ODBC role - this time Pinga is logging in and asking for access
		//
		loginUsingSSO("<EMAIL>","");

		Response odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/").post(null);
		assertEquals(200, odbcResponse.getStatus());
		String password = odbcResponse.readEntity(String.class);
		
		//
		// So, do we now have rights for pinga? Easiest way is to see if we can connect and run a query. It won't
		// return anything as its an empty table, but we'll get an Exception if the role isn't permitted.
		//
		CachedRowSet results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
		assertFalse(results.first());
		
		//
		// This should fail as pinga should only have viewer access
		//
		try {
			results = runSQLStatement("insert into \"testTable\" values ('testColumn' = 'banana')", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop pinga
		//
		logout();
		loginUsingSSO("<EMAIL>","<EMAIL>");
		odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/pingapenguinscom_ords").delete();
		assertEquals(200, odbcResponse.getStatus());
		
		//
		// This should now fail as pinga is no longer allowed access
		//
		try {
			results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop the DB
		//
		Response deleteResponse = getClient().path("/"+dbID+"/MAIN").delete();
		assertEquals(200, deleteResponse.getStatus());
	}
	
	@Test
	public void createOdbcContributorRole() throws Exception{
		
		//
		// First create a database
		//
		loginUsingSSO("<EMAIL>","<EMAIL>");
		DatabaseRequest dbr = this.buildDatabaseRequest(null, 98, "localhost");
		Response response = getClient().path("/0/MAIN").post(dbr);
		assertEquals(201, response.getStatus());
		OrdsPhysicalDatabase db = (OrdsPhysicalDatabase)response.readEntity(OrdsPhysicalDatabase.class);
		assertNotNull(db);
		
		//
		// Add a table
		//
		int dbID = db.getPhysicalDatabaseId();
		
		// Create a table
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/false").post(null);
		assertEquals(201, response.getStatus());
		
		// build a column
		ColumnRequest column1 = this.buildColumnRequest("testColumn", "varchar", null, true, false);
		// Create a column
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/column/testColumn/false").post(column1);
		assertEquals(201, response.getStatus());
		
		logout();
		
		//
		// Add Pinga as a user for this database
		//
		this.addContributor("<EMAIL>", dbID);
		
		//
		// Now lets add an ODBC role - this time Pinga is logging in and asking for access
		//
		loginUsingSSO("<EMAIL>","");

		Response odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/").post(null);
		assertEquals(200, odbcResponse.getStatus());
		String password = odbcResponse.readEntity(String.class);
		
		//
		// So, do we now have rights for pinga? Easiest way is to see if we can connect and run a query. It won't
		// return anything as its an empty table, but we'll get an Exception if the role isn't permitted.
		//
		CachedRowSet results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
		assertFalse(results.first());
		
		//
		// This should work as pinga has contributor access
		//
		runSQLStatement("insert into \"testTable\" values ('testColumn' = 'banana')", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
		results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
		assertTrue(results.first());
		
		//
		// Now drop pinga
		//
		logout();
		loginUsingSSO("<EMAIL>","<EMAIL>");
		odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/pingapenguinscom_ords").delete();
		assertEquals(200, odbcResponse.getStatus());
		
		//
		// This should now fail as pinga is no longer allowed access
		//
		try {
			results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop the DB
		//
		Response deleteResponse = getClient().path("/"+dbID+"/MAIN").delete();
		assertEquals(200, deleteResponse.getStatus());
	}
	/**
	 * Test that we can reset a user's ODBC password by POSTing
	 * @throws Exception
	 */
	@Test
	public void resetRole() throws Exception{
		
		//
		// First create a database
		//
		loginUsingSSO("<EMAIL>","<EMAIL>");
		DatabaseRequest dbr = this.buildDatabaseRequest(null, 97, "localhost");
		Response response = getClient().path("/0/MAIN").post(dbr);
		assertEquals(201, response.getStatus());
		OrdsPhysicalDatabase db = (OrdsPhysicalDatabase)response.readEntity(OrdsPhysicalDatabase.class);
		assertNotNull(db);
		
		//
		// Add a table
		//
		int dbID = db.getPhysicalDatabaseId();
		
		// Create a table
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/false").post(null);
		assertEquals(201, response.getStatus());
		
		// build a column
		ColumnRequest column1 = this.buildColumnRequest("testColumn", "varchar", null, true, false);
		// Create a column
		response = getClient().path("/"+dbID+"/MAIN/table/testTable/column/testColumn/false").post(column1);
		assertEquals(201, response.getStatus());
		
		logout();
		
		//
		// Add Pinga as a user for this database
		//
		this.addViewer("<EMAIL>", dbID);
		
		//
		// Now lets add an ODBC role - this time Pinga is logging in and asking for access
		//
		loginUsingSSO("<EMAIL>","");

		Response odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/").post(null);
		assertEquals(200, odbcResponse.getStatus());
		String password = odbcResponse.readEntity(String.class);
		
		odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/").post(null);
		assertEquals(200, odbcResponse.getStatus());
		String newPassword = odbcResponse.readEntity(String.class);
		assertFalse(password.equals(newPassword));
		
		//
		// This should now fail as we've reset the password
		//
		try {
			CachedRowSet results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", password);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("FATAL: password authentication failed for user \"pingapenguinscom_ords\"", e.getMessage());
		}
		
		//
		// So, do we now have rights for pinga? Easiest way is to see if we can connect and run a query. It won't
		// return anything as its an empty table, but we'll get an Exception if the role isn't permitted.
		//
		CachedRowSet results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", newPassword);
		assertFalse(results.first());
		
		//
		// This should fail as pinga should only have viewer access
		//
		try {
			results = runSQLStatement("insert into \"testTable\" values ('testColumn' = 'banana')", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", newPassword);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop pinga
		//
		logout();
		loginUsingSSO("<EMAIL>","<EMAIL>");
		odbcResponse = getClient().path("/"+dbID+"/MAIN/odbc/pingapenguinscom_ords").delete();
		assertEquals(200, odbcResponse.getStatus());
		
		//
		// This should now fail as pinga is no longer allowed access
		//
		try {
			results = runSQLStatement("SELECT * FROM \"testTable\"", "localhost", calculateInstanceName(db, "MAIN"), "pingapenguinscom_ords", newPassword);
			assertFalse(results.first());
			fail();
		} catch (Exception e) {
			assertEquals("ERROR: permission denied for relation testTable", e.getMessage());
		}
		
		//
		// Now drop the DB
		//
		Response deleteResponse = getClient().path("/"+dbID+"/MAIN").delete();
		assertEquals(200, deleteResponse.getStatus());
	}
	
	protected CachedRowSet runSQLStatement(String statement, String server,
			String databaseName, String user, String password) throws Exception {
		Connection connection = null;
		Properties connectionProperties = new Properties();
		PreparedStatement preparedStatement = null;
		connectionProperties.put("user", user);
		connectionProperties.put("password", password);
		String connectionURL = "jdbc:postgresql://" + server + "/"
				+ databaseName;
		try {
			connection = DriverManager.getConnection(connectionURL,
					connectionProperties);
			
			preparedStatement = connection.prepareStatement(statement);

			if (statement.toLowerCase().startsWith("select")) {
				ResultSet result = preparedStatement.executeQuery();
				CachedRowSet rowSet = RowSetProvider.newFactory()
						.createCachedRowSet();
				rowSet.populate(result);
				return rowSet;
			} else {
				preparedStatement.execute();
				return null;
			}
		}
		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}
	
	private String calculateInstanceName ( OrdsPhysicalDatabase db, String instance ){
		return 	(instance + "_" + db.getPhysicalDatabaseId() + "_" + db.getLogicalDatabaseId()).toLowerCase();
	}
	
	private void addViewer(String principal, int databaseid){

		Session session = HibernateUtils.getSessionFactory().getCurrentSession();
		Transaction transaction = session.beginTransaction();

		for (String permission : DatabaseStructurePermissionSets.getPermissionsForViewer(databaseid)){
			Permission permissionObject = new Permission();
			permissionObject.setRole("viewer_"+databaseid);
			permissionObject.setPermission(permission);
			session.save(permissionObject);
		}
		
		UserRole pinga = new UserRole();
		pinga.setPrincipalName(principal);
		pinga.setRole("viewer_"+databaseid);
		session.save(pinga);
		
		transaction.commit();
	}
	
	private void addContributor(String principal, int databaseid){

		Session session = HibernateUtils.getSessionFactory().getCurrentSession();
		Transaction transaction = session.beginTransaction();

		for (String permission : DatabaseStructurePermissionSets.getPermissionsForContributor(databaseid)){
			Permission permissionObject = new Permission();
			permissionObject.setRole("contributor_"+databaseid);
			permissionObject.setPermission(permission);
			session.save(permissionObject);
		}
		
		UserRole pinga = new UserRole();
		pinga.setPrincipalName(principal);
		pinga.setRole("contributor_"+databaseid);
		session.save(pinga);
		
		transaction.commit();
	}
}
{'fraction_non_alphanumeric': 0.13520749665327977, 'fraction_numerical': 0.005832855230445592, 'mean_word_length': 4.22663668165917, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3439360', 'n_tokens_mistral': 6883, 'n_tokens_neox': 6031, 'n_words': 1518}