Mercurial > gemma
annotate pkg/auth/opendb.go @ 503:cb555bffbc97
Format JSON file
This will allow readable diffs in case of changes.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 24 Aug 2018 15:35:32 +0200 |
parents | ff9dbe14f033 |
children | b2dc9c2f69e0 |
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" |
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
|
6 |
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 "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
|
8 "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
|
9 |
414
c1047fd04a3a
Moved project specific Go packages to new pkg folder.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
332
diff
changeset
|
10 "gemma.intevation.de/gemma/pkg/config" |
26
96a429c5f227
Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
11 ) |
96a429c5f227
Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
12 |
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
|
13 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
|
14 |
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
|
15 // 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
|
16 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
|
17 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
|
18 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
|
19 } |
28
714787accd26
Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
26
diff
changeset
|
20 |
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
|
21 // 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
|
22 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
|
23 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
|
24 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
|
25 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
|
26 cc.Database = config.DBName() |
28
714787accd26
Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
26
diff
changeset
|
27 |
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
|
28 return stdlib.OpenDB(cc), nil |
26
96a429c5f227
Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
29 } |
124
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
30 |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
31 const allRoles = ` |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
32 WITH RECURSIVE cte AS ( |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
33 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
|
34 UNION ALL |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
35 SELECT m.roleid |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
36 FROM cte |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
37 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
|
38 ) |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
39 SELECT rolname FROM pg_roles |
453
a7dc68d8e22f
Only let users in which are listed in users.list_users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
447
diff
changeset
|
40 WHERE oid IN (SELECT oid FROM cte) AND rolname <> current_user |
a7dc68d8e22f
Only let users in which are listed in users.list_users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
447
diff
changeset
|
41 AND EXISTS (SELECT 1 FROM users.list_users WHERE username = current_user)` |
124
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
42 |
467
73c7b2d6246e
Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
461
diff
changeset
|
43 var ErrNoMetamorphUser = errors.New("No metamorphic user configured") |
438
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
44 |
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
|
45 func AllOtherRoles(user, password string) (Roles, error) { |
302
0777aa6de45b
Password reset. Part I
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
125
diff
changeset
|
46 db, err := OpenDB(user, password) |
124
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
47 if err != nil { |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
48 return nil, err |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
49 } |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
50 defer db.Close() |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
51 rows, err := db.Query(allRoles) |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
52 if err != nil { |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
53 return nil, err |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
54 } |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
55 defer rows.Close() |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
56 |
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
|
57 roles := Roles{} // explicit empty by intention. |
124
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
58 |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
59 for rows.Next() { |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
60 var role string |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
61 if err := rows.Scan(&role); err != nil { |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
62 return nil, err |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
63 } |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
64 roles = append(roles, role) |
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 return roles, rows.Err() |
bb9120d28950
Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
29
diff
changeset
|
67 } |
438
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
68 |
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
69 func RunAs(role string, fn func(*sql.DB) error) error { |
461
685b886002b8
Baild out og RunAs if no metamorphic user is configured.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
453
diff
changeset
|
70 user := config.MetamorphDBUser() |
685b886002b8
Baild out og RunAs if no metamorphic user is configured.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
453
diff
changeset
|
71 if user == "" { |
685b886002b8
Baild out og RunAs if no metamorphic user is configured.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
453
diff
changeset
|
72 return ErrNoMetamorphUser |
685b886002b8
Baild out og RunAs if no metamorphic user is configured.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
453
diff
changeset
|
73 } |
685b886002b8
Baild out og RunAs if no metamorphic user is configured.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
453
diff
changeset
|
74 db, err := OpenDB(user, config.MetamorhpDBPassword()) |
438
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
75 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
|
76 return nil |
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
77 } |
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
78 defer db.Close() |
468
ff9dbe14f033
Don't use hex encoding for user impersonation when running it from a planned statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
467
diff
changeset
|
79 if _, err = db.Exec(`SELECT public.setrole_plan($1)`, role); err == nil { |
467
73c7b2d6246e
Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
461
diff
changeset
|
80 err = fn(db) |
438
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
81 } |
467
73c7b2d6246e
Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
461
diff
changeset
|
82 return err |
438
ffdb507d5b42
Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
415
diff
changeset
|
83 } |