BigQuery datasets are not reported in the daily audit report

[ameukam]

I noticed some BQ datasets of some projects are not reported in the daily audit. Like k8s-artificats-prod:

 gcloud alpha bq datasets list --project k8s-artifacts-prod
ID                               LOCATION
k8s-artifacts-prod:gcs_logs      US
k8s-artifacts-prod:http_lb_logs  US

gcloud alpha bq datasets describe http_lb_logs --project k8s-artifacts-prod
access:
- role: WRITER
  userByEmail: [email protected]
- role: OWNER
  specialGroup: projectWriters
- role: OWNER
  userByEmail: [email protected]
creationTime: '1595435572438'
datasetReference:
  datasetId: http_lb_logs
  projectId: k8s-artifacts-prod
etag: lJiP3Wus3wsAFvTNYpvLaw==
id: k8s-artifacts-prod:http_lb_logs
kind: bigquery#dataset
lastModifiedTime: '1595435572438'
location: US
selfLink: https://bigquery.googleapis.com/bigquery/v2/projects/k8s-artifacts-prod/datasets/http_lb_logs
type: DEFAULT

The current report is empty: https://github.com/kubernetes/k8s.io/blob/main/audit/projects/k8s-artifacts-prod/services/bigquery/bigquery.datasets.json

It would great to have them part of the daily report.

/assign @spiffxp @thockin
cc @hh

[spiffxp]

/reopen
Still not being reported per the latest audit PR, ref: #2094 (review)

[k8s-ci-robot]

@spiffxp: Reopened this issue.

In response to this:

/reopen
Still not being reported per the latest audit PR

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[spiffxp]

Looking at the audit PR more closely, the Kubernetes-public resources weren't deleted.

I'm guessing this means roles/viewer can see them:

k8s.io/audit/projects/kubernetes-public/services/bigquery/bigquery.datasets.kubernetes_public_billing.access.json

Lines 22 to 25 in 3365798

	{
	"role": "READER",
	"specialGroup": "projectReaders"
	},

And the auditor service account has that role for kubernetes-public:

k8s.io/audit/projects/kubernetes-public/iam.json

Lines 149 to 156 in 3365798

	"members": [
	"group:[email protected]",
	"group:[email protected]",
	"group:[email protected]",
	"group:[email protected]",
	"serviceAccount:[email protected]"
	],
	"role": "roles/viewer"

But not for the organization

Which makes me wonder if, just like org admins get roles/owner + a custom role for supplementary stuff, we should do the same for auditors.

Or, see if there's a way to give audit.viewer read access to everything it needs to export metadata about big query datasets (as there is with GCS)

[spiffxp]

Using #2100 to test

First run, I get an error

 ./audit/audit-gcp.sh k8s-infra-ii-sandbox
Exporting GCP project: k8s-infra-ii-sandbox
  Removing existing audit files for project: k8s-infra-ii-sandbox
  Exporting project description for project: k8s-infra-ii-sandbox
  Exporting IAM policy for project: k8s-infra-ii-sandbox
  Exporting IAM serviceaccounts for project: k8s-infra-ii-sandbox
  Exporting IAM roles for project: k8s-infra-ii-sandbox
  Exporting enabled services for project: k8s-infra-ii-sandbox
  Exporting resources for all enabled services for project: k8s-infra-ii-sandbox
    Exporting resources for service: bigquery, project: k8s-infra-ii-sandbox
      parse error: Invalid numeric literal at line 2, column 8
      Exception ignored in: <_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>
      BrokenPipeError: [Errno 32] Broken pipe

Next run works though

$ ./audit/audit-gcp.sh k8s-infra-ii-sandbox
Exporting GCP project: k8s-infra-ii-sandbox
  Removing existing audit files for project: k8s-infra-ii-sandbox
  Exporting project description for project: k8s-infra-ii-sandbox
  Exporting IAM policy for project: k8s-infra-ii-sandbox
  Exporting IAM serviceaccounts for project: k8s-infra-ii-sandbox
  Exporting IAM roles for project: k8s-infra-ii-sandbox
  Exporting enabled services for project: k8s-infra-ii-sandbox
  Exporting resources for all enabled services for project: k8s-infra-ii-sandbox
    Exporting resources for service: bigquery, project: k8s-infra-ii-sandbox
    Exporting resources for service: bigqueryconnection, project: k8s-infra-ii-sandbox
      WARN: Unaudited service bigqueryconnection enabled in project: k8s-infra-ii-sandbox
    Exporting resources for service: bigquerydatatransfer, project: k8s-infra-ii-sandbox
      WARN: Unaudited service bigquerydatatransfer enabled in project: k8s-infra-ii-sandbox
# ...
$ git diff
$

That's kicking loose a comment about bq I've seen somewhere in test-infra's codebase...

[spiffxp]

Found it: https://github.com/kubernetes/test-infra/blob/4d5c4c788bcee23a9323ca6f10563a08afb822f6/metrics/bigquery.py#L154-L157

    # the 'bq show' command is called as a hack to dodge the config prompts that bq presents    
    # the first time it is run. A newline is passed to stdin to skip the prompt for default project    
    # when the service account in use has access to multiple projects.    
    check(['bq', 'show'], stdin='\n')

[spiffxp]

#2100 added a bq show to try and work around this

Unfortunately it broke the ci job. #2104 will fix

[spiffxp]

/close
They're now reported, e.g. https://github.com/kubernetes/k8s.io/blob/main/audit/projects/kubernetes-public/services/bigquery/bigquery.datasets.kubernetes_public_billing.access.json

[k8s-ci-robot]

@spiffxp: Closing this issue.

In response to this:

/close
They're now reported, e.g. https://github.com/kubernetes/k8s.io/blob/main/audit/projects/kubernetes-public/services/bigquery/bigquery.datasets.kubernetes_public_billing.access.json

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[spiffxp]

/milestone v1.22