func (p *PG) DropUser(dbuser string) (err error) {
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#DropUser(%s) %s ! %s", p.IP, dbuser, p.URI, err))
return
}
defer db.Close()
exists, err := p.UserExists(dbuser)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#DropUser(%s) ! %s", p.IP, dbuser, err))
return
}
if !exists {
log.Error(fmt.Sprintf("pg.PG<%s>#DropUser(%s) User %s already does not exist.", p.IP, dbuser, err))
return
}
// TODO: How do we drop a database in bdr properly?
sq := fmt.Sprintf(`DROP USER %s`, dbuser)
log.Trace(fmt.Sprintf(`p.PG#DropDatabase(%s) %s DROP > %s`, dbuser, p.IP, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("p.PG#DropDatabase(%s) DROP %s ! %s", dbuser, p.IP, err))
return
}
return
}
// Check if the given PostgreSQL Database Exists on the host.
func (p *PG) DatabaseExists(dbname string) (exists bool, err error) {
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#DatabaseExists(%s) %s ! %s", p.IP, dbname, p.URI, err))
return
}
defer db.Close()
type name struct {
Name string `db:"name"`
}
var n name
sq := fmt.Sprintf(`SELECT datname AS name FROM pg_database WHERE datname='%s' LIMIT 1;`, dbname)
err = db.Get(&n, sq)
if err != nil {
if err == sql.ErrNoRows {
exists = false
err = nil
} else {
log.Error(fmt.Sprintf(`pg.PG<%s>#DatabaseExists(%s) ! %s`, p.IP, dbname, err))
return
}
}
if n.Name != "" {
exists = true
} else {
exists = false
}
return
}
// Connect to the host's database and return database connection object if successful
func (p *PG) Connect() (db *sqlx.DB, err error) {
db, err = sqlx.Connect(`postgres`, p.URI)
if err != nil {
log.Error(fmt.Sprintf(`pg.PG<%s>#Connect() %s ! %s`, p.IP, p.URI, err))
return db, err
}
return db, nil
}
func (p *PG) DisableDatabase(dbname string) (err error) {
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#DisableDatabase(%s) %s ! %s", p.IP, dbname, p.URI, err))
return
}
defer db.Close()
sq := fmt.Sprintf(`SELECT rdpg.bdr_disable_database('%s');`, dbname)
log.Trace(fmt.Sprintf(`pg.PG<%s>#DisableDatabase(%s) DISABLE > %s`, p.IP, dbname, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("p.PG<%s>#DisableDatabase(%s) DISABLE ! %s", p.IP, dbname, err))
}
return
}
// Create given extensions on a single target host.
func (p *PG) CreateExtensions(dbname string, exts []string) (err error) {
p.Set(`database`, dbname)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateExtensions(%s) %s ! %s", p.IP, dbname, p.URI, err))
return
}
for _, ext := range exts {
sq := fmt.Sprintf(`CREATE EXTENSION IF NOT EXISTS %s;`, ext)
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateExtensions() > %s`, p.IP, sq))
_, err = db.Exec(sq)
if err != nil {
db.Close()
log.Error(fmt.Sprintf("pg.PG<%s>#CreateExtensions() %s ! %s", p.IP, ext, err))
return
}
}
db.Close()
return
}
// Create a given user on a single target host.
func (p *PG) CreateUser(dbuser, dbpass string) (err error) {
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateUser(%s) %s ! %s", p.IP, dbuser, p.URI, err))
return
}
defer db.Close()
exists, err := p.UserExists(dbuser)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateUser(%s) ! %s", p.IP, dbuser, err))
return
}
if exists {
log.Debug(fmt.Sprintf(`User %s already exists, skipping.`, dbuser))
return nil
}
sq := fmt.Sprintf(`CREATE USER %s;`, dbuser)
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateUser(%s) > %s`, p.IP, dbuser, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateUser(%s) ! %s", p.IP, dbuser, err))
db.Close()
return err
}
sq = fmt.Sprintf(`ALTER USER %s ENCRYPTED PASSWORD '%s';`, dbuser, dbpass)
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateUser(%s)`, p.IP, dbuser))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`pg.PG<%s>#CreateUser(%s) ! %s`, p.IP, dbuser, err))
return
}
return
}
func (p *PG) StopReplication(dbname string) (err error) {
// TODO Finish this function
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#DropDatabase(%s) %s ! %s", p.IP, dbname, p.URI, err))
return
}
// sq := fmt.Sprintf(SELECT slot_name FROM pg_replication_slots WHERE database='%s',dbname);
// pg_recvlogical --drop-slot
defer db.Close()
return
}
// Create a given user on a single target host.
func (p *PG) UserGrantPrivileges(dbuser string, priviliges []string) (err error) {
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#UserGrantPrivileges(%s) %s ! %s", p.IP, dbuser, p.URI, err))
return
}
defer db.Close()
for _, priv := range priviliges {
sq := fmt.Sprintf(`ALTER USER %s GRANT %s;`, dbuser, priv)
log.Trace(fmt.Sprintf(`pg.PG<%s>#UserGrantPrivileges(%s) > %s`, p.IP, dbuser, sq))
result, err := db.Exec(sq)
rows, _ := result.RowsAffected()
if rows > 0 {
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateUser(%s) Successfully Created.`, p.IP, dbuser))
}
if err != nil {
log.Error(fmt.Sprintf(`pg.PG<%s>#CreateUser(%s) ! %s`, p.IP, dbuser, err))
return err
}
}
return nil
}
// Create a given database owned by user on a single target host.
func (p *PG) CreateDatabase(dbname, dbuser string) (err error) {
p.Set(`database`, `postgres`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateDatabase(%s,%s) %s ! %s", p.IP, dbname, dbuser, p.URI, err))
return
}
defer db.Close()
exists, err := p.UserExists(dbuser)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateDatabase(%s,%s) ! %s", p.IP, dbname, dbuser, err))
return
}
if !exists {
err = fmt.Errorf(`User does not exist, ensure that postgres user '%s' exists first.`, dbuser)
log.Error(fmt.Sprintf("pg.PG<%s>#CreateDatabase(%s,%s) ! %s", p.IP, dbname, dbuser, err))
return
}
sq := fmt.Sprintf(`CREATE DATABASE %s WITH OWNER %s TEMPLATE template0 ENCODING 'UTF8'`, dbname, dbuser)
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateDatabase(%s,%s) > %s`, p.IP, dbname, dbuser, sq))
_, err = db.Query(sq)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateDatabase(%s,%s) ! %s", p.IP, dbname, dbuser, err))
return
}
sq = fmt.Sprintf(`REVOKE ALL ON DATABASE "%s" FROM public`, dbname)
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateDatabase(%s,%s) > %s`, p.IP, dbname, dbuser, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateDatabase(%s,%s) ! %s", p.IP, dbname, dbuser, err))
}
sq = fmt.Sprintf(`GRANT ALL PRIVILEGES ON DATABASE %s TO %s`, dbname, dbuser)
log.Trace(fmt.Sprintf(`pg.PG<%s>#CreateDatabase(%s,%s) > %s`, p.IP, dbname, dbuser, sq))
_, err = db.Query(sq)
if err != nil {
log.Error(fmt.Sprintf("pg.PG<%s>#CreateDatabase(%s,%s) ! %s", p.IP, dbname, dbuser, err))
return
}
return nil
}