How to find the permissions a user has within Terraform Enterprise

Introduction

In Terraform Enterprise, users are assigned to Teams. These teams are then granted permissions at three possible levels within the platform:

• Organization

• Project

• Workspace

Problem

A user may belong to multiple teams, which can make it difficult to understand why they have certain permissions or can perform actions that were not anticipated.

Solutions:

The following queries will provide output from a database perspective on the 3 levels to see which permissions the user has. 

Make a connection to the PostgreSQL database. This can be done from the Terraform Enterprise container itself

• Go into the terraform enterprise container

docker exec -it <terraform-enterprise-container> bash

• Connect to the PostgreSQL database. Copy/Paste the below command

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

• Use the extended display by setting \x

postgres=# \x
Expanded display is on.

Queries

Organization level permissions query:

-- 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>';

Project level permissions query:

-- 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>';

Workspace level permissions query:

-- 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 query outputs will give you an overview what the user can do with the permissions given from the teams it is assigned to

Additional Information

• Documentation about organization permissions can be found here

• Documentation about project permissions can be found here

• Documentation about workspace permissions can be found here