How to find the permissions a user has within Terraform Enterprise – HashiCorp Help Center

Introduction

In Terraform Enterprise, user permissions are managed by assigning users to teams, which are then granted access at the organization, project, or workspace level. Because a user can belong to multiple teams, it can be challenging to determine their effective permissions across the platform.

This guide provides a procedure for querying the Terraform Enterprise database directly to determine the complete set of permissions for a specific user.

Prerequisites

Before you begin, you need shell and database access to your Terraform Enterprise instance. The queries in this guide are intended to be run directly against the PostgreSQL database used by your installation.

Procedure

Follow these steps to connect to the database and run the queries to find a user's permissions.

Access the Terraform Enterprise container. This command opens a bash shell inside the running container.
```
$ docker exec -it <terraform-enterprise-container> bash
```

Connect to the PostgreSQL database using the instance-specific environment variables.

$ psql postgres://$TFE_DATABASE_USER:$TFE_DATABASE_PASSWORD@$TFE_DATABASE_HOST/$TFE_DATABASE_NAME?$TFE_DATABASE_PARAMETERS

Enable expanded display mode in psql for better readability of the query results.
```
postgres=# \x
Expanded display is on.
```
Execute the following queries to inspect permissions at each level. Remember to replace <username> with the target user's actual username.

Query Organization-Level Permissions

This query shows the teams a user belongs to and their organization-level permissions.

-- ORGANIZATION-LEVEL PERMISSIONS
-- Shows teams and their organization-level permissions for user '<username>'
-- ====================================================================================
SELECT
  u.username,
  o.name AS organization_name,
  t.name AS team_name,
  ops.manage_policies,
  ops.manage_workspaces,
  ops.manage_vcs_settings,
  ops.manage_policy_overrides,
  ops.manage_modules,
  ops.manage_providers,
  ops.manage_run_tasks,
  ops.manage_projects,
  ops.read_workspaces,
  ops.read_projects,
  ops.manage_membership,
  ops.manage_public_providers,
  ops.manage_public_modules,
  ops.manage_teams,
  ops.manage_organization_access,
  ops.access_secret_teams,
  ops.manage_agent_pools,
  ops.manage_registry_components
FROM rails.users u
INNER JOIN rails.organization_users ou ON u.id = ou.user_id
INNER JOIN rails.organizations o ON ou.organization_id = o.id
INNER JOIN rails.memberships m ON ou.id = m.organization_user_id
INNER JOIN rails.teams t ON m.team_id = t.id
LEFT JOIN rails.organization_permission_sets ops ON t.id = ops.team_id
WHERE u.username = '<username>';

Query Project-Level Permissions

This query shows the teams a user belongs to and their project-level permissions.

-- PROJECT-LEVEL PERMISSIONS
-- Shows teams and their project-level permissions for user '<username>'
-- ====================================================================================
SELECT
  u.username,
  o.name AS organization_name,
  t.name AS team_name,
  p.name AS project_name,
  tp.access,
  tp.project_settings_permission,
  tp.project_teams_permission,
  tp.workspace_create_permission,
  tp.workspace_locking_permission,
  tp.workspace_move_permission,
  tp.workspace_runs_permission,
  tp.workspace_run_tasks_permission,
  tp.workspace_sentinel_mocks_permission,
  tp.workspace_state_versions_permission,
  tp.workspace_variables_permission,
  tp.workspace_delete_permission,
  tp.workspace_read_permission,
  tp.hcp_role_id,
  tp.project_variable_sets_permission
FROM rails.users u
INNER JOIN rails.organization_users ou ON u.id = ou.user_id
INNER JOIN rails.organizations o ON ou.organization_id = o.id
INNER JOIN rails.memberships m ON ou.id = m.organization_user_id
INNER JOIN rails.teams t ON m.team_id = t.id
LEFT JOIN rails.team_projects tp ON t.id = tp.team_id
LEFT JOIN rails.projects p ON tp.project_id = p.id
WHERE u.username = '<username>';

Query Workspace-Level Permissions

This query shows the teams a user belongs to and their workspace-level permissions.

-- WORKSPACE-LEVEL PERMISSIONS
-- Shows teams and their workspace-level permissions for user '<username>'
-- ====================================================================================
SELECT
  u.username,
  o.name AS organization_name,
  t.name AS team_name,
  w.name AS workspace_name,
  tw.runs_permission,
  tw.variables_permission,
  tw.state_versions_permission,
  tw.sentinel_mocks_permission,
  tw.workspace_locking_permission,
  tw.run_tasks_permission
FROM rails.users u
INNER JOIN rails.organization_users ou ON u.id = ou.user_id
INNER JOIN rails.organizations o ON ou.organization_id = o.id
INNER JOIN rails.memberships m ON ou.id = m.organization_user_id
INNER JOIN rails.teams t ON m.team_id = t.id
LEFT JOIN rails.team_workspaces tw ON t.id = tw.team_id
LEFT JOIN rails.workspaces w ON tw.workspace_id = w.id
WHERE u.username = '<username>';

Outcome

The output from these queries provides a comprehensive overview of the permissions granted to a user through their team memberships at the organization, project, and workspace levels.

Additional Information

For more details, refer to the Organization Permissions documentation.
For more details, refer to the Project Permissions documentation.
For more details, refer to the Workspace Permissions documentation.