Authenticate as an end user a Spring Boot app for access to the BigQuery API

This guide explains how web server applications written with Spring Boot can use Google OAuth 2.0 endpoints to implement OAuth 2.0 authorization to access Google APIs and use Spring authentication for access to the BigQuery API.

This is to ensure the app accesses only BigQuery tables and table rows that are available to the end user.

Configure APIs & Services

First, start to by configuring the OAuth consent screen. Follow those steps: https://developers.google.com/workspace/guides/configure-oauth-consent

Click on Add or Remove scopes and select bigquery.readonly in order to be able to read data on behalf of the authenticated user.

Once it is done, go to Credentials and click on CREATE CREDENTIALS, choose OAuth Client ID in the dropdown list

Application type: Web application (we are configuring it for a Spring Boot app)

In Authorized redirect URIs field, set http://localhost:8080/login/oauth2/code/google. login/oauth2/code/google is the default redirect URI of Google. Add the production URL when needed. Click on Save. A popup will show you client id and secret. Store them your are going to need them to configure your Spring Boot app.

At this point, if you try to edit the OAuth consent screen to add or remove roles, you might see a message stating

This scope is unavailable because this project has non-https URLs in OAuth clients

To circumvent this issue, remove all non-https URLs configured in your OAuth clients, edit the consent screen and add back your non-https URLs.

Configure Spring Boot app

You need to add this dependency to your project

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-oauth2-client</artifactId>
</dependency>

Create your security config

@Configuration
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
         .anyRequest().authenticated()
         .and()
         .oauth2Login();
    }
}

In src/main/resources, create or edit application.properties file and add

spring.security.oauth2.client.registration.google.client-id=your-client-id
spring.security.oauth2.client.registration.google.client-secret=your-client-secret
spring.security.oauth2.client.registration.google.scope=openid,https://www.googleapis.com/auth/bigquery

Do not forget to add BigQuery API scope to the config by using its key

https://www.googleapis.com/auth/bigquery

If you don’t, you will have this issue later on when trying to fetch the list of the tables.

com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
GET https://www.googleapis.com/bigquery/v2/projects/********/datasets/********/tables?prettyPrint=false
{
  "code" : 403,
  "details" : [ {
    "@type" : "type.googleapis.com/google.rpc.ErrorInfo",
    "reason" : "ACCESS_TOKEN_SCOPE_INSUFFICIENT"
  } ],
  "errors" : [ {
    "domain" : "global",
    "message" : "Insufficient Permission",
    "reason" : "insufficientPermissions"
  } ],
  "message" : "Request had insufficient authentication scopes.",
  "status" : "PERMISSION_DENIED"
}

List of admissible keys are available here https://developers.google.com/identity/protocols/oauth2/scopes

At this point, if you try to access to any URL in your web browser, you will be redirected to the Google Authentication form:

Next, you will be shown the consent screen

Use BigQuery Java client

Now let’s try it out to fetch the list of the tables the current authenticated user is allowed to see. Start by adding the java lib

<dependency>
   <groupId>com.google.cloud</groupId>
   <artifactId>google-cloud-bigquery</artifactId>
</dependency>

Let’s see how to use it in a controller. Here’s the starting point

@RestController
class HelloWorldController {

  @Autowired
  OAuth2AuthorizedClientRepository authorizedClientRepository;

  @Autowired
  Environment environment;

  @GetMapping("/")
  public String hello(Principal principal) {
    System.out.println("Authenticated user: " + principal);
    return "hello";
  }
}

Inject Principal object to your endpoint, we will use it to extract what we need.

To fetch the list of the tables from BigQuery, the code is simple

Credentials credentials = ???
String projectId = "your-google-project-id";
String datasetName = "your-bigquery-dataset-name";
BigQuery bigquery = BigQueryOptions.newBuilder()
        .setCredentials(credentials)
        .setProjectId(projectId)
        .build()
        .getService();

Set<String> tableNames = new HashSet<>();
bigquery.listTables(datasetName)
        .getValues().iterator().forEachRemaining(t -> tableNames.add(t.getTableId().getTable()));
System.out.println("List of tables: " + tableNames);

The only thing that is missing is the Google credentials to be able to execute an authenticated request to BigQuery. To do so, we can use the information stored in Principal object to convert it to com.google.auth.oauth2.Credentials type.

That’s it!

Full code