Mercurial > gemma

annotate pkg/auth/opendb.go @ 501:c10c76c92797 metamorph-for-all

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use metamorphic database connections for auth.RunAs().
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 24 Aug 2018 15:30:31 +0200
parents b2dc9c2f69e0
children 7e45aaec7081
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 (
486
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
4 "context"
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 "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
6 "errors"
486
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
7 "sync"
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
8
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"
405bdb9c6a77 Fix for wamos/issue96 (Login Behavior: names with spaces don't work)
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 414
diff changeset
10 "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
11
414
c1047fd04a3a Moved project specific Go packages to new pkg folder.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 332
diff changeset
12 "gemma.intevation.de/gemma/pkg/config"
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 )
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
501
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
15 var ErrNoMetamorphUser = errors.New("No metamorphic user configured")
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
16
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
17 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
18
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
19 // 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
20 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
21 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
22 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
23 }
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
24
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
25 // 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
26 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
27 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
28 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
29 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
30 cc.Database = config.DBName()
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
31
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
32 return stdlib.OpenDB(cc), nil
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 }
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
34
486
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
35 type metamorph struct {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
36 sync.Mutex
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
37 db *sql.DB
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
38 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
39
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
40 var mm metamorph
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
41
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
42 func (m *metamorph) open() (*sql.DB, error) {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
43 m.Lock()
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
44 defer m.Unlock()
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
45 if m.db != nil {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
46 return m.db, nil
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
47 }
501
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
48 user := config.MetamorphDBUser()
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
49 if user == "" {
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
50 return nil, ErrNoMetamorphUser
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
51 }
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
52 db, err := OpenDB(user, config.MetamorhpDBPassword())
486
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
53 if err != nil {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
54 return nil, err
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
55 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
56 m.db = db
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
57 return db, nil
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
58 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
59
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
60 func MetamorphConn(ctx context.Context, user string) (*sql.Conn, error) {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
61 db, err := mm.open()
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
62 if err != nil {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
63 return nil, err
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
64 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
65 conn, err := db.Conn(ctx)
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
66 if err != nil {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
67 return nil, err
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
68 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
69 if _, err := conn.ExecContext(ctx, `SELECT public.setrole_plan($1)`, user); err != nil {
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
70 conn.Close()
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
71 return nil, err
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
72 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
73 return conn, nil
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
74 }
b2dc9c2f69e0 First stab to use the metamorphic db to do all database stuff.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 468
diff changeset
75
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
76 const allRoles = `
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
77 WITH RECURSIVE cte AS (
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
78 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
79 UNION ALL
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
80 SELECT m.roleid
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
81 FROM cte
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
82 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
83 )
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
84 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
85 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
86 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
87
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
88 func AllOtherRoles(user, password string) (Roles, error) {
302
0777aa6de45b Password reset. Part I
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 125
diff changeset
89 db, err := OpenDB(user, password)
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
90 if err != nil {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
91 return nil, err
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
92 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
93 defer db.Close()
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
94 rows, err := db.Query(allRoles)
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
95 if err != nil {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
96 return nil, err
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
97 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
98 defer rows.Close()
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
99
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
100 roles := Roles{} // explicit empty by intention.
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
101
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
102 for rows.Next() {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
103 var role string
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
104 if err := rows.Scan(&role); err != nil {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
105 return nil, err
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
106 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
107 roles = append(roles, role)
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
108 }
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
109 return roles, rows.Err()
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
110 }
438
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
111
501
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
112 func RunAs(role string, ctx context.Context, fn func(*sql.Conn) error) error {
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
113 conn, err := MetamorphConn(ctx, role)
438
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
114 if err != nil {
501
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
115 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
116 }
501
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
117 defer conn.Close()
c10c76c92797 Use metamorphic database connections for auth.RunAs().
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 486
diff changeset
118 return fn(conn)
438
ffdb507d5b42 Removed db service user. Use an impersonated metamorph user instead.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 415
diff changeset
119 }