Using BigQuery row-level security with service account

Viewed 219

I am trying to set up row level security for a BigQuery table, to be accessed by a service account. How ever, when the service account queries the table, the result is empty with no error message.

  • Granting the ROW ACCESS POLICY to a user or a group and querying returns the expected, filtered result.
  • When removing the ROW ACCESS POLICY, the service account can access the whole result, so its not another access issue.

Query to replicate the test table:

CREATE TABLE `<project>.<dataset>.planets` (name String, largest_moon String, has_aliens Bool);

INSERT `<project>.<dataset>.planets` (name, largest_moon, has_aliens)
VALUES('mercury', null, false),
      ('venus', null, false),
      ('earth', 'moon', false),
      ('mars', 'phobos', true);

GRANT `roles/bigquery.filteredDataViewer`
ON TABLE `<project>.<dataset>.planets`
TO "serviceAccount:<account_name>@<project>.iam.gserviceaccount.com";

CREATE OR REPLACE ROW ACCESS POLICY aliens_filter
ON `<project>.<dataset>.planets`
GRANT TO ("serviceAccount:<account_name>@<project>.iam.gserviceaccount.com")
FILTER USING (has_aliens = false); 
1 Answers

Found the problem by looking in the query history, the wrong service account was being used by the service. And since that service account was not int the list of grantees it got an empty result back.

Related