coder · Emyrk · Apr 22, 2022 · Apr 12, 2022 · Apr 18, 2022 · Apr 18, 2022
diff --git a/Makefile b/Makefile
@@ -15,7 +15,7 @@ coderd/database/dump.sql: $(wildcard coderd/database/migrations/*.sql)
 .PHONY: coderd/database/dump.sql
 
 # Generates Go code for querying the database.
-coderd/database/generate: fmt/sql coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
+coderd/database/generate: coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
 	coderd/database/generate.sh
 .PHONY: coderd/database/generate
 
@@ -34,22 +34,10 @@ else
 endif
 .PHONY: fmt/prettier
 
-fmt/sql: $(wildcard coderd/database/queries/*.sql)
-	for fi in coderd/database/queries/*.sql; do \
-		npx sql-formatter \
-			--language postgresql \
-			--lines-between-queries 2 \
-			--tab-indent \
-			$$fi \
-			--output $$fi; \
-	done
-
-	sed -i 's/@ /@/g' ./coderd/database/queries/*.sql
-
 fmt/terraform: $(wildcard *.tf)
 	terraform fmt -recursive
 
-fmt: fmt/prettier fmt/sql fmt/terraform
+fmt: fmt/prettier fmt/terraform
 .PHONY: fmt
 
 gen: coderd/database/generate peerbroker/proto provisionersdk/proto provisionerd/proto apitypings/generate

diff --git a/coderd/coderd.go b/coderd/coderd.go
@@ -35,13 +35,17 @@ type Options struct {
 	Pubsub    database.Pubsub
 
 	AgentConnectionUpdateFrequency time.Duration
-	AWSCertificates                awsidentity.Certificates
-	AzureCertificates              x509.VerifyOptions
-	GoogleTokenValidator           *idtoken.Validator
-	ICEServers                     []webrtc.ICEServer
-	SecureAuthCookie               bool
-	SSHKeygenAlgorithm             gitsshkey.Algorithm
-	TURNServer                     *turnconn.Server
+	// APIRateLimit is the minutely throughput rate limit per user or ip.
+	// Setting a rate limit <0 will disable the rate limiter across the entire
+	// app. Specific routes may have their own limiters.
+	APIRateLimit         int
+	AWSCertificates      awsidentity.Certificates
+	AzureCertificates    x509.VerifyOptions
+	GoogleTokenValidator *idtoken.Validator
+	ICEServers           []webrtc.ICEServer
+	SecureAuthCookie     bool
+	SSHKeygenAlgorithm   gitsshkey.Algorithm
+	TURNServer           *turnconn.Server
 }
 
 // New constructs the Coder API into an HTTP handler.
@@ -52,6 +56,9 @@ func New(options *Options) (http.Handler, func()) {
 	if options.AgentConnectionUpdateFrequency == 0 {
 		options.AgentConnectionUpdateFrequency = 3 * time.Second
 	}
+	if options.APIRateLimit == 0 {
+		options.APIRateLimit = 512
+	}
 	api := &api{
 		Options: options,
 	}
@@ -61,7 +68,7 @@ func New(options *Options) (http.Handler, func()) {
 		r.Use(
 			chitrace.Middleware(),
 			// Specific routes can specify smaller limits.
-			httpmw.RateLimitPerMinute(512),
+			httpmw.RateLimitPerMinute(options.APIRateLimit),
 			debugLogRequest(api.Logger),
 		)
 		r.Get("/", func(w http.ResponseWriter, r *http.Request) {

diff --git a/coderd/coderdtest/coderdtest.go b/coderd/coderdtest/coderdtest.go
@@ -55,6 +55,7 @@ type Options struct {
 	AzureCertificates    x509.VerifyOptions
 	GoogleTokenValidator *idtoken.Validator
 	SSHKeygenAlgorithm   gitsshkey.Algorithm
+	APIRateLimit         int
 }
 
 // New constructs an in-memory coderd instance and returns
@@ -125,6 +126,7 @@ func New(t *testing.T, options *Options) *codersdk.Client {
 		GoogleTokenValidator: options.GoogleTokenValidator,
 		SSHKeygenAlgorithm:   options.SSHKeygenAlgorithm,
 		TURNServer:           turnServer,
+		APIRateLimit:         options.APIRateLimit,
 	})
 	t.Cleanup(func() {
 		cancelFunc()

diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -3,6 +3,7 @@ package databasefake
 import (
 	"context"
 	"database/sql"
+	"sort"
 	"strings"
 	"sync"
 
@@ -164,11 +165,72 @@ func (q *fakeQuerier) GetUserCount(_ context.Context) (int64, error) {
 	return int64(len(q.users)), nil
 }
 
-func (q *fakeQuerier) GetUsers(_ context.Context) ([]database.User, error) {
+func (q *fakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams) ([]database.User, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
-	return q.users, nil
+	users := q.users
+	// Database orders by created_at
+	sort.Slice(users, func(i, j int) bool {
+		if users[i].CreatedAt.Equal(users[j].CreatedAt) {
+			// Technically the postgres database also orders by uuid. So match
+			// that behavior
+			return users[i].ID.String() < users[j].ID.String()
+		}
+		return users[i].CreatedAt.Before(users[j].CreatedAt)
+	})
+
+	if params.AfterUser != uuid.Nil {
+		found := false
+		for i := range users {
+			if users[i].ID == params.AfterUser {
+				// We want to return all users after index i.
+				if i+1 >= len(users) {
+					return []database.User{}, nil
+				}
+				users = users[i+1:]
+				found = true
+				break
+			}
+		}
+
+		// If no users after the time, then we return an empty list.
+		if !found {
+			return []database.User{}, nil
+		}
+	}
+
+	if params.Search != "" {
+		tmp := make([]database.User, 0, len(users))
+		for i, user := range users {
+			if strings.Contains(user.Email, params.Search) {
+				tmp = append(tmp, users[i])
+			} else if strings.Contains(user.Username, params.Search) {
+				tmp = append(tmp, users[i])
+			} else if strings.Contains(user.Name, params.Search) {
+				tmp = append(tmp, users[i])
+			}
+		}
+		users = tmp
+	}
+
+	if params.OffsetOpt > 0 {
+		if int(params.OffsetOpt) > len(users)-1 {
+			return []database.User{}, nil
+		}
+		users = users[params.OffsetOpt:]
+	}
+
+	if params.LimitOpt > 0 {
+		if int(params.LimitOpt) > len(users) {
+			params.LimitOpt = int32(len(users))
+		}
+		users = users[:params.LimitOpt]
+	}
+	tmp := make([]database.User, len(users))
+	copy(tmp, users)
+
+	return tmp, nil
 }
 
 func (q *fakeQuerier) GetWorkspacesByTemplateID(_ context.Context, arg database.GetWorkspacesByTemplateIDParams) ([]database.Workspace, error) {

diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/users.sql b/coderd/database/queries/users.sql
@@ -56,4 +56,42 @@ WHERE
 SELECT
 	*
 FROM
-	users;
+	users
+WHERE
+	CASE
+		-- This allows using the last element on a page as effectively a cursor.
+		-- This is an important option for scripts that need to paginate without
+		-- duplicating or missing data.
+		WHEN @after_user :: uuid != '00000000-00000000-00000000-00000000' THEN (
+		    	-- The pagination cursor is the last user of the previous page.
+		    	-- The query is ordered by the created_at field, so select all
+		    	-- users after the cursor. We also want to include any users
+		    	-- that share the created_at (super rare).
+				created_at >= (
+					SELECT
+						created_at
+					FROM
+						users
+					WHERE
+						id = @after_user
+				)
+				-- Omit the cursor from the final.
+				AND id != @after_user
+			)
+			ELSE true
+	END
+	AND CASE
+		WHEN @search :: text != '' THEN (
+			email LIKE concat('%', @search, '%')
+			OR username LIKE concat('%', @search, '%')
+			OR 'name' LIKE concat('%', @search, '%')
+		)
+		ELSE true
+	END
+ORDER BY
+    -- Deterministic and consistent ordering of all users, even if they share
+    -- a timestamp. This is to ensure consistent pagination.
+	(created_at, id) ASC OFFSET @offset_opt
+LIMIT
+	-- A null limit means "no limit", so -1 means return all
+	NULLIF(@limit_opt :: int, -1);
diff --git a/coderd/httpmw/ratelimit.go b/coderd/httpmw/ratelimit.go
@@ -13,6 +13,12 @@ import (
 // RateLimitPerMinute returns a handler that limits requests per-minute based
 // on IP, endpoint, and user ID (if available).
 func RateLimitPerMinute(count int) func(http.Handler) http.Handler {
+	// -1 is no rate limit
+	if count <= 0 {
+		return func(handler http.Handler) http.Handler {
+			return handler
+		}
+	}
 	return httprate.Limit(
 		count,
 		1*time.Minute,