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
}
// 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
}
func (p *PG) BDRGroupCreate(group, dbname string) (err error) {
p.Set(`database`, dbname)
db, err := p.Connect()
if err != nil {
return
}
defer db.Close()
sq := fmt.Sprintf(`SELECT bdr.bdr_group_create( local_node_name := '%s',
node_external_dsn := 'host=%s port=%s user=%s dbname=%s'); `,
group, p.IP, p.Port, p.User, dbname,
)
log.Trace(fmt.Sprintf(`p.PG#CreateReplicationGroup(%s) %s > %s`, dbname, p.IP, sq))
_, err = db.Exec(sq)
if err == nil {
sq = `SELECT bdr.bdr_node_join_wait_for_ready();`
log.Trace(fmt.Sprintf(`p.PG#CreateReplicationGroup(%s) %s > %s`, dbname, p.IP, sq))
_, err = db.Exec(sq)
}
db.Close()
return
}
// Create and return a new PG using default parameters
func NewPG(host, port, user, database, pass string) (p *PG) {
p = &PG{IP: host, Port: port, User: user, Database: database, Pass: pass}
p.ConnectTimeout = `5` // Default connection time out.
p.SSLMode = `disable` // Default disable SSL Mode, can be overwritten using Set()
p.pgURI()
p.pgDSN()
log.Trace(fmt.Sprintf(`pg.PG#NewPG() New PG struct: %+v`, p))
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
}
// 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
}
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
}