CODEWITHSUNDEEP
google-bigquerynotificationsgoogle-cloud-pubsub
Santanu Ghosh
Santanu Ghosh

Reputation: 153

BigQuery Schedule Pub/Sub Notification Error

I am trying to schedule a query from BQ console with pub/sub notification.

The query is below.

INSERT INTO `myproject.my_ds.mytable_test`(Operator, Technology, Freq_Band, Sector)
SELECT Operator, Technology, Freq_Band, Sector FROM `myproject.my_ds.mytable` WHERE Freq_Band = '800' ;

The topic is already created. The custom service account has below permissions.

BigQuery Data Editor
BigQuery User
Logs Writer
Monitoring Metric Writer
Pub/Sub Publisher

The error is "User not authorized to perform this action". The screenshot is given below.

Please help.

enter image description here

Regards, Santanu

Upvotes: 1

Views: 1164

Answers (1)

Raul Saucedo
Raul Saucedo

Reputation: 1780

The account you are using needs more privileges.You can see more documentation about the privileges you need to schedule a query with BigQuery.

The privileges you need to schedule a query are:

  • bigquery.transfers.update or both bigquery.jobs.create and bigquery.transfers.get to create the transfer
  • bigquery.jobs.create to run the scheduled query
  • bigquery.datasets.update on the target dataset

To modify a scheduled query, you must be the creator of the schedule and have the following permissions:

  • bigquery.jobs.create
  • Bigquery.transfers.update

You can see the predefined roles and permission you need. You can see more documentation.

  • BigQuery ML
  • BigQuery Data Transfer Service
  • BigQuery BI Engine

EDIT

Hi, If you have admin permission for BigQuery, you need more privileges for Pub/Sub notifications. You can see more documentation about it.

You need to have sufficient permissions on the bucket you wish to monitor:

  • If you own the project that contains the bucket, you most likely have the necessary permission.
  • If you use IAM, you should have storage.buckets.update permission.
  • If you use ACLs, you should have OWNER permission.

Have sufficient permissions on the project that will receive notifications:

  • If you own the project that will receive notifications, you most likely have the necessary permission.
  • If you plan to create topics for receiving notifications, you should have pubsub.topics.create permission.
  • Whether you plan to use new or existing topics, you should have pubsub.topics.setIamPolicy permission. If you create a topic, you typically have pubsub.topics.setIamPolicy for it.

Have an existing Pub/Sub topic that you wish to send notifications to.

Get the email address of the service agent associated with the project that contains your Cloud Storage bucket.

Use the email address that you obtained in the previous step to give the service agent the IAM role pubsub.publisher for the desired Pub/Sub topic.

Upvotes: 1

Related Questions