github.com/machinefi/w3bstream@v1.6.5-rc9.0.20240426031326-b8c7c4876e72/pkg/depends/kit/sqlx/driver/postgres/privilege_util.go (about)

     1  package postgres
     2  
     3  import (
     4  	"fmt"
     5  
     6  	"github.com/pkg/errors"
     7  
     8  	"github.com/machinefi/w3bstream/pkg/depends/kit/sqlx"
     9  	"github.com/machinefi/w3bstream/pkg/depends/kit/sqlx/builder"
    10  	"github.com/machinefi/w3bstream/pkg/depends/x/ptrx"
    11  )
    12  
    13  func CreateUserIfNotExists(d sqlx.DBExecutor, usename, passwd string) (error, bool) {
    14  	exists := ptrx.Ptr(false)
    15  	err := d.QueryAndScan(
    16  		builder.Expr(fmt.Sprintf("SELECT 1 FROM pg_catalog.pg_user AS u WHERE u.usename='%s'", usename)),
    17  		exists,
    18  	)
    19  	if err != nil && !sqlx.DBErr(err).IsNotFound() {
    20  		return err, false
    21  	}
    22  	if *exists {
    23  		// DO NOT update password otherwise connection will be lost, if this is current user
    24  		return nil, false
    25  	}
    26  
    27  	_, err = d.Exec(builder.Expr("CREATE USER " + usename + " WITH PASSWORD '" + passwd + "'"))
    28  	if err != nil {
    29  		return err, false
    30  	}
    31  	return nil, true
    32  }
    33  
    34  func DropUser(d sqlx.DBExecutor, usename string) error {
    35  	currentUser, err := CurrentUser(d)
    36  	if currentUser == usename {
    37  		return ErrDropCurrentUser
    38  	}
    39  
    40  	_, err = d.Exec(builder.Expr("DROP USER " + usename))
    41  	return err
    42  }
    43  
    44  func CurrentUser(d sqlx.DBExecutor) (string, error) {
    45  	usename := ""
    46  	err := d.QueryAndScan(builder.Expr("SELECT USER"), &usename)
    47  	if err != nil {
    48  		return "", err
    49  	}
    50  	return usename, nil
    51  }
    52  
    53  func GrantAllPrivileges(d sqlx.DBExecutor, on PrivilegeDomain, onName, usename string) error {
    54  	_, err := d.Exec(builder.Expr(fmt.Sprintf(
    55  		"GRANT ALL PRIVILEGES ON %s %s TO %s",
    56  		on, onName, usename,
    57  	)))
    58  	return err
    59  }
    60  
    61  func AlterUserConnectionLimit(d sqlx.DBExecutor, usename string, lmt int) error {
    62  	_, err := d.Exec(builder.Expr(fmt.Sprintf(
    63  		"ALTER USER %s CONNECTION LIMIT %d", usename, lmt,
    64  	)))
    65  	return err
    66  }
    67  
    68  var (
    69  	ErrDropCurrentUser = errors.New("cannot drop current user")
    70  )
    71  
    72  type PrivilegeDomain string
    73  
    74  const (
    75  	PrivilegeDomainDatabase PrivilegeDomain = "DATABASE"
    76  	PrivilegeDomainTABLE    PrivilegeDomain = "TABLE"
    77  )