TestSQLEditorTableScopedDML reproduces SUP-222: a project IAM grant that gives bb.sql.dml scoped to a specific table must authorize an INSERT to that table in the SQL Editor. Postgres is required: it is a "newACL" engine, so validateQueryRequest / ValidateSQLForEditor is skipped (backend/api/v1/sql
(t *testing.T)
| 31 | // per-column resource.table_name. Until the fix lands, the assertion below FAILS |
| 32 | // with "permission denied to access resources: <db>" — the expected RED outcome. |
| 33 | func TestSQLEditorTableScopedDML(t *testing.T) { |
| 34 | t.Parallel() |
| 35 | a := require.New(t) |
| 36 | ctx := context.Background() |
| 37 | ctl := &controller{} |
| 38 | ctx, err := ctl.StartServerWithExternalPg(ctx) |
| 39 | a.NoError(err) |
| 40 | defer ctl.Close(ctx) |
| 41 | |
| 42 | // Save the owner token so we can swap identities and swap back. |
| 43 | ownerToken := ctl.authInterceptor.token |
| 44 | |
| 45 | pgContainer, err := getPgContainer(ctx) |
| 46 | defer func() { |
| 47 | pgContainer.Close(ctx) |
| 48 | }() |
| 49 | a.NoError(err) |
| 50 | |
| 51 | // 1. Create a Postgres instance + database as the owner. |
| 52 | instanceResp, err := ctl.instanceServiceClient.CreateInstance(ctx, connect.NewRequest(&v1pb.CreateInstanceRequest{ |
| 53 | InstanceId: generateRandomString("instance"), |
| 54 | Instance: &v1pb.Instance{ |
| 55 | Title: "pgInstance", |
| 56 | Engine: v1pb.Engine_POSTGRES, |
| 57 | Environment: new("environments/prod"), |
| 58 | Activation: true, |
| 59 | DataSources: []*v1pb.DataSource{{Type: v1pb.DataSourceType_ADMIN, Host: pgContainer.host, Port: pgContainer.port, Username: "postgres", Password: "root-password", Id: "admin"}}, |
| 60 | }, |
| 61 | })) |
| 62 | a.NoError(err) |
| 63 | instance := instanceResp.Msg |
| 64 | |
| 65 | const databaseName = "sup222" |
| 66 | err = ctl.createDatabase(ctx, ctl.project, instance, nil, databaseName, "postgres") |
| 67 | a.NoError(err) |
| 68 | |
| 69 | databaseResp, err := ctl.databaseServiceClient.GetDatabase(ctx, connect.NewRequest(&v1pb.GetDatabaseRequest{ |
| 70 | Name: fmt.Sprintf("%s/databases/%s", instance.Name, databaseName), |
| 71 | })) |
| 72 | a.NoError(err) |
| 73 | database := databaseResp.Msg |
| 74 | |
| 75 | // 2. Create the table public.locked_accounts via the change-database flow. |
| 76 | setupSheetResp, err := ctl.sheetServiceClient.CreateSheet(ctx, connect.NewRequest(&v1pb.CreateSheetRequest{ |
| 77 | Parent: ctl.project.Name, |
| 78 | Sheet: &v1pb.Sheet{Content: []byte(`CREATE TABLE public.locked_accounts (account_id bigint);`)}, |
| 79 | })) |
| 80 | a.NoError(err) |
| 81 | err = ctl.changeDatabase(ctx, ctl.project, database, setupSheetResp.Msg, false) |
| 82 | a.NoError(err) |
| 83 | |
| 84 | // 3. Create a custom role with only bb.sql.dml. |
| 85 | _, err = ctl.roleServiceClient.CreateRole(ctx, connect.NewRequest(&v1pb.CreateRoleRequest{ |
| 86 | Role: &v1pb.Role{ |
| 87 | Title: "repro-write", |
| 88 | Permissions: []string{"bb.sql.dml"}, |
| 89 | }, |
| 90 | RoleId: "repro-write", |
nothing calls this directly
no test coverage detected