Mercurial > gemma

annotate pkg/auth/opendb.go @ 447:62c909dd3098

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Only allow log in if user has at least one of the roles 'sys_admin', 'waterway_admin', 'waterway_user'.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 21 Aug 2018 18:29:34 +0200
parents ffdb507d5b42
children a7dc68d8e22f
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 package auth
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 import (
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 "database/sql"
438
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
5 "errors"
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
6 "strings"
415
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
7
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
8 "github.com/jackc/pgx"
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
9 "github.com/jackc/pgx/stdlib"
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
10
414
c1047fd04a3a Moved project specific Go packages to new pkg folder.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 332
diff changeset
11 "gemma.intevation.de/gemma/pkg/config"
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 )
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13
415
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
14 func OpenDB(user, password string) (*sql.DB, error) {
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
15
415
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
16 // To ease SSL config ride a bit on parsing.
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
17 cc, err := pgx.ParseConnectionString("sslmode=" + config.DBSSLMode())
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
18 if err != nil {
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
19 return nil, err
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
20 }
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
21
415
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
22 // Do the rest manually to allow whitespace in user/password.
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
23 cc.Host = config.DBHost()
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
24 cc.Port = uint16(config.DBPort())
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
25 cc.User = user
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
26 cc.Password = password
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
27 cc.Database = config.DBName()
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
28
415
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
29 return stdlib.OpenDB(cc), nil
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 }
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
31
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
32 const allRoles = `
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
33 WITH RECURSIVE cte AS (
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
34 SELECT oid FROM pg_roles WHERE rolname = current_user
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
35 UNION ALL
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
36 SELECT m.roleid
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
37 FROM cte
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
38 JOIN pg_auth_members m ON m.member = cte.oid
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
39 )
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
40 SELECT rolname FROM pg_roles
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
41 WHERE oid IN (SELECT oid FROM cte) AND rolname <> current_user`
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
42
438
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
43 const InvalidRoleCharacters = `\"':;`
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
44
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
45 var ErrInvalidRoleCharacters = errors.New("rolename contains invalid character")
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
46
447
62c909dd3098 Only allow log in if user has at least one of the roles 'sys_admin', 'waterway_admin', 'waterway_user'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 438
diff changeset
47 func AllOtherRoles(user, password string) (Roles, error) {
302
0777aa6de45b Password reset. Part I
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 125
diff changeset
48 db, err := OpenDB(user, password)
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
49 if err != nil {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
50 return nil, err
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
51 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
52 defer db.Close()
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
53 rows, err := db.Query(allRoles)
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
54 if err != nil {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
55 return nil, err
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
56 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
57 defer rows.Close()
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
58
447
62c909dd3098 Only allow log in if user has at least one of the roles 'sys_admin', 'waterway_admin', 'waterway_user'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 438
diff changeset
59 roles := Roles{} // explicit empty by intention.
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
60
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
61 for rows.Next() {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
62 var role string
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
63 if err := rows.Scan(&role); err != nil {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
64 return nil, err
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
65 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
66 roles = append(roles, role)
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
67 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
68 return roles, rows.Err()
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
69 }
438
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
70
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
71 func RunAs(role string, fn func(*sql.DB) error) error {
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
72 if strings.Contains(role, InvalidRoleCharacters) {
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
73 return ErrInvalidRoleCharacters
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
74 }
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
75 db, err := OpenDB(config.MetamorphDBUser(), config.MetamorhpDBPassword())
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
76 if err != nil {
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
77 return nil
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
78 }
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
79 defer db.Close()
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
80 if _, err := db.Exec(`SET ROLE "` + role + `"`); err != nil {
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
81 return err
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
82 }
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
83 return fn(db)
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
84 }