// DatabaseDetails is an endpoint that returns grants and a list of table names // for the specified database. func (s *adminServer) DatabaseDetails(ctx context.Context, req *DatabaseDetailsRequest) (*DatabaseDetailsResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) // Placeholders don't work with SHOW statements, so we need to manually // escape the database name. // // TODO(cdo): Use placeholders when they're supported by SHOW. escDBName := parser.Name(req.Database).String() query := fmt.Sprintf("SHOW GRANTS ON DATABASE %s; SHOW TABLES FROM %s;", escDBName, escDBName) r := s.sqlExecutor.ExecuteStatements(ctx, session, query, nil) if pErr := s.firstNotFoundError(r.ResultList); pErr != nil { return nil, grpc.Errorf(codes.NotFound, "%s", pErr) } if err := s.checkQueryResults(r.ResultList, 2); err != nil { return nil, s.serverError(err) } // Marshal grants. var resp DatabaseDetailsResponse { const ( userCol = "User" privilegesCol = "Privileges" ) scanner := makeResultScanner(r.ResultList[0].Columns) for _, row := range r.ResultList[0].Rows { // Marshal grant, splitting comma-separated privileges into a proper slice. var grant DatabaseDetailsResponse_Grant var privileges string if err := scanner.Scan(row, userCol, &grant.User); err != nil { return nil, err } if err := scanner.Scan(row, privilegesCol, &privileges); err != nil { return nil, err } grant.Privileges = strings.Split(privileges, ",") resp.Grants = append(resp.Grants, grant) } } // Marshal table names. { const tableCol = "Table" scanner := makeResultScanner(r.ResultList[1].Columns) if a, e := len(r.ResultList[1].Columns), 1; a != e { return nil, s.serverErrorf("show tables columns mismatch: %d != expected %d", a, e) } for _, row := range r.ResultList[1].Rows { var tableName string if err := scanner.Scan(row, tableCol, &tableName); err != nil { return nil, err } resp.TableNames = append(resp.TableNames, tableName) } } return &resp, nil }
// Events is an endpoint that returns the latest event log entries, with the following // optional URL parameters: // // type=STRING returns events with this type (e.g. "create_table") // targetID=INT returns events for that have this targetID func (s *adminServer) Events(c context.Context, req *EventsRequest) (*EventsResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) // Execute the query. q := &sqlQuery{} q.Append("SELECT timestamp, eventType, targetID, reportingID, info, uniqueID ") q.Append("FROM system.eventlog ") q.Append("WHERE true ") // This simplifies the WHERE clause logic below. if len(req.Type) > 0 { q.Append("AND eventType = $ ", parser.DString(req.Type)) } if req.TargetId > 0 { q.Append("AND targetID = $ ", parser.DInt(req.TargetId)) } q.Append("ORDER BY timestamp DESC ") q.Append("LIMIT $", parser.DInt(apiEventLimit)) if len(q.Errors()) > 0 { return nil, s.serverErrors(q.Errors()) } r := s.sqlExecutor.ExecuteStatements(session, q.String(), q.Params()) if err := s.checkQueryResults(r.ResultList, 1); err != nil { return nil, s.serverError(err) } // Marshal response. var resp EventsResponse scanner := newResultScanner(r.ResultList[0].Columns) for _, row := range r.ResultList[0].Rows { var event EventsResponse_Event var ts time.Time if err := scanner.ScanIndex(row, 0, &ts); err != nil { return nil, err } nanos := ts.UnixNano() event.Timestamp = &EventsResponse_Event_Timestamp{Sec: nanos / 1e9, Nsec: uint32(nanos % 1e9)} if err := scanner.ScanIndex(row, 1, &event.EventType); err != nil { return nil, err } if err := scanner.ScanIndex(row, 2, &event.TargetID); err != nil { return nil, err } if err := scanner.ScanIndex(row, 3, &event.ReportingID); err != nil { return nil, err } if err := scanner.ScanIndex(row, 4, &event.Info); err != nil { return nil, err } if err := scanner.ScanIndex(row, 5, &event.UniqueID); err != nil { return nil, err } resp.Events = append(resp.Events, &event) } return &resp, nil }
// SetUIData is an endpoint that stores the given key/value pairs in the // system.ui table. See GetUIData for more details on semantics. func (s *adminServer) SetUIData(ctx context.Context, req *SetUIDataRequest) (*SetUIDataResponse, error) { if len(req.KeyValues) == 0 { return nil, grpc.Errorf(codes.InvalidArgument, "KeyValues cannot be empty") } session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) for key, val := range req.KeyValues { // Do an upsert of the key. We update each key in a separate transaction to // avoid long-running transactions and possible deadlocks. br := s.sqlExecutor.ExecuteStatements(ctx, session, "BEGIN;", nil) if err := s.checkQueryResults(br.ResultList, 1); err != nil { return nil, s.serverError(err) } // See if the key already exists. resp, err := s.getUIData(session, s.getUser(req), []string{key}) if err != nil { return nil, s.serverError(err) } _, alreadyExists := resp.KeyValues[key] // INSERT or UPDATE as appropriate. if alreadyExists { query := "UPDATE system.ui SET value = $1, lastUpdated = NOW() WHERE key = $2; COMMIT;" params := []parser.Datum{ parser.DString(val), // $1 parser.DString(key), // $2 } r := s.sqlExecutor.ExecuteStatements(ctx, session, query, params) if err := s.checkQueryResults(r.ResultList, 2); err != nil { return nil, s.serverError(err) } if a, e := r.ResultList[0].RowsAffected, 1; a != e { return nil, s.serverErrorf("rows affected %d != expected %d", a, e) } } else { query := "INSERT INTO system.ui (key, value, lastUpdated) VALUES ($1, $2, NOW()); COMMIT;" params := []parser.Datum{ parser.DString(key), // $1 parser.DBytes(val), // $2 } r := s.sqlExecutor.ExecuteStatements(ctx, session, query, params) if err := s.checkQueryResults(r.ResultList, 2); err != nil { return nil, s.serverError(err) } if a, e := r.ResultList[0].RowsAffected, 1; a != e { return nil, s.serverErrorf("rows affected %d != expected %d", a, e) } } } return &SetUIDataResponse{}, nil }
// SetUIData is an endpoint that sets the data associated with a key. func (s *adminServer) SetUIData(_ context.Context, req *SetUIDataRequest) (*SetUIDataResponse, error) { if len(req.Key) == 0 { return nil, grpc.Errorf(codes.InvalidArgument, "key cannot be empty") } session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) // Do an upsert of the key. br := s.sqlExecutor.ExecuteStatements(session, "BEGIN;", nil) if err := s.checkQueryResults(br.ResultList, 1); err != nil { return nil, s.serverError(err) } // See if the key already exists. alreadyExists := true if _, _, err := s.getUIData(session, s.getUser(req), req.Key); err != nil { if err != errUIKeyNotFound { return nil, s.serverError(err) } alreadyExists = false } // INSERT or UPDATE as appropriate. if alreadyExists { query := "UPDATE system.ui SET value = $1, lastUpdated = NOW() WHERE key = $2; COMMIT;" params := []parser.Datum{ parser.DString(req.Value), // $1 parser.DString(req.Key), // $2 } r := s.sqlExecutor.ExecuteStatements(session, query, params) if err := s.checkQueryResults(r.ResultList, 2); err != nil { return nil, s.serverError(err) } if a, e := r.ResultList[0].RowsAffected, 1; a != e { return nil, s.serverErrorf("rows affected %d != expected %d", a, e) } } else { query := "INSERT INTO system.ui (key, value, lastUpdated) VALUES ($1, $2, NOW()); COMMIT;" params := []parser.Datum{ parser.DString(req.Key), // $1 parser.DBytes(req.Value), // $2 } r := s.sqlExecutor.ExecuteStatements(session, query, params) if err := s.checkQueryResults(r.ResultList, 2); err != nil { return nil, s.serverError(err) } if a, e := r.ResultList[0].RowsAffected, 1; a != e { return nil, s.serverErrorf("rows affected %d != expected %d", a, e) } } return &SetUIDataResponse{}, nil }
func makeV3Conn( conn net.Conn, executor *sql.Executor, metrics *serverMetrics, sessionArgs sql.SessionArgs) v3Conn { return v3Conn{ conn: conn, rd: bufio.NewReader(conn), wr: bufio.NewWriter(conn), executor: executor, writeBuf: writeBuffer{bytecount: metrics.bytesOutCount}, metrics: metrics, session: sql.NewSession(sessionArgs, executor, conn.RemoteAddr()), } }
// Users returns a list of users, stripped of any passwords. func (s *adminServer) Users(ctx context.Context, req *UsersRequest) (*UsersResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) query := "SELECT username FROM system.users" r := s.sqlExecutor.ExecuteStatements(ctx, session, query, nil) if err := s.checkQueryResults(r.ResultList, 1); err != nil { return nil, s.serverError(err) } var resp UsersResponse for _, row := range r.ResultList[0].Rows { resp.Users = append(resp.Users, UsersResponse_User{string(row.Values[0].(parser.DString))}) } return &resp, nil }
// GetUIData returns data associated with the given keys, which was stored // earlier through SetUIData. // // The stored values are meant to be opaque to the server. In the rare case that // the server code needs to call this method, it should only read from keys that // have the prefix `serverUIDataKeyPrefix`. func (s *adminServer) GetUIData(_ context.Context, req *GetUIDataRequest) (*GetUIDataResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) if len(req.Keys) == 0 { return nil, grpc.Errorf(codes.InvalidArgument, "keys cannot be empty") } resp, err := s.getUIData(session, s.getUser(req), req.Keys) if err != nil { return nil, s.serverError(err) } return resp, nil }
// GetUIData returns data associated with the given key, which was stored // earlier through SetUIData. func (s *adminServer) GetUIData(_ context.Context, req *GetUIDataRequest) (*GetUIDataResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) if len(req.Key) == 0 { return nil, grpc.Errorf(codes.InvalidArgument, "key cannot be empty") } val, ts, err := s.getUIData(session, s.getUser(req), req.Key) if err != nil { if err == errUIKeyNotFound { return nil, grpc.Errorf(codes.NotFound, "key %s not found", req.Key) } return nil, s.serverError(err) } return &GetUIDataResponse{Value: val, LastUpdated: &ts}, nil }
// Databases is an endpoint that returns a list of databases. func (s *adminServer) Databases(ctx context.Context, req *DatabasesRequest) (*DatabasesResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) r := s.sqlExecutor.ExecuteStatements(ctx, session, "SHOW DATABASES;", nil) if err := s.checkQueryResults(r.ResultList, 1); err != nil { return nil, s.serverError(err) } var resp DatabasesResponse for _, row := range r.ResultList[0].Rows { dbname, ok := row.Values[0].(parser.DString) if !ok { return nil, s.serverErrorf("type assertion failed on db name: %T", row.Values[0]) } resp.Databases = append(resp.Databases, string(dbname)) } return &resp, nil }
func TestAdminAPIUsers(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() ts := s.(*TestServer) // Create sample users. session := sql.NewSession( context.Background(), sql.SessionArgs{User: security.RootUser}, ts.sqlExecutor, nil) query := ` INSERT INTO system.users (username, hashedPassword) VALUES ('admin', 'abc'), ('bob', 'xyz')` res := ts.sqlExecutor.ExecuteStatements(session, query, nil) if a, e := len(res.ResultList), 1; a != e { t.Fatalf("len(results) %d != %d", a, e) } else if res.ResultList[0].Err != nil { t.Fatal(res.ResultList[0].Err) } // Query the API for users. var resp serverpb.UsersResponse if err := apiGet(s, "users", &resp); err != nil { t.Fatal(err) } expResult := serverpb.UsersResponse{ Users: []serverpb.UsersResponse_User{ {Username: "******"}, {Username: "******"}, }, } // Verify results. const sortKey = "Username" testutils.SortStructs(resp.Users, sortKey) testutils.SortStructs(expResult.Users, sortKey) if !reflect.DeepEqual(resp, expResult) { t.Fatalf("result %v != expected %v", resp, expResult) } }
func TestAdminAPIEvents(t *testing.T) { defer leaktest.AfterTest(t)() s := StartTestServer(t) defer s.Stop() session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, s.sqlExecutor, nil) setupQueries := []string{ "CREATE DATABASE api_test", "CREATE TABLE api_test.tbl1 (a INT)", "CREATE TABLE api_test.tbl2 (a INT)", "CREATE TABLE api_test.tbl3 (a INT)", "DROP TABLE api_test.tbl1", "DROP TABLE api_test.tbl2", } for _, q := range setupQueries { res := s.sqlExecutor.ExecuteStatements(session, q, nil) if res.ResultList[0].PErr != nil { t.Fatalf("error executing '%s': %s", q, res.ResultList[0].PErr) } } var zeroTimestamp EventsResponse_Event_Timestamp testcases := []struct { eventType sql.EventLogType expCount int }{ {"", 7}, {sql.EventLogNodeJoin, 1}, {sql.EventLogNodeRestart, 0}, {sql.EventLogDropDatabase, 0}, {sql.EventLogCreateDatabase, 1}, {sql.EventLogDropTable, 2}, {sql.EventLogCreateTable, 3}, } for i, tc := range testcases { var url string if len(tc.eventType) > 0 { url = fmt.Sprintf("events?type=%s", tc.eventType) } else { url = "events" } var resp EventsResponse if err := apiGet(s, url, &resp); err != nil { t.Fatal(err) } if a, e := len(resp.Events), tc.expCount; a != e { t.Errorf("%d: # of events %d != expected %d", i, a, e) } // Ensure we don't have blank / nonsensical fields. for _, e := range resp.Events { if *e.Timestamp == zeroTimestamp { t.Errorf("%d: missing/empty timestamp", i) } if len(tc.eventType) > 0 { if a, e := e.EventType, string(tc.eventType); a != e { t.Errorf("%d: event type %s != expected %s", i, a, e) } } else { if len(e.EventType) == 0 { t.Errorf("%d: missing event type in event", i) } } if e.TargetID == 0 { t.Errorf("%d: missing/empty TargetID", i) } if e.ReportingID == 0 { t.Errorf("%d: missing/empty ReportingID", i) } if len(e.Info) == 0 { t.Errorf("%d: missing/empty Info", i) } if len(e.UniqueID) == 0 { t.Errorf("%d: missing/empty UniqueID", i) } } } }
func TestAdminAPIDatabases(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() ts := s.(*TestServer) // Test databases endpoint. const testdb = "test" session := sql.NewSession( context.Background(), sql.SessionArgs{User: security.RootUser}, ts.sqlExecutor, nil) query := "CREATE DATABASE " + testdb createRes := ts.sqlExecutor.ExecuteStatements(session, query, nil) if createRes.ResultList[0].Err != nil { t.Fatal(createRes.ResultList[0].Err) } var resp serverpb.DatabasesResponse if err := apiGet(s, "databases", &resp); err != nil { t.Fatal(err) } // We should have three databases: // - system database // - information_schema // - newly created test database if a, e := len(resp.Databases), 3; a != e { t.Fatalf("length of result %d != expected %d", a, e) } sort.Strings(resp.Databases) for i, e := range []string{"information_schema", "system", testdb} { if a := resp.Databases[i]; a != e { t.Fatalf("database name %s != expected %s", a, e) } } // Test database details endpoint. privileges := []string{"SELECT", "UPDATE"} testuser := "******" grantQuery := "GRANT " + strings.Join(privileges, ", ") + " ON DATABASE " + testdb + " TO " + testuser grantRes := s.(*TestServer).sqlExecutor.ExecuteStatements(session, grantQuery, nil) if grantRes.ResultList[0].Err != nil { t.Fatal(grantRes.ResultList[0].Err) } var details serverpb.DatabaseDetailsResponse if err := apiGet(s, "databases/"+testdb, &details); err != nil { t.Fatal(err) } if a, e := len(details.Grants), 2; a != e { t.Fatalf("# of grants %d != expected %d", a, e) } for _, grant := range details.Grants { switch grant.User { case security.RootUser: if !reflect.DeepEqual(grant.Privileges, []string{"ALL"}) { t.Fatalf("privileges %v != expected %v", details.Grants[0].Privileges, privileges) } case testuser: sort.Strings(grant.Privileges) if !reflect.DeepEqual(grant.Privileges, privileges) { t.Fatalf("privileges %v != expected %v", grant.Privileges, privileges) } default: t.Fatalf("unknown grant to user %s", grant.User) } } }
// TableDetails is an endpoint that returns columns, indices, and other // relevant details for the specified table. func (s *adminServer) TableDetails(ctx context.Context, req *TableDetailsRequest) ( *TableDetailsResponse, error) { session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil) // TODO(cdo): Use real placeholders for the table and database names when we've extended our SQL // grammar to allow that. escDbName := parser.Name(req.Database).String() escTableName := parser.Name(req.Table).String() escQualTable := fmt.Sprintf("%s.%s", escDbName, escTableName) query := fmt.Sprintf("SHOW COLUMNS FROM %s; SHOW INDEX FROM %s; SHOW GRANTS ON TABLE %s", escQualTable, escQualTable, escQualTable) r := s.sqlExecutor.ExecuteStatements(ctx, session, query, nil) if pErr := s.firstNotFoundError(r.ResultList); pErr != nil { return nil, grpc.Errorf(codes.NotFound, "%s", pErr) } if err := s.checkQueryResults(r.ResultList, 3); err != nil { return nil, err } var resp TableDetailsResponse // Marshal SHOW COLUMNS result. // // TODO(cdo): protobuf v3's default behavior for fields with zero values (e.g. empty strings) // is to suppress them. So, if protobuf field "foo" is an empty string, "foo" won't show // up in the marshalled JSON. I feel that this is counterintuitive, and this should be fixed // for our API. { const ( fieldCol = "Field" // column name typeCol = "Type" nullCol = "Null" defaultCol = "Default" ) scanner := makeResultScanner(r.ResultList[0].Columns) for _, row := range r.ResultList[0].Rows { var col TableDetailsResponse_Column if err := scanner.Scan(row, fieldCol, &col.Name); err != nil { return nil, err } if err := scanner.Scan(row, typeCol, &col.Type); err != nil { return nil, err } if err := scanner.Scan(row, nullCol, &col.Nullable); err != nil { return nil, err } isDefaultNull, err := scanner.IsNull(row, defaultCol) if err != nil { return nil, err } if !isDefaultNull { if err := scanner.Scan(row, defaultCol, &col.Default); err != nil { return nil, err } } resp.Columns = append(resp.Columns, col) } } // Marshal SHOW INDEX result. { const ( nameCol = "Name" uniqueCol = "Unique" seqCol = "Seq" columnCol = "Column" directionCol = "Direction" storingCol = "Storing" ) scanner := makeResultScanner(r.ResultList[1].Columns) for _, row := range r.ResultList[1].Rows { // Marshal grant, splitting comma-separated privileges into a proper slice. var index TableDetailsResponse_Index if err := scanner.Scan(row, nameCol, &index.Name); err != nil { return nil, err } if err := scanner.Scan(row, uniqueCol, &index.Unique); err != nil { return nil, err } if err := scanner.Scan(row, seqCol, &index.Seq); err != nil { return nil, err } if err := scanner.Scan(row, columnCol, &index.Column); err != nil { return nil, err } if err := scanner.Scan(row, directionCol, &index.Direction); err != nil { return nil, err } if err := scanner.Scan(row, storingCol, &index.Storing); err != nil { return nil, err } resp.Indexes = append(resp.Indexes, index) } } // Marshal SHOW GRANTS result. { const ( userCol = "User" privilegesCol = "Privileges" ) scanner := makeResultScanner(r.ResultList[2].Columns) for _, row := range r.ResultList[2].Rows { // Marshal grant, splitting comma-separated privileges into a proper slice. var grant TableDetailsResponse_Grant var privileges string if err := scanner.Scan(row, userCol, &grant.User); err != nil { return nil, err } if err := scanner.Scan(row, privilegesCol, &privileges); err != nil { return nil, err } grant.Privileges = strings.Split(privileges, ",") resp.Grants = append(resp.Grants, grant) } } // Get the number of ranges in the table. We get the key span for the table // data. Then, we count the number of ranges that make up that key span. { var iexecutor sql.InternalExecutor var tableSpan roachpb.Span if pErr := s.db.Txn(func(txn *client.Txn) *roachpb.Error { var pErr *roachpb.Error tableSpan, pErr = iexecutor.GetTableSpan(s.getUser(req), txn, escDbName, escTableName) return pErr }); pErr != nil { return nil, s.serverError(pErr.GoError()) } tableRSpan := roachpb.RSpan{} var err error tableRSpan.Key, err = keys.Addr(tableSpan.Key) if err != nil { return nil, s.serverError(err) } tableRSpan.EndKey, err = keys.Addr(tableSpan.EndKey) if err != nil { return nil, s.serverError(err) } rangeCount, pErr := s.distSender.CountRanges(tableRSpan) if pErr != nil { return nil, s.serverError(pErr.GoError()) } resp.RangeCount = rangeCount } return &resp, nil }
// TableDetails is an endpoint that returns columns, indices, and other // relevant details for the specified table. func (s *adminServer) TableDetails( ctx context.Context, req *serverpb.TableDetailsRequest, ) (*serverpb.TableDetailsResponse, error) { args := sql.SessionArgs{User: s.getUser(req)} session := sql.NewSession(ctx, args, s.server.sqlExecutor, nil) // TODO(cdo): Use real placeholders for the table and database names when we've extended our SQL // grammar to allow that. escDBName := parser.Name(req.Database).String() escTableName := parser.Name(req.Table).String() escQualTable := fmt.Sprintf("%s.%s", escDBName, escTableName) query := fmt.Sprintf("SHOW COLUMNS FROM %s; SHOW INDEX FROM %s; SHOW GRANTS ON TABLE %s; SHOW CREATE TABLE %s;", escQualTable, escQualTable, escQualTable, escQualTable) r := s.server.sqlExecutor.ExecuteStatements(session, query, nil) if err := s.firstNotFoundError(r.ResultList); err != nil { return nil, grpc.Errorf(codes.NotFound, "%s", err) } if err := s.checkQueryResults(r.ResultList, 4); err != nil { return nil, err } var resp serverpb.TableDetailsResponse // Marshal SHOW COLUMNS result. // // TODO(cdo): protobuf v3's default behavior for fields with zero values (e.g. empty strings) // is to suppress them. So, if protobuf field "foo" is an empty string, "foo" won't show // up in the marshalled JSON. I feel that this is counterintuitive, and this should be fixed // for our API. { const ( fieldCol = "Field" // column name typeCol = "Type" nullCol = "Null" defaultCol = "Default" ) scanner := makeResultScanner(r.ResultList[0].Columns) for _, row := range r.ResultList[0].Rows { var col serverpb.TableDetailsResponse_Column if err := scanner.Scan(row, fieldCol, &col.Name); err != nil { return nil, err } if err := scanner.Scan(row, typeCol, &col.Type); err != nil { return nil, err } if err := scanner.Scan(row, nullCol, &col.Nullable); err != nil { return nil, err } isDefaultNull, err := scanner.IsNull(row, defaultCol) if err != nil { return nil, err } if !isDefaultNull { if err := scanner.Scan(row, defaultCol, &col.DefaultValue); err != nil { return nil, err } } resp.Columns = append(resp.Columns, col) } } // Marshal SHOW INDEX result. { const ( nameCol = "Name" uniqueCol = "Unique" seqCol = "Seq" columnCol = "Column" directionCol = "Direction" storingCol = "Storing" ) scanner := makeResultScanner(r.ResultList[1].Columns) for _, row := range r.ResultList[1].Rows { // Marshal grant, splitting comma-separated privileges into a proper slice. var index serverpb.TableDetailsResponse_Index if err := scanner.Scan(row, nameCol, &index.Name); err != nil { return nil, err } if err := scanner.Scan(row, uniqueCol, &index.Unique); err != nil { return nil, err } if err := scanner.Scan(row, seqCol, &index.Seq); err != nil { return nil, err } if err := scanner.Scan(row, columnCol, &index.Column); err != nil { return nil, err } if err := scanner.Scan(row, directionCol, &index.Direction); err != nil { return nil, err } if err := scanner.Scan(row, storingCol, &index.Storing); err != nil { return nil, err } resp.Indexes = append(resp.Indexes, index) } } // Marshal SHOW GRANTS result. { const ( userCol = "User" privilegesCol = "Privileges" ) scanner := makeResultScanner(r.ResultList[2].Columns) for _, row := range r.ResultList[2].Rows { // Marshal grant, splitting comma-separated privileges into a proper slice. var grant serverpb.TableDetailsResponse_Grant var privileges string if err := scanner.Scan(row, userCol, &grant.User); err != nil { return nil, err } if err := scanner.Scan(row, privilegesCol, &privileges); err != nil { return nil, err } grant.Privileges = strings.Split(privileges, ",") resp.Grants = append(resp.Grants, grant) } } // Marshal SHOW CREATE TABLE result. { const createTableCol = "CreateTable" showResult := r.ResultList[3] if len(showResult.Rows) != 1 { return nil, s.serverErrorf("CreateTable response not available.") } scanner := makeResultScanner(showResult.Columns) var createStmt string if err := scanner.Scan(showResult.Rows[0], createTableCol, &createStmt); err != nil { return nil, err } resp.CreateTableStatement = createStmt } // Get the number of ranges in the table. We get the key span for the table // data. Then, we count the number of ranges that make up that key span. { var iexecutor sql.InternalExecutor var tableSpan roachpb.Span if err := s.server.db.Txn(func(txn *client.Txn) error { var err error tableSpan, err = iexecutor.GetTableSpan(s.getUser(req), txn, escDBName, escTableName) return err }); err != nil { return nil, s.serverError(err) } tableRSpan := roachpb.RSpan{} var err error tableRSpan.Key, err = keys.Addr(tableSpan.Key) if err != nil { return nil, s.serverError(err) } tableRSpan.EndKey, err = keys.Addr(tableSpan.EndKey) if err != nil { return nil, s.serverError(err) } rangeCount, err := s.server.distSender.CountRanges(tableRSpan) if err != nil { return nil, s.serverError(err) } resp.RangeCount = rangeCount } // Query the zone configuration for this table. { path, err := s.queryDescriptorIDPath(session, []string{escDBName, escTableName}) if err != nil { return nil, s.serverError(err) } id, zone, zoneExists, err := s.queryZonePath(session, path) if err != nil { return nil, s.serverError(err) } if !zoneExists { zone = config.DefaultZoneConfig() } resp.ZoneConfig = zone switch id { case path[1]: resp.ZoneConfigLevel = serverpb.ZoneConfigurationLevel_DATABASE case path[2]: resp.ZoneConfigLevel = serverpb.ZoneConfigurationLevel_TABLE default: resp.ZoneConfigLevel = serverpb.ZoneConfigurationLevel_CLUSTER } } return &resp, nil }
func TestAdminAPITableDetails(t *testing.T) { defer leaktest.AfterTest(t)() s := StartTestServer(t) defer s.Stop() session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, s.sqlExecutor, nil) setupQueries := []string{ "CREATE DATABASE test", ` CREATE TABLE test.tbl ( nulls_allowed INT, nulls_not_allowed INT NOT NULL DEFAULT 1000, default2 INT DEFAULT 2, string_default STRING DEFAULT 'default_string' )`, "GRANT SELECT ON test.tbl TO readonly", "GRANT SELECT,UPDATE,DELETE ON test.tbl TO app", "CREATE INDEX descIdx ON test.tbl (default2 DESC)", } for _, q := range setupQueries { res := s.sqlExecutor.ExecuteStatements(session, q, nil) if res.ResultList[0].PErr != nil { t.Fatalf("error executing '%s': %s", q, res.ResultList[0].PErr) } } // Perform API call. var resp TableDetailsResponse if err := apiGet(s, "databases/test/tables/tbl", &resp); err != nil { t.Fatal(err) } // Verify columns. expColumns := []TableDetailsResponse_Column{ {Name: "nulls_allowed", Type: "INT", Nullable: true, Default: ""}, {Name: "nulls_not_allowed", Type: "INT", Nullable: false, Default: "1000"}, {Name: "default2", Type: "INT", Nullable: true, Default: "2"}, {Name: "string_default", Type: "STRING", Nullable: true, Default: "'default_string'"}, {Name: "rowid", Type: "INT", Nullable: false, Default: "unique_rowid()"}, } testutils.SortStructs(expColumns, "Name") testutils.SortStructs(resp.Columns, "Name") if a, e := len(resp.Columns), len(expColumns); a != e { t.Fatalf("# of result columns %d != expected %d (got: %#v)", a, e, resp.Columns) } for i, a := range resp.Columns { e := expColumns[i] if a.String() != e.String() { t.Fatalf("mismatch at index %d: actual %#v != %#v", i, a, e) } } // Verify grants. expGrants := []TableDetailsResponse_Grant{ {User: security.RootUser, Privileges: []string{"ALL"}}, {User: "******", Privileges: []string{"DELETE", "SELECT", "UPDATE"}}, {User: "******", Privileges: []string{"SELECT"}}, } testutils.SortStructs(expGrants, "User") testutils.SortStructs(resp.Grants, "User") if a, e := len(resp.Grants), len(expGrants); a != e { t.Fatalf("# of grant columns %d != expected %d (got: %#v)", a, e, resp.Grants) } for i, a := range resp.Grants { e := expGrants[i] sort.Strings(a.Privileges) sort.Strings(e.Privileges) if a.String() != e.String() { t.Fatalf("mismatch at index %d: actual %#v != %#v", i, a, e) } } // Verify indexes. expIndexes := []TableDetailsResponse_Index{ {Name: "primary", Column: "rowid", Direction: "ASC", Unique: true, Seq: 1}, {Name: "descIdx", Column: "default2", Direction: "DESC", Unique: false, Seq: 1}, } testutils.SortStructs(expIndexes, "Column") testutils.SortStructs(resp.Indexes, "Column") for i, a := range resp.Indexes { e := expIndexes[i] if a.String() != e.String() { t.Fatalf("mismatch at index %d: actual %#v != %#v", i, a, e) } } if a, e := resp.RangeCount, int64(1); a != e { t.Fatalf("# of ranges %d != expected %d", a, e) } }
func TestAdminAPIDatabases(t *testing.T) { defer leaktest.AfterTest(t)() s := StartTestServer(t) defer s.Stop() // Test databases endpoint. const testdb = "test" session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, s.sqlExecutor, nil) query := "CREATE DATABASE " + testdb createRes := s.sqlExecutor.ExecuteStatements(session, query, nil) if createRes.ResultList[0].PErr != nil { t.Fatal(createRes.ResultList[0].PErr) } var resp DatabasesResponse if err := apiGet(s, "databases", &resp); err != nil { t.Fatal(err) } // We should have the system database and the newly created test database. if a, e := len(resp.Databases), 2; a != e { t.Fatalf("length of result %d != expected %d", a, e) } sort.Strings(resp.Databases) if a, e := resp.Databases[0], "system"; a != e { t.Fatalf("database name %s != expected %s", a, e) } if a, e := resp.Databases[1], testdb; a != e { t.Fatalf("database name %s != expected %s", a, e) } // Test database details endpoint. privileges := []string{"SELECT", "UPDATE"} testuser := "******" grantQuery := "GRANT " + strings.Join(privileges, ", ") + " ON DATABASE " + testdb + " TO " + testuser grantRes := s.sqlExecutor.ExecuteStatements(session, grantQuery, nil) if grantRes.ResultList[0].PErr != nil { t.Fatal(grantRes.ResultList[0].PErr) } var details DatabaseDetailsResponse if err := apiGet(s, "databases/"+testdb, &details); err != nil { t.Fatal(err) } if a, e := len(details.Grants), 2; a != e { t.Fatalf("# of grants %d != expected %d", a, e) } for _, grant := range details.Grants { switch grant.User { case security.RootUser: if !reflect.DeepEqual(grant.Privileges, []string{"ALL"}) { t.Fatalf("privileges %v != expected %v", details.Grants[0].Privileges, privileges) } case testuser: sort.Strings(grant.Privileges) if !reflect.DeepEqual(grant.Privileges, privileges) { t.Fatalf("privileges %v != expected %v", grant.Privileges, privileges) } default: t.Fatalf("unknown grant to user %s", grant.User) } } }
func TestAdminAPITableDetailsZone(t *testing.T) { defer leaktest.AfterTest(t)() s, _, _ := serverutils.StartServer(t, base.TestServerArgs{}) defer s.Stopper().Stop() ts := s.(*TestServer) // Create database and table. session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, ts.sqlExecutor, nil) setupQueries := []string{ "CREATE DATABASE test", "CREATE TABLE test.tbl (val STRING)", } for _, q := range setupQueries { res := ts.sqlExecutor.ExecuteStatements(context.Background(), session, q, nil) if res.ResultList[0].Err != nil { t.Fatalf("error executing '%s': %s", q, res.ResultList[0].Err) } } // Function to verify the zone for test.tbl as returned by the Admin API. verifyZone := func(expectedZone config.ZoneConfig, expectedLevel serverpb.ZoneConfigurationLevel) { var resp serverpb.TableDetailsResponse if err := apiGet(s, "databases/test/tables/tbl", &resp); err != nil { t.Fatal(err) } if a, e := &resp.ZoneConfig, &expectedZone; !proto.Equal(a, e) { t.Errorf("actual zone config %v did not match expected value %v", a, e) } if a, e := resp.ZoneConfigLevel, expectedLevel; a != e { t.Errorf("actual ZoneConfigurationLevel %s did not match expected value %s", a, e) } if t.Failed() { t.FailNow() } } // Function to store a zone config for a given object ID. setZone := func(zoneCfg config.ZoneConfig, id sqlbase.ID) { zoneBytes, err := zoneCfg.Marshal() if err != nil { t.Fatal(err) } const query = `INSERT INTO system.zones VALUES($1, $2)` params := parser.NewPlaceholderInfo() params.SetValue(`1`, parser.NewDInt(parser.DInt(id))) params.SetValue(`2`, parser.NewDBytes(parser.DBytes(zoneBytes))) res := ts.sqlExecutor.ExecuteStatements(context.Background(), session, query, params) if res.ResultList[0].Err != nil { t.Fatalf("error executing '%s': %s", query, res.ResultList[0].Err) } } // Verify zone matches cluster default. verifyZone(config.DefaultZoneConfig(), serverpb.ZoneConfigurationLevel_CLUSTER) // Get ID path for table. This will be an array of three IDs, containing the ID of the root namespace, // the database, and the table (in that order). idPath, err := ts.admin.queryDescriptorIDPath(context.Background(), session, []string{"test", "tbl"}) if err != nil { t.Fatal(err) } // Apply zone configuration to database and check again. dbZone := config.ZoneConfig{ RangeMinBytes: 456, } setZone(dbZone, idPath[1]) verifyZone(dbZone, serverpb.ZoneConfigurationLevel_DATABASE) // Apply zone configuration to table and check again. tblZone := config.ZoneConfig{ RangeMinBytes: 789, } setZone(tblZone, idPath[2]) verifyZone(tblZone, serverpb.ZoneConfigurationLevel_TABLE) }