Unable to retrieve BQ table metadata in GCP

[Suguresh_lloyds]

Hi There,

I'm trying to pull BigQuery table metadata from GCP into Collibra DGC. With the below configuration I'm able see all tables not its column metadata. Could you pls suggest me right VPCSC BQ Method/permission in GCP to resolve this issue ASAP.

If I use "bigquery.tables.getData" permission at both end (vpcsc ingress & egress) i'm able to see column metadata, But I can't use "bigquery.tables.getData" permission due to Organization security concern, because it also pulls actual data. 

As per the google IAM document, I started using "bigquery.tables.get" permission to get table metadata but its fetches only tables not its column metadata. .

Below is my Ingress and Egress VPCSC configuration FYR:

Egress_vpcsc.yaml (Project-1)

  - egress_from:

    - identities:

      - serviceAccount:svc-xyz@project-id.iam.gserviceaccount.com

    egress_to:

    - operations:

      - method_selectors:

        - method: BigQueryStorage.CreateReadSession

        - method: BigQueryRead.CreateReadSession

        - method: BigQueryStorage.ReadRows

        service_name: bigquery.googleapis.com

      resources:

      - projects/10xxxxxxxx8

      - projects/53xxxxxxx3

      - projects/44xxxxxxx6

  - egress_from:

    - identities:

      - serviceAccount:svc-xyz@project-id.iam.gserviceaccount.com

    egress_to:

    - operations:

      - method_selectors:

        - permission: bigquery.datasets.get

        - permission: bigquery.tables.get

        - permission: bigquery.tables.list

        - permission: bigquery.jobs.create

        service_name: bigquery.googleapis.com

      resources:

      - projects/71xxxxxxx3

Ingress_vpcsc.yaml (Project-2)

    ingress_to:

    - operations:

      - service_name: bigquery.googleapis.com

        method_selectors:

        - method: BigQueryStorage.CreateReadSession

        - method: BigQueryRead.CreateReadSession

        - method: BigQueryStorage.ReadRows

      resources:

      - projects/53xxxxxx3

  - ingress_from:

    - sources:

      - resource: projects/98xxxxxxx9

      identities:

      - serviceAccount:svc-xyz@project-id.iam.gserviceaccount.com

    ingress_to:

    - operations:

      - service_name: bigquery.googleapis.com

        method_selectors:

        - permission: bigquery.datasets.get

        - permission: bigquery.tables.get

        - permission: bigquery.tables.list

        - permission: bigquery.jobs.create

      resources:

      - projects/53xxxxxx3

[Willbin]

Hello @Suguresh_lloyds,

Welcome to Google Cloud Community!

You may try adding the BigQuery Metadata Viewer role (roles/bigquery.metadataViewer) 

---

When applied to a table or view, this role provides permissions to:

• Read metadata from the table or view.

This role cannot be applied to individual models or routines.

When applied to a dataset, this role provides permissions to:

• List tables and views in the dataset.
• Read metadata from the dataset's tables and views.

When applied at the project or organization level, this role provides permissions to:

• List all datasets and read metadata for all datasets in the project.
• List all tables and views and read metadata for all tables and views in the project.

Additional roles are necessary to allow the running of jobs.

Lowest-level resources where you can grant this role:

• Table
• View

---

Permissions for this role include:

bigquery.datasets.get
bigquery.datasets.getIamPolicy
bigquery.models.getMetadata
bigquery.models.list
bigquery.routines.get
bigquery.routines.list
bigquery.tables.get
bigquery.tables.getIamPolicy
bigquery.tables.list
resourcemanager.projects.get
resourcemanager.projects.list

For table view, see this document.

Thanks!