PostgreSQL backup tool with Point-In-Time-Recovery and restore verification
TypeScript backupbackupsdatabasedatabase-backupdevopsdockergolangkubernetesmariadbmongodbmysqlpgpostgrespostgresqls3self-hostedsystem-administrationtoolsweb-ui
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,
})
}