Example #1
0
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
}
Example #2
0
// 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
}
Example #3
0
// 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
}
Example #4
0
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
}
Example #5
0
// 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
}
Example #6
0
// 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
}
Example #7
0
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
}
Example #8
0
// 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
}
Example #9
0
// 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
}