mirror of
https://github.com/1Panel-dev/1Panel.git
synced 2026-11-20 00:01:06 +00:00
* feat(postgresql): add user and database authorization management * fix(postgresql): reuse existing roles during app recovery
383 lines
11 KiB
Go
383 lines
11 KiB
Go
package service
|
|
|
|
import (
|
|
"context"
|
|
"encoding/json"
|
|
"errors"
|
|
"fmt"
|
|
"strings"
|
|
|
|
"github.com/1Panel-dev/1Panel/agent/app/dto"
|
|
"github.com/1Panel-dev/1Panel/agent/app/model"
|
|
"github.com/1Panel-dev/1Panel/agent/app/repo"
|
|
"github.com/1Panel-dev/1Panel/agent/buserr"
|
|
"github.com/1Panel-dev/1Panel/agent/constant"
|
|
"github.com/1Panel-dev/1Panel/agent/utils/encrypt"
|
|
"github.com/1Panel-dev/1Panel/agent/utils/postgresql/client"
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
func (u *PostgresqlService) ListUsers(req dto.PostgresqlUserSearch) ([]dto.PostgresqlUser, error) {
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer cli.Close()
|
|
users, err := client.ListUsers(cli)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
metadata, err := databaseUserRepo.List(repo.WithByType("postgresql"), databaseUserRepo.WithByDatabase(req.Database))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
legacy, err := postgresqlRepo.List(postgresqlRepo.WithByPostgresqlName(req.Database))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
result := make([]dto.PostgresqlUser, 0, len(users))
|
|
for _, user := range users {
|
|
item := dto.PostgresqlUser{Username: user.Username, SuperUser: user.SuperUser, Protected: user.Protected, Databases: user.Databases}
|
|
passwords := make(map[string]struct{})
|
|
for _, db := range legacy {
|
|
if db.Username == user.Username && db.Password != "" {
|
|
passwords[db.Password] = struct{}{}
|
|
}
|
|
}
|
|
if len(passwords) == 1 {
|
|
for password := range passwords {
|
|
item.Password = password
|
|
}
|
|
}
|
|
for _, meta := range metadata {
|
|
if meta.Username == user.Username {
|
|
item.Password, item.Description = meta.Password, meta.Description
|
|
break
|
|
}
|
|
}
|
|
result = append(result, item)
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
func (u *PostgresqlService) CreateUser(req dto.PostgresqlUserCreate) error {
|
|
if strings.HasPrefix(req.Username, "pg_") || req.Username == "postgres" {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
if req.Username == "" || len(req.Username) > 63 || req.Password == "" || strings.ContainsAny(req.Username+req.Password, "\x00") {
|
|
return buserr.New("ErrInvalidParams")
|
|
}
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer cli.Close()
|
|
if err := cli.CreateUser(client.CreateInfo{Username: req.Username, Password: req.Password, SuperUser: req.SuperUser, Timeout: 300}, false); err != nil {
|
|
return err
|
|
}
|
|
return saveDatabaseUserCredential("postgresql", req.Database, req.Username, "", req.Password, req.Description)
|
|
}
|
|
|
|
func (u *PostgresqlService) UpdateUser(req dto.PostgresqlUserUpdate) error {
|
|
user, err := u.loadPostgresqlUser(req.Database, req.Username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if user.Protected {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer cli.Close()
|
|
if err := cli.ChangePrivileges(client.Privileges{Username: req.Username, SuperUser: req.SuperUser, Timeout: 300}); err != nil {
|
|
return err
|
|
}
|
|
dbs, err := postgresqlRepo.List(postgresqlRepo.WithByPostgresqlName(req.Database))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for _, db := range dbs {
|
|
if db.Username == req.Username {
|
|
if err := postgresqlRepo.Update(db.ID, map[string]interface{}{"super_user": req.SuperUser}); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
return saveDatabaseUserCredential("postgresql", req.Database, req.Username, "", user.Password, req.Description)
|
|
}
|
|
|
|
func (u *PostgresqlService) ChangeUserPassword(req dto.PostgresqlUserPassword) error {
|
|
user, err := u.loadPostgresqlUser(req.Database, req.Username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if user.Protected {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
if req.Password == "" || strings.ContainsRune(req.Password, 0) {
|
|
return buserr.New("ErrInvalidParams")
|
|
}
|
|
targets, err := loadPostgresqlUserApps(req.Database, req.Username, "")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
dbs, err := postgresqlRepo.List(postgresqlRepo.WithByPostgresqlName(req.Database))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
password, err := encrypt.StringEncrypt(req.Password)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer cli.Close()
|
|
if err := cli.ChangePassword(client.PasswordChangeInfo{Username: req.Username, Password: req.Password, Timeout: 300}); err != nil {
|
|
return err
|
|
}
|
|
if err := saveDatabaseUserCredential("postgresql", req.Database, req.Username, "", req.Password, user.Description); err != nil {
|
|
return err
|
|
}
|
|
for _, db := range dbs {
|
|
if db.Username == req.Username {
|
|
if err := postgresqlRepo.Update(db.ID, map[string]interface{}{"password": password}); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
var failures []string
|
|
for _, app := range targets {
|
|
if err := updateInstallInfoInDB(app.App.Key, app.Name, "user-password", req.Password); err != nil {
|
|
failures = append(failures, fmt.Sprintf("%s: %v", app.Name, err))
|
|
}
|
|
}
|
|
if len(failures) != 0 {
|
|
return buserr.WithDetail("ErrPgUserPasswordSync", strings.Join(failures, "; "), nil)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (u *PostgresqlService) SaveUserPassword(req dto.PostgresqlUserPassword) error {
|
|
if req.Password == "" || strings.ContainsRune(req.Password, 0) {
|
|
return buserr.New("ErrInvalidParams")
|
|
}
|
|
user, err := u.loadPostgresqlUser(req.Database, req.Username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if user.Protected {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
metadata, err := databaseUserRepo.Get(repo.WithByType("postgresql"), databaseUserRepo.WithByDatabase(req.Database), databaseUserRepo.WithByUser(req.Username, ""))
|
|
if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
|
|
return err
|
|
}
|
|
metadata.Type, metadata.Database, metadata.Username, metadata.Host = "postgresql", req.Database, req.Username, ""
|
|
metadata.Password, metadata.Description, metadata.IsDelete = req.Password, user.Description, false
|
|
return postgresqlRepo.SaveUserPassword(&metadata)
|
|
}
|
|
|
|
func (u *PostgresqlService) DeleteUser(req dto.PostgresqlUserDelete) error {
|
|
user, err := u.loadPostgresqlUser(req.Database, req.Username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if user.Protected {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
if err := checkPostgresqlUserUsage(req.Database, req.Username, ""); err != nil {
|
|
return err
|
|
}
|
|
if len(user.Databases) != 0 {
|
|
return buserr.New("ErrPgUserBound")
|
|
}
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer cli.Close()
|
|
if err := cli.Delete(client.DeleteInfo{Username: req.Username, Timeout: 300}); err != nil {
|
|
return err
|
|
}
|
|
dbs, err := postgresqlRepo.List(postgresqlRepo.WithByPostgresqlName(req.Database))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for _, db := range dbs {
|
|
if db.Username == req.Username {
|
|
if err := postgresqlRepo.Update(db.ID, map[string]interface{}{"username": "", "password": "", "super_user": false}); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
return databaseUserRepo.Delete(repo.WithByType("postgresql"), databaseUserRepo.WithByDatabase(req.Database), databaseUserRepo.WithByUser(req.Username, ""))
|
|
}
|
|
|
|
func (u *PostgresqlService) GrantUser(req dto.PostgresqlUserGrant) error {
|
|
user, err := u.loadPostgresqlUser(req.Database, req.Username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if user.Protected {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
if user.SuperUser {
|
|
return buserr.New("ErrPgSuperUserGrant")
|
|
}
|
|
db, err := postgresqlRepo.Get(postgresqlRepo.WithByPostgresqlName(req.Database), repo.WithByName(req.Name))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer cli.Close()
|
|
if err := client.GrantUser(cli, req.Name, req.Username, false); err != nil {
|
|
return err
|
|
}
|
|
if db.Username == "" {
|
|
password, err := encrypt.StringEncrypt(user.Password)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return postgresqlRepo.Update(db.ID, map[string]interface{}{"username": req.Username, "password": password, "super_user": false})
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (u *PostgresqlService) RevokeUser(req dto.PostgresqlUserGrant) error {
|
|
user, err := u.loadPostgresqlUser(req.Database, req.Username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if user.Protected {
|
|
return buserr.New("ErrPgUserProtected")
|
|
}
|
|
if user.SuperUser {
|
|
return buserr.New("ErrPgSuperUserGrant")
|
|
}
|
|
db, err := postgresqlRepo.Get(postgresqlRepo.WithByPostgresqlName(req.Database), repo.WithByName(req.Name))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if err := checkPostgresqlUserUsage(req.Database, req.Username, req.Name); err != nil {
|
|
return err
|
|
}
|
|
cli, err := LoadPostgresqlClientByFrom(req.Database)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer cli.Close()
|
|
if err := saveDatabaseUserCredential("postgresql", req.Database, req.Username, "", user.Password, user.Description); err != nil {
|
|
return err
|
|
}
|
|
if err := client.GrantUser(cli, req.Name, req.Username, true); err != nil {
|
|
return err
|
|
}
|
|
if db.Username == req.Username {
|
|
return postgresqlRepo.Update(db.ID, map[string]interface{}{"username": "", "password": "", "super_user": false})
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (u *PostgresqlService) loadPostgresqlUser(database, username string) (dto.PostgresqlUser, error) {
|
|
users, err := u.ListUsers(dto.PostgresqlUserSearch{Database: database})
|
|
if err != nil {
|
|
return dto.PostgresqlUser{}, err
|
|
}
|
|
for _, user := range users {
|
|
if user.Username == username {
|
|
return user, nil
|
|
}
|
|
}
|
|
return dto.PostgresqlUser{}, buserr.New("ErrRecordNotFound")
|
|
}
|
|
|
|
func loadPostgresqlUserApps(database, username, dbName string) ([]model.AppInstall, error) {
|
|
instance, err := databaseRepo.Get(repo.WithByName(database))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
dbs, err := postgresqlRepo.List(postgresqlRepo.WithByPostgresqlName(database))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
ids := make([]uint, 0, len(dbs))
|
|
for _, db := range dbs {
|
|
if dbName == "" || db.Name == dbName {
|
|
ids = append(ids, db.ID)
|
|
}
|
|
}
|
|
if len(ids) == 0 {
|
|
return nil, nil
|
|
}
|
|
opts := []repo.DBOption{appInstallResourceRepo.WithResourceIds(ids), appRepo.WithKey(instance.Type)}
|
|
if instance.From == "local" {
|
|
app, err := appInstallRepo.LoadBaseInfo(instance.Type, database)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
opts = append(opts, appInstallResourceRepo.WithLinkId(app.ID))
|
|
}
|
|
resources, err := appInstallResourceRepo.GetBy(opts...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
appIDs := make([]uint, 0, len(resources))
|
|
for _, resource := range resources {
|
|
appIDs = append(appIDs, resource.AppInstallId)
|
|
}
|
|
if len(appIDs) == 0 {
|
|
return nil, nil
|
|
}
|
|
apps, err := appInstallRepo.ListBy(context.Background(), repo.WithByIDs(appIDs))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
result := make([]model.AppInstall, 0, len(apps))
|
|
for _, app := range apps {
|
|
var env map[string]interface{}
|
|
if err := json.Unmarshal([]byte(app.Env), &env); err != nil {
|
|
return nil, err
|
|
}
|
|
if env["PANEL_DB_USER"] == username {
|
|
result = append(result, app)
|
|
}
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
func checkPostgresqlUserUsage(database, username, dbName string) error {
|
|
apps, err := loadPostgresqlUserApps(database, username, dbName)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if len(apps) != 0 {
|
|
return buserr.New("ErrPgUserUsed")
|
|
}
|
|
dbs, err := postgresqlRepo.List(postgresqlRepo.WithByPostgresqlName(database))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
websites, err := websiteRepo.List(websiteRepo.WithDBTypes([]string{constant.AppPostgresql, constant.AppPostgresqlCluster}))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for _, db := range dbs {
|
|
if db.Username != username || (dbName != "" && db.Name != dbName) {
|
|
continue
|
|
}
|
|
for _, website := range websites {
|
|
if website.DbID == db.ID {
|
|
return buserr.New("ErrPgUserUsed")
|
|
}
|
|
}
|
|
}
|
|
return nil
|
|
}
|