How to use Service Account to authenticate with Google Cloud SQL Java

Question

I am aware that there is Cloud SQL JDBC Socket Factory. However, that requires Application Default Credentials. What I am trying to do is to use a service Account(Which is the JSON secrets file) to authenticate with the Cloud SQL. Can someone please point me in the right direction? Thanks!

Codes from GettingStarted(https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory):

public class GettingStarted2{
    private static final ImmutableSet<String> SYSTEM_DATABASES =
            ImmutableSet.of(
                    // MySQL.
                    "mysql", "information_schema", "performance_schema",
                    // Postgres.
                    "cloudsqladmin", "postgres");

    @Parameter(names = "-v", description = "Verbose logging.")
    private boolean verbose = false;

    private void run() throws IOException, SQLException {
        System.out.println("Checking API credentials.");
        GoogleCredential apiCredentials;
        try {
//            apiCredentials = GoogleCredential.getApplicationDefault();
            apiCredentials = GoogleCredential.fromStream(new FileInputStream("<keypath.json>"))
                    .createScoped(Lists.newArrayList("https://www.googleapis.com/auth/cloud-platform"));
        } catch (IOException e) {
            System.err.println(
                    "Unable to find API credentials. \nPlease run "
                            + "'gcloud auth application-default login' to make credentials available to "
                            + "this application.");
            if (verbose) {
                e.printStackTrace();
            }
            System.exit(-1);
            return;
        }

        SQLAdmin adminApiClient = createAdminApiClient(apiCredentials);
        Optional<List<DatabaseInstance>> instances = askForProject(adminApiClient);
        if (!instances.isPresent()) {
            return;
        }

        if (instances.get().isEmpty()) {
            System.out.println(
                    "This project does not contain any Cloud SQL instances. "
                            + "Please create one using the Cloud Console.");
            return;
        }

        Optional<DatabaseInstance> optionalInstance = askForInstance(instances.get());
        if (!optionalInstance.isPresent()) {
            return;
        }

        String instanceConnectionName = optionalInstance.get().getConnectionName();
        Optional<DatabaseCredentials> optionalDatabaseCredentials =
                askForDatabaseCredentials(optionalInstance.get());
        if (!optionalDatabaseCredentials.isPresent()) {
            return;
        }

        Connection connection = optionalDatabaseCredentials.get().getConnection();
        List<String> databases = listDatabases(optionalInstance.get(), connection);
        connection.close();
        if (databases.isEmpty()) {
            printConnectionDetails(
                    optionalInstance.get(), Optional.empty(), optionalDatabaseCredentials.get());
            return;
        }

        Optional<String> database = askForDatabase(databases);
        if (!database.isPresent()) {
            return;
        }

        printConnectionDetails(optionalInstance.get(), database, optionalDatabaseCredentials.get());
    }

    private List<String> listDatabases(
            DatabaseInstance databaseInstance, Connection connection) throws SQLException {
        String listDatabasesQuery;
        switch (getDatabaseType(databaseInstance)) {
            case MYSQL:
                listDatabasesQuery = "SHOW DATABASES";
                break;
            case POSTGRES:
                listDatabasesQuery =
                        "SELECT datname AS database FROM pg_database WHERE datistemplate = false";
                break;
            default:
                throw new IllegalStateException();
        }

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(listDatabasesQuery);
        List<String> databases = new ArrayList<>();
        while (resultSet.next()) {
            String database = resultSet.getString("database");
            if (SYSTEM_DATABASES.contains(database)) {
                continue;
            }
            databases.add(database);
        }
        statement.close();
        databases.sort(String::compareTo);
        return databases;
    }

    private Optional<DatabaseCredentials> askForDatabaseCredentials(DatabaseInstance databaseInstance)
            throws SQLException {

        String defaultUser;
        String displayDatabaseType;
        String defaultDatabase;
        switch (getDatabaseType(databaseInstance)) {
            case MYSQL:
                defaultUser = "root";
                displayDatabaseType = "MySQL";
                defaultDatabase = "mysql";
                break;
            case POSTGRES:
                defaultUser = "postgres";
                displayDatabaseType = "Postgres";
                defaultDatabase = "postgres";
                break;
            default:
                return Optional.empty();
        }

        Console console = System.console();
        String user;
        String lastUser = defaultUser;
        for (; ; ) {
            char[] password={'r','o','o','t'};
            System.out.printf("Please enter %s username [%s]: ", displayDatabaseType, lastUser);
//            user = console.readLine();
            user="root";
            if (user == null) {
                return Optional.empty();
            }

            if (user.trim().isEmpty()) {
                user = lastUser;
            } else {
                lastUser = user;
            }

            System.out.printf("Please enter %s password: ", displayDatabaseType);
            //password = console.readPassword();
            if (password == null) {
                return Optional.empty();
            }

            try {
                return Optional.of(
                        new DatabaseCredentials(
                                user,
                                password,
                                DriverManager.getConnection(
                                        constructJdbcUrl(databaseInstance, defaultDatabase),
                                        user,
                                        new String(password))));
            } catch (SQLException e) {
                if (e.getErrorCode() == 1045) {
                    System.out.println("Invalid username/password. Please try again.");
                    continue;
                }
                // Too bad Postgres doesn't set the error code...
                if (e instanceof PSQLException
                        && e.getMessage().contains("password authentication failed")) {
                    System.out.println("Invalid username/password. Please try again.");
                    continue;
                }
                throw e;
            }
        }
    }

    private static String constructJdbcUrl(DatabaseInstance databaseInstance, String database) {
        switch (getDatabaseType(databaseInstance)) {
            case MYSQL:
                return String.format(
                        "jdbc:mysql://google/%s?socketFactory=com.google.cloud.sql.mysql.SocketFactory" +
                                "&cloudSqlInstance=%s",
                        database,
                        databaseInstance.getConnectionName());
            case POSTGRES:
                return String.format(
                        "jdbc:postgresql://google/%s?socketFactory=com.google.cloud.sql.postgres.SocketFactory" +
                                "&socketFactoryArg=%s",
                        database,
                        databaseInstance.getConnectionName());
            default:
                throw new IllegalStateException();
        }
    }

    private Optional<DatabaseInstance> askForInstance(List<DatabaseInstance> instances) {
        Optional<Integer> instanceChoice =
                chooseFromList(
                        "Please enter the number of the instance you want to use [1]: ",
                        instances.stream()
                                .map(
                                        inst ->
                                                String.format(
                                                        "%s [%s] (%s)",
                                                        inst.getName(),
                                                        inst.getDatabaseVersion(),
                                                        inst.getConnectionName()))
                                .collect(Collectors.toList()));
        if (!instanceChoice.isPresent()) {
            return Optional.empty();
        }

        return Optional.of(instances.get(instanceChoice.get()));
    }

    private Optional<String> askForDatabase(List<String> databases) {
        Optional<Integer> databaseIndex =
                chooseFromList("Please enter the number of the database you want to use [1]: ", databases);
        if (!databaseIndex.isPresent()) {
            return Optional.empty();
        }

        return Optional.of(databases.get(databaseIndex.get()));
    }

    private Optional<Integer> chooseFromList(String prompt, List<String> options) {
        Console console = System.console();

        for (int i = 0; i < options.size(); i++) {
            System.out.println(String.format("%d: %s", i + 1, options.get(i)));
        }

        int choice;
        for (;;) {
            System.out.print(prompt);
//            String line = console.readLine();
            String line="1";
            if (line == null) {
                return Optional.empty();
            }

            if (line.trim().isEmpty()) {
                return Optional.of(0);
            } else {
                try {
                    choice = Integer.parseInt(line);
                } catch (NumberFormatException e) {
                    System.out.println("Invalid choice.");
                    continue;
                }

                if (choice < 1 || choice > options.size()) {
                    System.out.println("Invalid choice.");
                    continue;
                }

                return Optional.of(choice - 1);
            }
        }
    }

    private Optional<List<DatabaseInstance>> askForProject(SQLAdmin adminApiClient)
            throws IOException {
        Console console = System.console();

        InstancesListResponse instancesListResponse = null;
        while (instancesListResponse == null) {
            String project = "";
            while (project.isEmpty()) {
                System.out.print("Enter the name of your Cloud project: ");
                //project = console.readLine();
                project="<projectname>";
                if (project == null) {
                    return Optional.empty();
                }
                project = project.trim();
            }

            System.out.println("Listing Cloud SQL instances.");

            try {
                instancesListResponse = adminApiClient.instances().list(project).execute();
            } catch (GoogleJsonResponseException e) {
                if (e.getStatusCode() >= 400 && e.getStatusCode() < 500) {
                    System.out.println("That doesn't appear to be a valid project, try again.");
                    continue;
                }
                throw e;
            }
        }

        ArrayList<DatabaseInstance> instances = new ArrayList<>(instancesListResponse.getItems());
        instances.sort(Comparator.comparing(DatabaseInstance::getName));
        return Optional.of(instances);
    }

    private void printConnectionDetails(
            DatabaseInstance databaseInstance,
            Optional<String> database,
            DatabaseCredentials databaseCredentials) {
        String databaseName = database.orElse("<database_name>");

        System.out.println("\n\n");
        System.out.printf(
                "Use the following JDBC URL%s:\n\n    %s\n",
                !database.isPresent() ? " after creating a database" : "",
                constructJdbcUrl(databaseInstance, databaseName));
        System.out.println();
        System.out.println("    Username: " + databaseCredentials.getUsername());
        System.out.println(
                "    Password: " + (databaseCredentials.getPassword().length > 0 ? "<yes>" : "<empty>"));
        System.out.println("\n\n");
    }

    private static SQLAdmin createAdminApiClient(Credential credential) {
        HttpTransport httpTransport;
        try {
            httpTransport = GoogleNetHttpTransport.newTrustedTransport();
        } catch (GeneralSecurityException | IOException e) {
            throw new RuntimeException("Unable to initialize HTTP transport", e);
        }

        return new SQLAdmin.Builder(httpTransport, JacksonFactory.getDefaultInstance(), credential)
                .setApplicationName("Cloud SQL Example")
                .build();
    }

    private static final class DatabaseCredentials {
        private final String username;
        private final char[] password;
        private final Connection connection;

        public DatabaseCredentials(String username, char[] password, Connection connection) {
            this.username = username;
            this.password = password;
            this.connection = connection;
        }

        public String getUsername() {
            return username;
        }

        public char[] getPassword() {
            return password;
        }

        public Connection getConnection() {
            return connection;
        }
    }

    private static DatabaseType getDatabaseType(DatabaseInstance databaseInstance) {
        if (databaseInstance.getDatabaseVersion().startsWith("MYSQL_")) {
            return DatabaseType.MYSQL;
        } else if (databaseInstance.getDatabaseVersion().startsWith("POSTGRES_")) {
            return DatabaseType.POSTGRES;
        } else {
            System.err.println("Unsupported database type: " + databaseInstance.getDatabaseVersion());
            System.exit(-1);
            return null;
        }
    }

    private enum DatabaseType {
        MYSQL,
        POSTGRES
    }

    public static void main(String[] args) throws IOException, SQLException {
        GettingStarted2 gettingStarted2 = new GettingStarted2();
        new JCommander(gettingStarted2, args);
        gettingStarted2.run();
    }
}

StackTrace: https://pastebin.com/PMJsAFaK 2nd StackTrace(Using codes from Github):https://pastebin.com/aH5vkLYF

Answer 1

Update(Answer from Google Engineer): After doing more research on whether it is possible to authenticate using a JSON file instead, here is what I found. They have stated that it currently does not allow External GoogleCredentials authentication.

Currently the way to load ServiceAccount would be this: The factory uses the ApplicationDefaultCredentials to authenticate as you mentioned. You can set these to a service account (it's the first example on that page).

$ gcloud iam service-accounts create my-account
$ gcloud iam service-accounts keys create key.json --iam-account=my-account@my-project.iam.gserviceaccount.com
$ export GOOGLE_APPLICATION_CREDENTIALS=key.json
$ ./my_application.sh

Current open issue(They may add authentication using GoogleCredentials in the future): https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/issues/66

Answer 2

You can manually provide the credentials when authenticating.

If you follow the examples for the JDBC Socket Factory, in particular this example, you can see that almost everything is set up in the run(). Bearing in mind my first link, you would have to change it to something like this:

GoogleCredential apiCredentials;
    try {
        apiCredentials = GoogleCredential.fromStream(new FileInputStream("yourkey.json"))
    .createScoped(Lists.newArrayList("https://www.googleapis.com/auth/cloud-platform"));

If you follow the example I have linked, don't forget to add the import java.io.FileInputStream and com.google.common.collect.Lists.