I have a Snowpipe created by user A. I would then like a separate user B to check its status using the Snowflake rest API endpoint /insertReport.
- User A is an
ACCOUNTADMIN - User A created the Snowpipe.
- User A ran the following for user B's default role:
GRANT ALL ON PIPE <my pipe> TO ROLE USER_B_ROLE;
GRANT ALL ON FUTURE PIPES IN DATABASE <my db> TO ROLE USER_B_ROLE;
GRANT MONITOR ON DATABASE <my db> TO ROLE USER_B_ROLE;
GRANT USAGE ON DATABASE <my db> TO ROLE USER_B_ROLE;
GRANT MONITOR ON SCHEMA <my schema> TO ROLE USER_B_ROLE;
GRANT USAGE ON SCHEMA <my schema> TO ROLE USER_B_ROLE;
- I assumed role
USER_B_ROLE(I don't have direct access to log in as User B) and can run the following commands and see output as expected:
USE ROLE USER_B_ROLE;
SELECT CURRENT_ROLE();
-- USER_B_ROLE
DESCRIBE PIPE <my pipe>;
-- created_on,name,database_name,schema_name,definition,owner,notification_channel,comment,integration,pattern,error_integration
-- 2022-01-..., ..., ...
SELECT SYSTEM$PIPE_STATUS('<my db>.<my schema>.<my pipe>');
-- {"executionState":"RUNNING","pendingFileCount":0,"notificationChannelName":"arn:aws:sqs:...","numOutstandingMessagesOnChannel":0,"lastReceivedMessageTimestamp":"..."}
When I try using user B, using GET https://{account}.snowflakecomputing.com/v1/data/pipes/{snowpipe}/insertReport I receive: {'data': None, 'code': '390403', 'message': 'Not authorized to manage the specified object. Pipe access permission denied', 'success': False, 'headers': None}
I have checked that the public key fp and private key work since I can get a proper JWT token.
I don't think I can transfer ownership of the pipe and all future pipes of this kind as a blanket statement to user B because Terraform deploys, even those without changes, will recreate the PIPE and remove all permissions from user B - which is why I want to use FUTURE.
I've also tried GRANT MONITOR EXECUTION but that doesn't work either.
What permissions am I missing?
Related documentation:
- The Snowflake rest API page does not mention permissions needed: https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-apis.html#endpoint-insertreport
- Snowpipes only support
MONITORandOPERATE(and I gave them all already): https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#pipe-privileges - This answer says that
OWNERSHIPmust be granted, but this was 2 years ago - surely there must be a way for a non-owner to check a Snowpipe ingestion report especially when I can already check them directly via SQL: https://community.snowflake.com/s/question/0D50Z00009YAcOz/how-can-i-see-pipes-using-show-pipes-or-see-their-status-with-systempipestatus-without-having-ownership-permission-to-the-pipes-need-to-debug-issues-without-the-ability-to-change-them - This question mentions default role, but my User B is using the correct default role that I set up. Snowflake Pipe access permission denied