Skip to content

PostgreSQL backup tool with Point-In-Time-Recovery and restore verification

16.6 KB
Raw Blame
package databases

import (
	"errors"
	"net/http"

	"github.com/gin-gonic/gin"
	"github.com/google/uuid"

	postgresql_shared "databasus-backend/internal/features/databases/databases/postgresql/shared"
	users_middleware "databasus-backend/internal/features/users/middleware"
	users_services "databasus-backend/internal/features/users/services"
	workspaces_services "databasus-backend/internal/features/workspaces/services"
)

type DatabaseController struct {
	databaseService  *DatabaseService
	userService      *users_services.UserService
	workspaceService *workspaces_services.WorkspaceService
}

func (c *DatabaseController) RegisterRoutes(router *gin.RouterGroup) {
	router.POST("/databases/create", c.CreateDatabase)
	router.POST("/databases/update", c.UpdateDatabase)
	router.DELETE("/databases/:id", c.DeleteDatabase)
	router.GET("/databases/:id", c.GetDatabase)
	router.GET("/databases", c.GetDatabases)
	router.POST("/databases/:id/test-connection", c.TestDatabaseConnection)
	router.POST("/databases/test-connection-direct", c.TestDatabaseConnectionDirect)
	router.POST("/databases/:id/copy", c.CopyDatabase)
	router.GET("/databases/notifier/:id/is-using", c.IsNotifierUsing)
	router.GET("/databases/notifier/:id/databases-count", c.CountDatabasesByNotifier)
	router.POST("/databases/should-suggest-readonly-user", c.ShouldSuggestReadOnlyUser)
	router.POST("/databases/create-readonly-user", c.CreateReadOnlyUser)
	router.POST("/databases/create-replication-only-user", c.CreateReplicationOnlyUser)
}

func (c *DatabaseController) RegisterPublicRoutes(_ *gin.RouterGroup) {
}

// CreateDatabase
// @Summary Create a new database
// @Description Create a new database configuration in a workspace
// @Tags databases
// @Accept json
// @Produce json
// @Param request body Database true "Database creation data with workspaceId"
// @Success 201 {object} Database
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases/create [post]
func (c *DatabaseController) CreateDatabase(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	var request Database
	if err := ctx.ShouldBindJSON(&request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	if request.WorkspaceID == nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "workspaceId is required"})
		return
	}

	database, err := c.databaseService.CreateDatabase(ctx.Request.Context(), user, *request.WorkspaceID, &request)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusCreated, database)
}

// UpdateDatabase
// @Summary Update a database
// @Description Update an existing database configuration
// @Tags databases
// @Accept json
// @Produce json
// @Param request body Database true "Database update data"
// @Success 200 {object} Database
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases/update [post]
func (c *DatabaseController) UpdateDatabase(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	var request Database
	if err := ctx.ShouldBindJSON(&request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	if err := c.databaseService.UpdateDatabase(ctx.Request.Context(), user, &request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, request)
}

// DeleteDatabase
// @Summary Delete a database
// @Description Delete a database configuration
// @Tags databases
// @Param id path string true "Database ID"
// @Success 204
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases/{id} [delete]
func (c *DatabaseController) DeleteDatabase(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	id, err := uuid.Parse(ctx.Param("id"))
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid database ID"})
		return
	}

	if err := c.databaseService.DeleteDatabase(ctx.Request.Context(), user, id); err != nil {
		_ = ctx.Error(err)
		ctx.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	ctx.Status(http.StatusNoContent)
}

// GetDatabase
// @Summary Get a database
// @Description Get a database configuration by ID
// @Tags databases
// @Produce json
// @Param id path string true "Database ID"
// @Success 200 {object} Database
// @Failure 400
// @Failure 401
// @Router /databases/{id} [get]
func (c *DatabaseController) GetDatabase(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	id, err := uuid.Parse(ctx.Param("id"))
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid database ID"})
		return
	}

	database, err := c.databaseService.GetDatabase(ctx.Request.Context(), user, id)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, database)
}

// GetDatabases
// @Summary Get databases by workspace
// @Description Get all databases for a specific workspace
// @Tags databases
// @Produce json
// @Param workspace_id query string true "Workspace ID"
// @Success 200 {array} Database
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases [get]
func (c *DatabaseController) GetDatabases(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	workspaceIDStr := ctx.Query("workspace_id")
	if workspaceIDStr == "" {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "workspace_id query parameter is required"})
		return
	}

	workspaceID, err := uuid.Parse(workspaceIDStr)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid workspace_id"})
		return
	}

	databases, err := c.databaseService.GetDatabasesByWorkspace(ctx.Request.Context(), user, workspaceID)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, databases)
}

// TestDatabaseConnection
// @Summary Test database connection
// @Description Test connection to an existing database configuration. A physical PostgreSQL source is refused with a machine-readable code, including no_wal_switch_privilege when the backup type is FULL_INCREMENTAL_WAL_STREAM and the credentials cannot execute pg_switch_wal().
// @Tags databases
// @Produce json
// @Param id path string true "Database ID"
// @Success 200
// @Failure 400 {object} map[string]string
// @Failure 401
// @Failure 500
// @Router /databases/{id}/test-connection [post]
func (c *DatabaseController) TestDatabaseConnection(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	id, err := uuid.Parse(ctx.Param("id"))
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid database ID"})
		return
	}

	if err := c.databaseService.TestDatabaseConnection(ctx.Request.Context(), user, id); err != nil {
		respondConnectionTestError(ctx, err)
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"message": "connection successful"})
}

// TestDatabaseConnectionDirect
// @Summary Test database connection directly
// @Description Test connection to a database configuration without saving it. A physical PostgreSQL source is refused with a machine-readable code, including no_wal_switch_privilege when the backup type is FULL_INCREMENTAL_WAL_STREAM and the credentials cannot execute pg_switch_wal().
// @Tags databases
// @Accept json
// @Produce json
// @Param request body Database true "Database configuration to test"
// @Success 200
// @Failure 400 {object} map[string]string
// @Failure 401
// @Failure 403 {object} map[string]string
// @Failure 500 {object} map[string]string
// @Router /databases/test-connection-direct [post]
func (c *DatabaseController) TestDatabaseConnectionDirect(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	var request Database
	if err := ctx.ShouldBindJSON(&request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	if err := c.databaseService.TestDatabaseConnectionDirect(ctx.Request.Context(), user, &request); err != nil {
		respondConnectionTestError(ctx, err)
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"message": "connection successful"})
}

func respondConnectionTestError(ctx *gin.Context, err error) {
	if errors.Is(err, ErrInsufficientPermissionsToTestDatabaseConnection) {
		ctx.JSON(http.StatusForbidden, gin.H{"error": ErrInsufficientPermissionsToTestDatabaseConnection.Error()})
		return
	}

	if errors.Is(err, ErrDatabaseConnectionTargetLookup) {
		ctx.JSON(http.StatusInternalServerError, gin.H{"error": ErrDatabaseConnectionTargetLookup.Error()})
		return
	}

	if errors.Is(err, ErrDatabaseConnectionAuthorizationLookup) {
		ctx.JSON(http.StatusInternalServerError, gin.H{"error": ErrDatabaseConnectionAuthorizationLookup.Error()})
		return
	}

	if connErr, ok := errors.AsType[*postgresql_shared.ConnectionTestError](err); ok {
		ctx.JSON(http.StatusBadRequest, gin.H{"code": connErr.Code})
		return
	}

	ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
}

// IsNotifierUsing
// @Summary Check if notifier is being used
// @Description Check if a notifier is currently being used by any database
// @Tags databases
// @Produce json
// @Param id path string true "Notifier ID"
// @Success 200 {object} map[string]bool
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases/notifier/{id}/is-using [get]
func (c *DatabaseController) IsNotifierUsing(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	id, err := uuid.Parse(ctx.Param("id"))
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid notifier ID"})
		return
	}

	isUsing, err := c.databaseService.IsNotifierUsing(ctx.Request.Context(), user, id)
	if err != nil {
		_ = ctx.Error(err)
		ctx.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"isUsing": isUsing})
}

// CountDatabasesByNotifier
// @Summary Count databases using a notifier
// @Description Get the count of databases that are using a specific notifier
// @Tags databases
// @Produce json
// @Param id path string true "Notifier ID"
// @Success 200 {object} map[string]int
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases/notifier/{id}/databases-count [get]
func (c *DatabaseController) CountDatabasesByNotifier(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	id, err := uuid.Parse(ctx.Param("id"))
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid notifier ID"})
		return
	}

	count, err := c.databaseService.CountDatabasesByNotifier(ctx.Request.Context(), user, id)
	if err != nil {
		_ = ctx.Error(err)
		ctx.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, gin.H{"count": count})
}

// CopyDatabase
// @Summary Copy a database
// @Description Copy an existing database configuration
// @Tags databases
// @Produce json
// @Param id path string true "Database ID"
// @Success 201 {object} Database
// @Failure 400
// @Failure 401
// @Failure 500
// @Router /databases/{id}/copy [post]
func (c *DatabaseController) CopyDatabase(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	id, err := uuid.Parse(ctx.Param("id"))
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": "invalid database ID"})
		return
	}

	copiedDatabase, err := c.databaseService.CopyDatabase(ctx.Request.Context(), user, id)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusCreated, copiedDatabase)
}

// ShouldSuggestReadOnlyUser
// @Summary Check whether a limited backup user should be suggested
// @Description Report whether Databasus should offer to create a read-only (or replication-only) user for these credentials
// @Tags databases
// @Accept json
// @Produce json
// @Security BearerAuth
// @Param request body Database true "Database configuration to check"
// @Success 200 {object} ShouldSuggestReadOnlyUserResponse
// @Failure 400 {object} map[string]string
// @Failure 401 {object} map[string]string
// @Failure 403 {object} map[string]string
// @Router /databases/should-suggest-readonly-user [post]
func (c *DatabaseController) ShouldSuggestReadOnlyUser(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	var request Database
	if err := ctx.ShouldBindJSON(&request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	shouldSuggestReadOnlyUser, privileges, err := c.databaseService.ShouldSuggestReadOnlyUser(
		ctx.Request.Context(),
		user,
		&request,
	)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, ShouldSuggestReadOnlyUserResponse{
		ShouldSuggestReadOnlyUser: shouldSuggestReadOnlyUser,
		Privileges:                privileges,
	})
}

// CreateReadOnlyUser
// @Summary Create read-only database user
// @Description Create a new PostgreSQL user with read-only privileges for backup operations
// @Tags databases
// @Accept json
// @Produce json
// @Security BearerAuth
// @Param request body Database true "Database configuration to create user for"
// @Success 200 {object} CreateReadOnlyUserResponse
// @Failure 400 {object} map[string]string
// @Failure 401 {object} map[string]string
// @Failure 403 {object} map[string]string
// @Router /databases/create-readonly-user [post]
func (c *DatabaseController) CreateReadOnlyUser(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	var request Database
	if err := ctx.ShouldBindJSON(&request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	username, password, err := c.databaseService.CreateReadOnlyUser(ctx.Request.Context(), user, &request)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, CreateReadOnlyUserResponse{
		Username: username,
		Password: password,
	})
}

// CreateReplicationOnlyUser
// @Summary Create replication-only database user (PostgreSQL physical only)
// @Description Provision a fresh PostgreSQL role with LOGIN + REPLICATION (or its cloud equivalent on RDS / Azure / GCP) plus EXECUTE on pg_switch_wal() where the source allows it, and nothing more. isForcedWalRotationAvailable reports whether the created role can force a WAL segment switch, which the FULL_INCREMENTAL_WAL_STREAM backup type requires. Refuses for database types other than POSTGRES_PHYSICAL.
// @Tags databases
// @Accept json
// @Produce json
// @Security BearerAuth
// @Param request body Database true "Database configuration (must be POSTGRES_PHYSICAL)"
// @Success 200 {object} CreateReplicationOnlyUserResponse
// @Failure 400 {object} map[string]string
// @Failure 401 {object} map[string]string
// @Failure 403 {object} map[string]string
// @Router /databases/create-replication-only-user [post]
func (c *DatabaseController) CreateReplicationOnlyUser(ctx *gin.Context) {
	user, ok := users_middleware.GetUserFromContext(ctx)
	if !ok {
		ctx.JSON(http.StatusUnauthorized, gin.H{"error": "User not authenticated"})
		return
	}

	var request Database
	if err := ctx.ShouldBindJSON(&request); err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	createdUser, err := c.databaseService.CreateReplicationOnlyUser(ctx.Request.Context(), user, &request)
	if err != nil {
		ctx.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	ctx.JSON(http.StatusOK, CreateReplicationOnlyUserResponse{
		Username:                     createdUser.Username,
		Password:                     createdUser.Password,
		IsForcedWalRotationAvailable: createdUser.IsForcedWalRotationAvailable,
	})
}