func Migrate() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'config'`)
if err != nil {
log.Error("Select tables names failed: ", err.Error())
return err
}
defer rows.Close()
if rows.Next() {
log.Info("config table already set up")
return nil
}
rows, err = db.Query(
`CREATE TABLE config (
key varchar(255) PRIMARY KEY,
value varchar(255)
);`)
if err != nil {
log.Errorf("Unable to create config table: %s", err)
return err
}
rows.Close()
return nil
}
func Migrate() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'histories'`)
if err != nil {
log.Error(err.Error())
panic(err)
}
defer rows.Close()
if rows.Next() {
log.Info("Histories table already set up")
return nil
}
rows, err = db.Query(
`CREATE TABLE histories (
id varchar(36) PRIMARY KEY,
userid varchar(36) NOT NULL DEFAULT '',
usermail varchar(36) NOT NULL DEFAULT '',
userfirstname varchar(36) NOT NULL DEFAULT '',
userlastname varchar(36) NOT NULL DEFAULT '',
connectionid varchar(36) NOT NULL DEFAULT '',
startdate varchar(36) NOT NULL DEFAULT '',
enddate varchar(36) NOT NULL DEFAULT ''
);`)
if err != nil {
log.Errorf("Unable to create histories table: %s", err)
return err
}
rows.Close()
return nil
}
func createWindowsUsersTable() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'windows_users'`)
if err != nil {
return err
}
defer rows.Close()
if rows.Next() {
return nil
}
rows, err = db.Query(
`CREATE TABLE windows_users (
id serial PRIMARY KEY,
sam varchar(35),
password varchar(255),
domain varchar(255)
);`)
if err != nil {
return err
}
rows.Close()
return nil
}
func createUsersTable() (bool, error) {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'users'`)
if err != nil {
return false, err
}
defer rows.Close()
if rows.Next() {
return false, nil
}
rows, err = db.Query(
`CREATE TABLE users (
id varchar(36) PRIMARY KEY,
first_name varchar(36) NOT NULL DEFAULT '',
last_name varchar(36) NOT NULL DEFAULT '',
email varchar(36) NOT NULL DEFAULT '' UNIQUE,
password varchar(60) NOT NULL DEFAULT '',
signup_date timestamp with time zone NOT NULL DEFAULT current_timestamp,
is_admin boolean,
activated boolean
);`)
if err != nil {
return false, err
}
rows.Close()
return true, nil
}
func createUsersWindowsUserTable() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'users_windows_user'`)
if err != nil {
return err
}
defer rows.Close()
if rows.Next() {
return nil
}
rows, err = db.Query(
`CREATE TABLE users_windows_user (
user_id varchar(36)
REFERENCES users(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
windows_user_id serial
REFERENCES windows_users(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (user_id)
);`)
if err != nil {
return err
}
rows.Close()
return nil
}
func Migrate() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'apps'`)
if err != nil {
log.Error("Select tables names failed: ", err.Error())
return err
}
defer rows.Close()
if rows.Next() {
log.Info("apps table already set up")
return nil
}
rows, err = db.Query(
`CREATE TABLE apps (
id SERIAL PRIMARY KEY,
collection_name varchar(36),
alias varchar(36) UNIQUE,
display_name varchar(36),
file_path varchar(255),
icon_content bytea
);`)
if err != nil {
log.Errorf("Unable to create apps table: %s", err)
return err
}
rows.Close()
return nil
}
func Migrate() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'machines'`)
if err != nil {
log.Error(err.Error())
panic(err)
}
defer rows.Close()
if rows.Next() {
log.Info("Machines table already set up")
return nil
}
rows, err = db.Query(
`CREATE TABLE machines (
id varchar(60) PRIMARY KEY,
name varchar(255),
type varchar(36),
ip varchar(255),
plazaport varchar(4) NOT NULL DEFAULT '9090',
username varchar(36),
password varchar(60)
);`)
if err != nil {
log.Errorf("Unable to create machines table: %s", err)
return err
}
if os.Getenv("IAAS") == "manual" {
servers := os.Getenv("EXECUTION_SERVERS")
password := os.Getenv("WINDOWS_PASSWORD")
user := os.Getenv("WINDOWS_USER")
ips := strings.Split(servers, ";")
for i, val := range ips {
rows, err := db.Query(`INSERT INTO machines
(id, name, type, ip, username, password)
VALUES( $1::varchar, $2::varchar, $3::varchar,
$4::varchar, $5::varchar, $6::varchar)`,
uuid.NewV4().String(),
fmt.Sprintf("Machine #%d", i+1),
"manual",
val,
user,
password,
)
if err != nil {
log.Error("Unable to create machine")
return err
}
rows.Close()
}
}
rows.Close()
return nil
}
// ========================================================================================================================
// Procedure: unpublishApplication
//
// Does:
// - Unpublish specified applications from ActiveDirectory
// ========================================================================================================================
func UnpublishApp(user *users.User, id string) error {
rows, err := db.Query(
`SELECT alias, collection_name FROM apps WHERE id = $1::varchar`,
id,
)
if err != nil {
log.Error(err)
return UnpublishFailed
}
defer rows.Close()
var alias string
var collection string
if !rows.Next() {
return errors.New("Application not found")
}
rows.Scan(&alias, &collection)
plazaAddress := utils.Env("PLAZA_ADDRESS", "iaas-module")
if plazaAddress == "" {
return errors.New("plaza address unknown")
}
plazaPort, err := strconv.Atoi(utils.Env("PLAZA_PORT", "9090"))
if err != nil {
return err
}
winUser, err := user.WindowsCredentials()
if err != nil {
return err
}
_, err = plaza.UnpublishApp(
plazaAddress, plazaPort,
winUser.Sam,
winUser.Domain,
winUser.Password,
collection,
alias,
)
if err != nil {
log.Error(err)
return UnpublishFailed
}
_, err = db.Query("DELETE FROM apps WHERE id = $1::varchar", id)
if err != nil {
log.Error("delete from postgres failed: ", err)
return UnpublishFailed
}
return nil
}
func CreateHistory(
userId string,
userMail string,
userFirstname string,
userLastname string,
connectionId string,
startDate string,
endDate string,
) (*History, error) {
id := uuid.NewV4().String()
rows, err := db.Query(
`INSERT INTO histories
(id, userid, usermail, userfirstname, userlastname, connectionid, startdate, enddate)
VALUES( $1::varchar, $2::varchar, $3::varchar, $4::varchar, $5::varchar, $6::varchar, $7::varchar, $8::varchar)`,
id, userId, userMail, userFirstname, userLastname, connectionId, startDate, endDate)
if err != nil {
return nil, err
}
rows.Close()
rows, err = db.Query(
`SELECT id, userid, usermail, userfirstname, userlastname, connectionid, startdate, enddate
FROM histories WHERE id = $1::varchar`, id)
if err != nil {
return nil, err
}
if !rows.Next() {
return nil, HistoryNotCreated
}
var history History
rows.Scan(
&history.Id,
&history.UserId,
&history.UserMail,
&history.UserFirstname,
&history.UserLastname,
&history.ConnectionId,
&history.StartDate,
&history.EndDate,
)
rows.Close()
return &history, err
}
func FindUsers() ([]*User, error) {
rows, err := db.Query(
`SELECT id,
first_name, last_name,
email, is_admin, activated
FROM users`,
)
if err != nil {
return nil, err
}
var users []*User
defer rows.Close()
for rows.Next() {
user := User{}
rows.Scan(
&user.Id,
&user.FirstName, &user.LastName,
&user.Email,
&user.IsAdmin,
&user.Activated,
)
users = append(users, &user)
}
err = rows.Err()
if err != nil {
return nil, err
}
return users, nil
}
func (u *User) WindowsCredentials() (*WindowsUser, error) {
res, err := db.Query(
`SELECT
machines.username,
machines.password,
machines.domain
FROM machines_users
LEFT JOIN
machines
ON machines_users.machine_id = machines.id
WHERE machines_users.user_id = $1::varchar`,
u.GetID(),
)
if err != nil {
return nil, err
}
defer res.Close()
if !res.Next() {
return nil, errors.New("No credentials found for this user")
}
winUser := WindowsUser{}
res.Scan(
&winUser.Sam,
&winUser.Password,
&winUser.Domain,
)
return &winUser, nil
}
func GetAll(userSam string) ([]Session, error) {
var sessionList []Session
resp, err := http.Get("http://" + kServer + ":" + kPort + "/sessions/" + userSam)
if err != nil {
return nil, err
}
b, err := ioutil.ReadAll(resp.Body)
if err != nil {
log.Error(err)
return nil, err
}
var body struct {
Data [][]string
}
err = json.Unmarshal(b, &body)
if err != nil {
return nil, err
}
for _, tab := range body.Data {
rows, err := db.Query(
`SELECT users.id FROM users
left join users_windows_user on users.id = users_windows_user.user_id
left join windows_users on users_windows_user.windows_user_id = windows_users.id
WHERE windows_users.sam = $1::varchar`,
tab[1])
if err != nil {
return nil, err
}
defer rows.Close()
var user_id string
if rows.Next() {
err = rows.Scan(
&user_id,
)
if err != nil {
return nil, err
}
var session Session
session.SessionName = tab[0]
session.Username = tab[1]
session.Id = tab[2]
session.State = tab[3]
session.UserId = user_id
sessionList = append(sessionList, session)
}
}
return sessionList, nil
}
func GetApp(appId string) (*Application, error) {
rows, err := db.Query(
`SELECT id, collection_name,
alias, display_name,
file_path,
icon_content
FROM apps WHERE id = $1::varchar`, appId)
if err != nil {
return nil, GetAppFailed
}
defer rows.Close()
if rows.Next() {
var application Application
err = rows.Scan(
&application.Id,
&application.CollectionName,
&application.Alias,
&application.DisplayName,
&application.FilePath,
&application.IconContents,
)
if err != nil {
return nil, err
}
return &application, nil
}
return nil, nil
}
// Return a map with the keys arguments associated with the found value if any.
// If the key doesn't exist, no field for the actual key will be present in the map.
func Get(keys ...string) map[string]string {
rt := make(map[string]string)
l := len(keys)
if l == 0 {
return rt
}
queryArgs := make([]string, l)
args := make([]interface{}, l)
for k, v := range keys {
args[k] = v
queryArgs[k] = fmt.Sprintf("$%d::varchar", k+1)
}
rows, err := db.Query(
fmt.Sprintf("SELECT key, value FROM config WHERE key IN(%s)", strings.Join(queryArgs, ",")),
args...,
)
if err != nil {
log.Error(err)
return rt
}
defer rows.Close()
for rows.Next() {
var key string
var value string
rows.Scan(&key, &value)
rt[key] = value
}
return rt
}
func (v *vm) Machine(id string) (vms.Machine, error) {
rows, err := db.Query(
`SELECT id, name, ip, plazaport, username, password
FROM machines WHERE id = $1::varchar`, id)
if err != nil {
fmt.Println(err.Error())
return nil, err
}
machine := &machine{}
for rows.Next() {
rows.Scan(
&machine.id,
&machine.name,
&machine.server,
&machine.plazaport,
&machine.user,
&machine.password,
)
}
err = rows.Err()
if err != nil {
log.Error(err.Error())
return nil, err
}
return machine, nil
}
func Get(c *echo.Context) error {
user := c.Get("user").(*users.User)
res, err := db.Query(
"SELECT id, created_at FROM oauth_access_tokens WHERE user_id = $1::varchar",
user.Id,
)
if err != nil {
return err
}
defer res.Close()
r := make([]hash, 0)
for res.Next() {
var id, createdAt string
err := res.Scan(&id, &createdAt)
if err != nil {
continue
}
r = append(r, hash{
"id": id,
"type": "token",
"attributes": hash{
"created-at": createdAt,
},
})
}
return c.JSON(http.StatusOK, r)
}
func GetAllApps() ([]*Application, error) {
rows, err := db.Query(
`SELECT id, collection_name,
alias, display_name,
file_path,
icon_content
FROM apps`)
if err != nil {
log.Error("Connection to postgres failed: ", err.Error())
return nil, GetAppsFailed
}
defer rows.Close()
applications := make([]*Application, 0)
for rows.Next() {
appParam := Application{}
rows.Scan(
&appParam.Id,
&appParam.CollectionName,
&appParam.Alias,
&appParam.DisplayName,
&appParam.FilePath,
&appParam.IconContents,
)
applications = append(applications, &appParam)
}
return applications, nil
}
func GetUser(id string) (*User, error) {
rows, err := db.Query(
`SELECT id,
first_name, last_name,
email, is_admin, activated
FROM users
WHERE id = $1::varchar`,
id)
if err != nil {
return nil, err
}
defer rows.Close()
if rows.Next() {
var user User
err = rows.Scan(
&user.Id,
&user.FirstName,
&user.LastName,
&user.Email,
&user.IsAdmin,
&user.Activated,
)
if err != nil {
return nil, err
}
return &user, nil
}
return nil, nil
}
func (c oauthConnector) GetUserFromAccessToken(accessToken string) (interface{}, error) {
rows, err := db.Query(
`SELECT user_id
FROM oauth_access_tokens
WHERE token = $1::varchar
AND expires_at > NOW()
`,
accessToken,
)
if err != nil {
return nil, err
}
defer rows.Close()
if !rows.Next() {
return nil, nil
}
var userId string
err = rows.Scan(&userId)
if err != nil {
return nil, err
}
return users.GetUser(userId)
}
func FindAll() ([]*History, error) {
result := make([]*History, 0)
res, err := db.Query(
`SELECT id, userid, usermail, userfirstname,
userlastname, connectionid, startdate, enddate FROM histories`,
)
if err != nil {
return nil, err
}
defer res.Close()
for res.Next() {
h := History{}
res.Scan(
&h.Id,
&h.UserId,
&h.UserMail,
&h.UserFirstname,
&h.UserLastname,
&h.ConnectionId,
&h.StartDate,
&h.EndDate,
)
result = append(result, &h)
}
return result, nil
}
func (u *User) WindowsCredentials() (*WindowsUser, error) {
res, err := db.Query(
`SELECT
windows_users.sam,
windows_users.password,
windows_users.domain
FROM users_windows_user
LEFT JOIN
windows_users
ON users_windows_user.windows_user_id = windows_users.id
WHERE users_windows_user.user_id = $1::varchar`,
u.Id,
)
if err != nil {
return nil, err
}
defer res.Close()
if !res.Next() {
return nil, errors.New("No credentials found for this user")
}
winUser := WindowsUser{}
res.Scan(
&winUser.Sam,
&winUser.Password,
&winUser.Domain,
)
return &winUser, nil
}
// ========================================================================================================================
// Procedure: unpublishApplication
//
// Does:
// - Unpublish specified applications from ActiveDirectory
// ========================================================================================================================
func UnpublishApp(Alias string) error {
cmd := exec.Command(
"sshpass", "-p", kPassword,
"ssh", "-o", "StrictHostKeyChecking=no",
"-o", "UserKnownHostsFile=/dev/null",
"-p", kSSHPort,
fmt.Sprintf(
"%s@%s",
kUser,
kServer,
),
"powershell.exe \"Import-Module RemoteDesktop; Remove-RDRemoteApp -Alias '"+Alias+"' -CollectionName collection -Force\"",
)
response, err := cmd.CombinedOutput()
if err != nil {
log.Error("Failed to execute sshpass command to unpublish an app", err, string(response))
return UnpublishFailed
}
_, err = db.Query("DELETE FROM apps WHERE alias = $1::varchar", Alias)
if err != nil {
log.Error("delete from postgres failed: ", err)
return UnpublishFailed
}
return nil
}
/*
* Download tokens are needed to download a file without a OAuth access token.
* As we do a simple HTTP request, we don't want the token to appear in the history
* or to be shared to a third party user.
* Download tokens are valid for one hour.
*
* A token is build as : {access_token_id}:{SHA1{oauth_access_token:filename:time_stone}}
* Where:
* - access_token_id is the id of a OAuth access token of the file owner
* - oauth_access_token is the token associated to access_token_id
* - filename is the name of the filename associated to the token
* - time_stone is this (($NOW + $NOW % 3600) + 3600) where NOW is a unix timestamp.
* It makes a download token valid for current and the next hour.
* (is the token is generate at 1:55am then the token is valid from 1:00am to 2:59)
*/
func createDownloadToken(user *users.User, accessToken string, filename string) (string, error) {
rows, err := db.Query(
"SELECT id FROM oauth_access_tokens WHERE token = $1::varchar AND user_id = $2::varchar",
accessToken,
user.Id,
)
if err != nil {
return "", err
}
defer rows.Close()
if !rows.Next() {
return "", errors.New("unable to retreive user from access token")
}
var accessTokenId string
err = rows.Scan(&accessTokenId)
if err != nil {
return "", err
}
timeStone := time.Now().Unix()
timeStone = timeStone + (3600 - timeStone%3600) + 3600
h := sha1Hash(fmt.Sprintf(
"%s:%s:%d",
accessToken,
filename,
timeStone,
))
return accessTokenId + ":" + h, nil
}
func GetUser(id string) (*User, error) {
rows, err := db.Query(
`SELECT id, first_name, last_name, email, is_admin,
activated, extract(epoch from signup_date)
FROM users
WHERE id = $1::varchar`,
id)
if err != nil {
return nil, err
}
defer rows.Close()
if rows.Next() {
var user User
var timestamp float64
err = rows.Scan(
&user.Id,
&user.FirstName,
&user.LastName,
&user.Email,
&user.IsAdmin,
&user.Activated,
×tamp,
)
if err != nil {
return nil, err
}
// javascript time is in millisecond not in second
user.SignupDate = int(1000 * timestamp)
return &user, nil
}
return nil, nil
}
func PublishApp(user *users.User, app *Application) error {
plazaAddress := utils.Env("PLAZA_ADDRESS", "iaas-module")
if plazaAddress == "" {
return errors.New("plaza address unknown")
}
plazaPort, err := strconv.Atoi(utils.Env("PLAZA_PORT", "9090"))
if err != nil {
return err
}
winUser, err := user.WindowsCredentials()
if err != nil {
return err
}
res, err := plaza.PublishApp(
plazaAddress, plazaPort,
winUser.Sam,
winUser.Domain,
winUser.Password,
app.CollectionName,
app.DisplayName,
app.Path,
)
if err != nil {
log.Error(err)
return PublishFailed
}
a := ApplicationWin{}
err = json.Unmarshal(res, &a)
if err != nil {
return err
}
id := uuid.NewV4().String()
_, err = db.Query(
`INSERT INTO apps
(id, collection_name, alias, display_name, file_path, icon_content)
VALUES ( $1::varchar, $2::varchar, $3::varchar, $4::varchar, $5::varchar, $6::bytea)
`,
id, a.CollectionName, a.Alias, a.DisplayName, a.FilePath, a.IconContents,
)
if err != nil {
return err
}
app.CollectionName = a.CollectionName
app.Alias = a.Alias
app.DisplayName = a.DisplayName
app.FilePath = a.FilePath
app.IconContents = a.IconContents
app.Id = id
return nil
}
func RetrieveConnections(user *users.User, users []*users.User) ([]Connection, error) {
rand.Seed(time.Now().UTC().UnixNano())
var connections []Connection
rows, err := db.Query("SELECT alias FROM apps")
if err != nil {
log.Error("Unable to retrieve apps list from Postgres: ", err.Error())
return nil, AppsListUnavailable
}
defer rows.Close()
var execServ string
for rows.Next() {
appParam := Application{}
rows.Scan(
&appParam.Alias,
)
if count := len(kExecutionServers); count > 0 {
execServ = kExecutionServers[rand.Intn(count)]
} else {
execServ = kServer
}
winUser, err := user.WindowsCredentials()
if err != nil {
return nil, err
}
username := winUser.Sam + "@" + winUser.Domain
pwd := winUser.Password
var conn Connection
if appParam.Alias != "hapticDesktop" {
conn = Connection{
Hostname: execServ,
Port: kRDPPort,
Protocol: kProtocol,
Username: username,
Password: pwd,
RemoteApp: "||" + appParam.Alias,
AppName: appParam.Alias,
}
} else {
conn = Connection{
Hostname: execServ,
Port: kRDPPort,
Protocol: kProtocol,
Username: username,
Password: pwd,
RemoteApp: "",
AppName: "hapticDesktop",
}
}
connections = append(connections, conn)
}
return connections, nil
}
func CreateUser(
activated bool,
email string,
firstName string,
lastName string,
password string,
isAdmin bool,
) (*User, error) {
id := uuid.NewV4().String()
pass, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
if err != nil {
return nil, err
}
rows, err := db.Query(
`INSERT INTO users
(id, email, activated,
first_name, last_name,
password, is_admin)
VALUES(
$1::varchar, $2::varchar, $3::bool,
$4::varchar, $5::varchar,
$6::varchar, $7::bool)
RETURNING id, email, activated,
first_name, last_name,
is_admin`,
id, email, activated,
firstName, lastName,
pass, isAdmin)
if err != nil {
switch err.Error() {
case "pq: duplicate key value violates unique constraint \"users_pkey\"":
log.Error("user id exists already")
return nil, UserDuplicated
case "pq: duplicate key value violates unique constraint \"users_email_key\"":
log.Error("user email exists already")
return nil, UserDuplicated
}
return nil, err
}
defer rows.Close()
if !rows.Next() {
return nil, UserNotCreated
}
var user User
rows.Scan(
&user.Id, &user.Email,
&user.Activated, &user.FirstName,
&user.LastName, &user.IsAdmin,
)
return &user, err
}
func Migrate() error {
rows, err := db.Query(
`SELECT table_name
FROM information_schema.tables
WHERE table_name = 'apps'`)
if err != nil {
log.Error("Select tables names failed: ", err.Error())
return err
}
defer rows.Close()
if rows.Next() {
log.Info("apps table already set up")
return nil
}
rows, err = db.Query(
`CREATE TABLE apps (
id varchar(36) PRIMARY KEY,
collection_name varchar(36),
alias varchar(36) UNIQUE,
display_name varchar(36),
file_path varchar(255),
icon_content bytea
);`)
if err != nil {
log.Errorf("Unable to create apps table: %s", err)
return err
}
id := uuid.NewV4().String()
rows, err = db.Query(
`INSERT INTO apps
(id, collection_name, alias, display_name, file_path, icon_content)
VALUES ($1::varchar, $2::varchar, $3::varchar, $4::varchar, $5::varchar, $6::bytea)
`,
id, "", "hapticDesktop", "Desktop", "", "",
)
if err != nil {
log.Errorf("Unable to insert haptic desktop: %s", err)
return err
}
rows.Close()
return nil
}
func DisableUser(id string) error {
rows, err := db.Query(
`UPDATE users
SET activated = false
WHERE id = $1::varchar`,
id)
if err != nil {
rows.Close()
}
return err
}
func ChangeName(appId, newName string) error {
_, err := db.Query(
`UPDATE apps
SET display_name = $1::varchar
WHERE alias = $2::varchar`,
newName, appId)
if err != nil {
log.Error("Changing app name failed: ", err)
return FailedNameChange
}
return nil
}