Created
August 5, 2024 20:22
-
-
Save benedikt-buchert/3d0eee13f6a260fed611558dcb62639b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import json | |
| from google.cloud import recommender_v1 | |
| from google.auth import default | |
| import gspread | |
| from google import auth | |
| import pandas as pd | |
| from google.protobuf.json_format import MessageToJson | |
| def extract_permissions_and_user(data): | |
| # Extract the username | |
| data_dict = dict(data) | |
| username = MessageToJson(data_dict['member']) | |
| permssion_json = MessageToJson(data_dict['exercisedPermissions']) | |
| exercised_permissions = [item['permission'] | |
| for item in json.loads(permssion_json)] | |
| return username, exercised_permissions | |
| def get_excess_permissions(project_id): | |
| """ | |
| Retrieve excess permissions insights for a given project. | |
| Args: | |
| project_id (str): The ID of the GCP project. | |
| Returns: | |
| list: A list of dictionaries containing excess permission details. | |
| """ | |
| client = recommender_v1.RecommenderClient() | |
| parent = f"projects/{project_id}/locations/global/insightTypes/google.iam.policy.Insight" | |
| excess_permissions = [] | |
| try: | |
| for insight in client.list_insights(parent=parent): | |
| description = insight.description | |
| content = insight.content.pb | |
| data = extract_permissions_and_user(content) | |
| print(data) | |
| excess_permissions.append({ | |
| "project_id": project_id, | |
| "user": data[0], | |
| "exercised_permissions": data[1] | |
| }) | |
| except Exception as e: | |
| print(f"Error processing project {project_id}: {str(e)}") | |
| return excess_permissions | |
| def create_dataframe(project_data): | |
| data = {item['user']: item['exercised_permissions'] | |
| for item in project_data} | |
| # Find the maximum number of permissions | |
| max_permissions = max(len(permissions) for permissions in data.values()) | |
| # Pad shorter lists with empty strings | |
| padded_data = {user: permissions + [''] * (max_permissions - len(permissions)) | |
| for user, permissions in data.items()} | |
| df = pd.DataFrame(padded_data) | |
| return df | |
| def write_to_google_sheet(data, sheet_key): | |
| credentials, project_id = auth.default() | |
| gc = gspread.authorize(credentials) | |
| try: | |
| spreadsheet = gc.open_by_key(sheet_key) | |
| except gspread.SpreadsheetNotFound: | |
| print(f"Spreadsheet with key { | |
| sheet_key} not found. Please check the key and try again.") | |
| return | |
| for project_data in data: | |
| project_id = project_data[0]['project_id'] | |
| # Create a new worksheet for the project if it doesn't exist | |
| try: | |
| worksheet = spreadsheet.worksheet(project_id) | |
| except gspread.WorksheetNotFound: | |
| worksheet = spreadsheet.add_worksheet( | |
| title=project_id, rows=1, cols=1) | |
| # Prepare the data for writing | |
| dataframe = create_dataframe(project_data) | |
| # Write the data to the worksheet | |
| worksheet.clear() | |
| worksheet.update([dataframe.columns.values.tolist() | |
| ] + dataframe.values.tolist()) | |
| def main(projects, sheet_key): | |
| all_project_data = [] | |
| for project_id in projects: | |
| project_data = get_excess_permissions(project_id) | |
| if project_data: | |
| all_project_data.append(project_data) | |
| write_to_google_sheet(all_project_data, sheet_key) | |
| if __name__ == "__main__": | |
| # Add your project IDs here | |
| projects = ["list_allproject_ids"] | |
| # Add your Google Sheet key here | |
| sheet_key = "replacewithspreadsheetid" | |
| main(projects, sheet_key) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Scopes for local usage:
gcloud auth application-default login \ --scopes =openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets