This is an automated email from the git hooks/post-receive script. New commit to branch bow-v2-go in repository bow. See https://gitlab.nuiton.org/chorem/bow.git commit 68f57ba40a7af7c8c6c5476b1ff0d289b7af55e9 Author: Benjamin <poussin@codelutin.com> Date: Thu Apr 9 22:56:42 2020 +0200 en cours de refactoring des requetes sql --- doc/implementation.md | 1 + migrate/001_init_schema.sql | 50 ++++++++-- pkg/repository/database.go | 46 +++++++++ pkg/repository/userRepository.go | 211 ++++++++++++++++++++++++++++++++------- pkg/utils/error.go | 52 ++++++++++ 5 files changed, 312 insertions(+), 48 deletions(-) diff --git a/doc/implementation.md b/doc/implementation.md index b8f3fcb..fbbe2e9 100644 --- a/doc/implementation.md +++ b/doc/implementation.md @@ -1,4 +1,5 @@ TODO: table d'historique d'authentification +TODO: faire des tests de perf entre TEXT[] et jsonb qui stockerait que des chaines == Creation d'un compte diff --git a/migrate/001_init_schema.sql b/migrate/001_init_schema.sql index 5ca5dcd..a40815a 100644 --- a/migrate/001_init_schema.sql +++ b/migrate/001_init_schema.sql @@ -8,7 +8,7 @@ CREATE EXTENSION IF NOT EXISTS "citext"; -- CREATE EXTENSION IF NOT EXISTS "btree_gist"; -- fonction a utilise pour les recherches dans les tableaux de text (ex: where text(tags) ilike '%adm%') -CREATE OR REPLACE FUNCTION text(citext[]) +CREATE OR REPLACE FUNCTION text(text[]) returns text language sql immutable as $$ select $1::text $$; @@ -63,8 +63,8 @@ CREATE TABLE bowgroup ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), creationDate timestamp DEFAULT current_timestamp, updateDate timestamp DEFAULT current_timestamp, - name Text, - password Text, + name TEXT, + description TEXT, tokens jsonb, -- [{name: sring, token: string, expiration: date}] admin UUID[], writer UUID[], @@ -82,7 +82,7 @@ CREATE TABLE bookmark ( owner UUID REFERENCES bowUser(id) ON DELETE CASCADE ON UPDATE CASCADE, uri TEXT, description TEXT, - tags citext[], + tags text[], creationDate TIMESTAMP, updateDate timestamp DEFAULT current_timestamp, importDate TIMESTAMP, @@ -158,28 +158,60 @@ CREATE TRIGGER update_Bookmark_updateDate BEFORE INSERT OR UPDATE ON Bookmark FO -- nobody n'herite pas des droits des autres pour le force a faire un "set role" CREATE USER nobody WITH NOINHERIT CREATEROLE LOGIN PASSWORD '{{.nobody_password}}' +-- l'utilisateur nobody a le droit d'inserer des users (creation de compte) et c'est lui qui visite les pages GRANT INSERT ON bowUser TO nobody; GRANT INSERT ON pageHistory TO nobody; -GRANT SELECT, DELETE, TRIGGER ON bowUser TO PUBLIC; +-- tout le monde a le droit de faire certaine chose, on restraint les updates au champs qui peuvent varier +GRANT SELECT, INSERT, DELETE, TRIGGER ON bowUser TO PUBLIC; GRANT UPDATE (updateDate, password, tokens, emails, unconfirmedEmails, authenticationInfo, autoScreenshot, autoFavicon, maxTagInCloud, maxResult, actions) ON bowUser TO PUBLIC; +GRANT SELECT, INSERT, DELETE, TRIGGER ON bowgroup TO PUBLIC; +GRANT UPDATE(updateDate, description, tokens, admin, writer, reader) ON bowgroup TO PUBLIC; GRANT SELECT, INSERT, DELETE, TRIGGER ON bookmark TO PUBLIC; GRANT UPDATE(uri, description, tags, updateDate, privateAlias, publicAlias, authenticationInfo, favicon, screenshot, visit, lang) ON bookmark TO PUBLIC; +-- on ne peut pas modifier une action, mais on peut la supprimer GRANT SELECT, INSERT, DELETE, TRIGGER ON actionHistory TO PUBLIC; +-- on ne peut que lire les historiques de pages GRANT SELECT ON pageHistory TO PUBLIC; ALTER TABLE bowUser ENABLE ROW LEVEL SECURITY; +ALTER TABLE bowgroup ENABLE ROW LEVEL SECURITY; ALTER TABLE bookmark ENABLE ROW LEVEL SECURITY; ALTER TABLE actionHistory ENABLE ROW LEVEL SECURITY; +-- les utilisateurs n'ont le droit d'agir que sur leur propre compte CREATE POLICY bowUser_access ON bowUser - USING (id::text = current_user); + FOR ALL + USING (id = current_user::uuid); + +-- tout le monde peut lire les groupes si on est dans les utilisateurs du groupes +CREATE POLICY bowgroup_access_select ON bowgroup + FOR SELECT + USING ((admin || writer || reader ) @> ('{' || current_user || '}')::uuid[]); + +-- tous les utilisateurs peuvent créer un groupe s'il finisse admin du groupe +CREATE POLICY bowgroup_access_insert ON bowgroup + FOR INSERT + WITH CHECK (admin @> ('{' || current_user || '}')::uuid[]); +-- seul les admins peuvent ajouter/retirer des utilisateurs d'un group, mais ils ne peuvent pas se retirer eux meme de l'admin (il reste toujours un admin) +CREATE POLICY bowgroup_access_update ON bowgroup + FOR UPDATE + USING (admin @> ('{' || current_user || '}')::uuid[]); + +-- tout le monde peut creer des bookmarks, mais a la fin le createur doit etre owner CREATE POLICY bookmark_access ON bookmark - USING (owner::text = current_user); + USING (owner = current_user::uuid); + +-- si un bookmark a comme tag '@toto' et proprietaire '1234' alors il est visible de toutes les utilisateurs +-- appartenant (admin, writer, reader) au groupe 'toto' dont l'utilisateur '1234' est admin ou writer +CREATE POLICY bookmark_access_group ON bookmark + USING ( current_user::uuid in (SELECT unnest(admin || writer || reader) FROM bowgroup WHERE tags @> ('{@' || name || '}')::text[] + AND ('{' || owner || '}')::uuid[] <@ (admin || writer))); +-- tout le monde peut gerer des actionHistory, mais a la fin le createur doit etre owner (il n'y pas droit au update via le GRANT) CREATE POLICY actionHistory_access ON actionHistory - USING (owner::text = current_user); + USING (owner = current_user::uuid); -- droit specifique pour nobody -- il peut lire tous les id, login, email, password @@ -200,10 +232,8 @@ DROP TYPE AuthenticationInfo; DROP TYPE Action; DROP TYPE Token; -DROP FUNCTION text(citext[]); DROP FUNCTION update_creationDate_column; DROP FUNCTION update_updateDate_column; DROP EXTENSION IF EXISTS "pgcrypto"; DROP EXTENSION IF EXISTS "pg_trgm"; -DROP EXTENSION IF EXISTS "citext"; \ No newline at end of file diff --git a/pkg/repository/database.go b/pkg/repository/database.go index 795373c..a925fa8 100644 --- a/pkg/repository/database.go +++ b/pkg/repository/database.go @@ -151,3 +151,49 @@ func migrateDatabase(databaseURL string) { os.Exit(1) } } + +func execOnOneRow(currentUserID string, presql string, sql string, postsql string, arguments ...interface{}) error { + tx, err := db.Begin(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) + + if presql != nil { + _, err = db.Exec(context.Background(), fmt.Sprintf(presql, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + } + + _, err = db.Exec(context.Background(), fmt.Sprintf(` + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + + modif, err := db.Exec(context.Background(), sql, arguments) + + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + + if modif.RowsAffected() != 1 { + return return utils.NewHTTPError(fmt.Sprintf("User not found '%s'", id), currentUserID, 404) + } + + if postsql != nil { + _, err = db.Exec(context.Background(), fmt.Sprintf(postsql, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + } + + err = tx.Commit(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + + return nil +} diff --git a/pkg/repository/userRepository.go b/pkg/repository/userRepository.go index e630963..20c234d 100644 --- a/pkg/repository/userRepository.go +++ b/pkg/repository/userRepository.go @@ -98,7 +98,9 @@ func CheckUserPasswordForEmail(loginOrEmail string, password string) (string, er var hash string row := db.QueryRow(context.Background(), `select id, password from bowuser where login=$1 or emails @> $2::text[]`, loginOrEmail, fmt.Sprintf(`{"%s"}`, loginOrEmail)) err := row.Scan(&uuid, &hash) - if err != nil { + if err != nil {CREATE POLICY bowUser_access ON bowUser + FOR ALL + USING (id = current_user::uuid); return "", err } @@ -121,80 +123,143 @@ CreateUser retourne l'utilisateur au format json func CreateUser(login string, password string) (string, error) { hashPassword, err := utils.HashPassword(password) if err != nil { - return "", err + return "",utils.NewHTTPError500(err, login) } uuid, err := utils.GenUUID() if err != nil { - return "", err + return "",utils.NewHTTPError500(err, login) } + currentUserID := uuid + user := model.BowUser{ ID: uuid, Login: login, Password: hashPassword, MaxTagInCloud: 20, MaxResult: 20, AutoFavicon: false, AutoScreenshot: false, AuthenticationInfo: model.AuthenticationInfo{DomainComponent: 2, MaxLength: 15}} userAsJSON, err := json.Marshal(user) if err != nil { - return "", err + return "", utils.NewHTTPError500(err, currentUserID) } log.Println("create user", string(userAsJSON)) - modif, err := db.Exec(context.Background(), fmt.Sprintf(` - INSERT INTO bowUser AS t SELECT * FROM json_populate_record(NULL::bowUser, $1::json); + + tx, err := db.Begin(context.Background()) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) + + // pas d'argument sql donc pas de requete preparer, on peut en mettre 3 + _, err = db.Exec(context.Background(), fmt.Sprintf(` CREATE ROLE "%[1]s"; GRANT "%[1]s" TO nobody; - `, user.ID), string(userAsJSON)) + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) + } - if modif.RowsAffected() != 1 { - return "", errors.New("No user created") + modif, err := db.Exec(context.Background(), ` + INSERT INTO bowUser AS t SELECT * FROM json_populate_record(NULL::bowUser, $1::json); + `, string(userAsJSON)) + + if err != nil || modif.RowsAffected() != 1 { + return "", utils.errors.New("No user created") + } + + err = tx.Commit(context.Background()) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) } - return user.ID, err + return user.ID, nil } /* DeleteUser suppression d'un nouveau bookmark */ -func DeleteUser(id string) error { - modif, err := db.Exec(context.Background(), fmt.Sprintf(` - DELETE FROM bowuser WHERE id=$1; - DROP ROLE IF EXISTS %s; - `, id), id) +func DeleteUser(currentUserID string, id string) error { + tx, err := db.Begin(context.Background()) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) + + _, err = db.Exec(context.Background(), fmt.Sprintf(` + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) + } + + modif, err := db.Exec(context.Background(), ` + DELETE FROM bowuser WHERE id=$1;`, id) if modif.RowsAffected() != 1 { return fmt.Errorf("No user found for id '%s'", id) } + _, err = db.Exec(context.Background(), fmt.Sprintf(` + RESET ROLE; + DROP ROLE IF EXISTS "%s"; + `, currentUserID)) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) + } + + err = tx.Commit(context.Background()) + if err != nil { + return "", utils.NewHTTPError500(err, currentUserID) + } + return err } /* UpdateUserPassword update user password, if old password match, or if force is true */ -func UpdateUserPassword(id string, password string, oldPassword string, force bool) error { - if force || CheckUserPasswordForID(id, oldPassword) { +func UpdateUserPassword(currentUserID string, id string, password string, oldPassword string, force bool) error { + if !force && !CheckUserPasswordForID(id, oldPassword) { + return utils.NewHTTPError(fmt.Sprintf("Bad old password for user '%s'", id), currentUserID, 400) + } - hash, err := utils.HashPassword(password) + hash, err := utils.HashPassword(password) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } - if err == nil { - modif, err := db.Exec(context.Background(), `update bowuser SET password=$2 where id=$1;`, id, hash) - if err != nil { - return err - } + tx, err := db.Begin(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) - if modif.RowsAffected() != 1 { - return fmt.Errorf("No user found for id '%s'", id) - } - } + _, err = db.Exec(context.Background(), fmt.Sprintf(` + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } - return err + modif, err := db.Exec(context.Background(), `update bowuser SET password=$2 where id=$1;`, id, hash) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + + if modif.RowsAffected() != 1 { + return return utils.NewHTTPError(fmt.Sprintf("User not found '%s'", id), currentUserID, 404) + } + err = tx.Commit(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) } - return fmt.Errorf("Bad old password for user '%s'", id) + return nil } /* AddUserToken ajout un tocken d'authentification pour l'utilisateur */ -func AddUserToken(id string, name string, expiration time.Time) (string, error) { +func AddUserToken(currentUserID string, id string, name string, expiration time.Time) (string, error) { token, err := utils.GenUUID() if err != nil { return "", err @@ -205,18 +270,41 @@ func AddUserToken(id string, name string, expiration time.Time) (string, error) return token, err } + tx, err := db.Begin(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) + + _, err = db.Exec(context.Background(), fmt.Sprintf(` + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + modif, err := db.Exec(context.Background(), `update bowuser SET tokens=coalesce(tokens, '[]'::jsonb) || $2::jsonb where id=$1;`, id, json) + + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + if modif.RowsAffected() != 1 { - return "", fmt.Errorf("No user found for id '%s'", id) + return return utils.NewHTTPError(fmt.Sprintf("User not found '%s'", id), currentUserID, 404) + } + + err = tx.Commit(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) } - return token, err + return token, nil } /* AddUserUnconfirmedEmail ajout d'un email non confirme, retourne le token permettant la confirmation */ -func AddUserUnconfirmedEmail(id string, email string) (string, error) { +func AddUserUnconfirmedEmail(currentUserID string, id string, email string) (string, error) { token, err := utils.GenUUID() if err != nil { return "", err @@ -227,31 +315,78 @@ func AddUserUnconfirmedEmail(id string, email string) (string, error) { return token, err } + tx, err := db.Begin(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) + + _, err = db.Exec(context.Background(), fmt.Sprintf(` + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + modif, err := db.Exec(context.Background(), `update bowuser SET unconfirmedemails=coalesce(unconfirmedemails, '[]'::jsonb) || $2::jsonb where id=$1;`, id, json) + + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + if modif.RowsAffected() != 1 { - return "", fmt.Errorf("No user found for id '%s'", id) + return return utils.NewHTTPError(fmt.Sprintf("User not found '%s'", id), currentUserID, 404) + } + + err = tx.Commit(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) } - return token, err + return token, nil } /* UpdateUserAuthenticationInfo met a jour les infos d'authentification */ -func UpdateUserAuthenticationInfo(id string, auth model.AuthenticationInfo) error { +func UpdateUserAuthenticationInfo(currentUserID string, id string, auth model.AuthenticationInfo) error { authAsJSON, err := json.Marshal(auth) if err != nil { return err } + tx, err := db.Begin(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + defer tx.Rollback(context.Background()) + + _, err = db.Exec(context.Background(), fmt.Sprintf(` + SET ROLE "%[1]s"; + `, currentUserID)) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + modif, err := db.Exec(context.Background(), ` UPDATE bowuser SET (authenticationinfo) = (SELECT * FROM json_populate_record(NULL::authenticationinfo, $2::json)) WHERE id=$1`, id, string(authAsJSON)) + + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + if modif.RowsAffected() != 1 { - return fmt.Errorf("No user found for id '%s'", id) + return return utils.NewHTTPError(fmt.Sprintf("User not found '%s'", id), currentUserID, 404) } - return err + + err = tx.Commit(context.Background()) + if err != nil { + return utils.NewHTTPError500(err, currentUserID) + } + + return nil } /* diff --git a/pkg/utils/error.go b/pkg/utils/error.go new file mode 100644 index 0000000..1c7afe3 --- /dev/null +++ b/pkg/utils/error.go @@ -0,0 +1,52 @@ +package utils + +import ( + "fmt" + "net/http" +) + +/* +httpError erreur permettant de porter le code HTTP souhaite +*/ +type httpError struct { + ID string + Msg string + CurrentUserID string + StatusCode int +} + +func (e *httpError) Error() string { + return fmt.Sprintf(`{"ID": "%s", "CurrentUserID": "%s", "StatusCode": %v, "Msg": %q}`, e.ID, e.CurrentUserID, e.StatusCode, e.Msg) +} + +func NewHTTPError(msg string, currentUserID string, statusCode int) *httpError { + e := httpError{} + e.ID, _ = GenUUID() + e.Msg = msg + e.CurrentUserID = currentUserID + if statusCode > 0 { + e.StatusCode = statusCode + } else { + e.StatusCode = 500 + } + + return &e +} + +func NewHTTPError500(err error, currentUserID string) *httpError { + e := httpError{} + e.ID, _ = GenUUID() + e.Msg = fmt.Sprintf("%v", err) + e.CurrentUserID = currentUserID + e.StatusCode = 500 + + return &e +} + +func Throw(w http.ResponseWriter, err error) { + if err, ok := err.(*httpError); ok { + http.Error(w, fmt.Sprintf("%s", err), err.StatusCode) + } else { + http.Error(w, fmt.Sprintf("%s", err), 500) + } +} -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.