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 POLICYto auseror agroupand 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);