Mercurial > gemma

annotate auth/opendb.go @ 196:b67208d82543

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make test output more comprehensive Running all tests in one transaction ensures the final output tells about any failing test, not just in the last transaction (i.e. test script). The price is that no traces of the tests are left in the database because we have to rollback in order to have no left-over test roles in the cluster.
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Jul 2018 18:31:45 +0200
parents a98a282f00e1
children 0777aa6de45b
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"
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
5 "fmt"
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
6 "strings"
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
7
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
8 "gemma.intevation.de/gemma/config"
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
9
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
10 _ "github.com/jackc/pgx/stdlib"
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
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
13 const driver = "pgx"
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
14
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
15 // dbQuote quotes strings to be able to contain whitespace
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
16 // and backslashes in database DSN strings.
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
17 var dbQuote = strings.NewReplacer(`\`, `\\`, `'`, `\'`).Replace
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
18
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
19 // dbDSN creates a data source name suitable for sql.Open on
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
20 // PostgreSQL databases.
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
21 func dbDSN(host string, port uint, dbname, user, password string, sslmode string) string {
125
a98a282f00e1 Wired token generator and connection pool to token server.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
22 return fmt.Sprintf("host=%s port=%d dbname=%s user=%s password=%s sslmode=%s",
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
23 dbQuote(host), port, dbQuote(dbname),
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
24 dbQuote(user), dbQuote(password), sslmode)
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
25 }
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
26
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 func opendb(user, password string) (*sql.DB, error) {
125
a98a282f00e1 Wired token generator and connection pool to token server.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
28 dsn := dbDSN(
28
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
29 config.Config.DBHost, config.Config.DBPort,
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
30 config.Config.DBName,
714787accd26 Fetch database connection string parts from configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 26
diff changeset
31 user, password,
125
a98a282f00e1 Wired token generator and connection pool to token server.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
32 config.Config.DBSSLMode)
a98a282f00e1 Wired token generator and connection pool to token server.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
33 return sql.Open(driver, dsn)
26
96a429c5f227 Fundamental connection pool based on tokens.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 }
124
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
35
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
36 const allRoles = `
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
37 WITH RECURSIVE cte AS (
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
38 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
39 UNION ALL
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
40 SELECT m.roleid
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
41 FROM cte
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
42 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
43 )
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
44 SELECT rolname FROM pg_roles
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
45 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
46
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
47 func AllOtherRoles(user, password string) ([]string, error) {
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
48 db, err := opendb(user, password)
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
bb9120d28950 Generate JWT from database roles.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 29
diff changeset
59 roles := []string{} // explicit empty by intention.
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 }