title: BigQuery Node.js API Permission Bug
body: <p>I am building a Node.js server to run queries against BigQuery. For security reasons, I want this server to be read only. For example, if I write a query with <code>DROP</code>, <code>INSERT</code>, <code>ALTER</code>, etc. statement my query should get rejected. However, something like <code>SELECT * FROM DATASET.TABLE LIMIT 10</code> should be allowed. </p>
  
  <p>To solve this problem, I decided to use a <a href="https://cloud.google.com/bigquery/docs/access-control#predefined_roles_comparison_matrix" rel="nofollow noreferrer">service account with "jobUser" level access</a>. According to BQ documentation, that should allow me to run queries, but I shouldn't be able to "modify/delete tables". </p>
  
  <p>So I <a href="https://i.stack.imgur.com/rF9Xo.png" rel="nofollow noreferrer">created such a service account</a> using the Google Cloud Console UI and I pass that file to the BigQuery Client Library (for Node.js) as the <code>keyFilename</code> parameter in the code below.</p>
  
  <pre class="lang-js prettyprint-override"><code>// Get service account key for .env file
  require( 'dotenv' ).config()
  const BigQuery = require( '@google-cloud/bigquery' );
  
  // Query goes here
  const query = `
    SELECT * 
    FROM \`dataset.table0\` 
    LIMIT 10
  `
  
  // Creates a client
  const bigquery = new BigQuery({
    projectId: process.env.BQ_PROJECT,
    keyFilename: process.env.BQ_SERVICE_ACCOUNT
  });
  
  // Use standard sql
  const query_options = {
    query : query,
    useLegacySql : false,
    useQueryCache : false
  }
  
  // Run query and log results
  bigquery
      .query( query_options )
      .then( console.log )
      .catch( console.log )
  </code></pre>
  
  <p>I then ran the above code against <a href="https://i.stack.imgur.com/8zWhs.png" rel="nofollow noreferrer">my test dataset/table in BigQuery</a>. However, running this code results in the following error message (fyi: <code>exemplary-city-194015</code> is my projectID for my test account)</p>
  
  <pre><code>{ ApiError: Access Denied: Project exemplary-city-194015: The user test-bq-jobuser@exemplary-city-194015.iam.gserviceaccount.com does not have bigquery.jobs.create permission in project exemplary-city-194015.
  </code></pre>
  
  <p>What is strange is that my service account (<code>test-bq-jobuser@exemplary-city-194015.iam.gserviceaccount.com</code>) <a href="https://imgur.com/a/iAxEs" rel="nofollow noreferrer">has the 'Job User' role</a> and the Job User role <a href="https://imgur.com/a/RYraK" rel="nofollow noreferrer">does contain the <code>bigquery.jobs.create permission</code></a>. So that error message doesn't make sense.</p>
  
  <p>In fact, I tested out all possible access control levels (dataViewer, dataEditor, ... , admin) and I get error messages for every role except the "admin" role. So either my service account isn't correctly configured or @google-cloud/bigquery has some bug. I don't want to use a service account with 'admin' level access because that allows me to run <code>DROP TABLE</code>-esque queries.</p>
  
  <p><strong>Solution:</strong></p>
  
  <p>I created a service account and assigned it <a href="https://imgur.com/a/2lc59" rel="nofollow noreferrer">a custom role</a> with <code>bigquery.jobs.create</code> <strong>and</strong> <code>bigquery.tables.getData</code> permissions. And that seemed to work. I can run basic <code>SELECT</code> queries but <code>DROP TABLE</code> and other write operations fail, which is what I want.</p>