Mercurial > gemma
annotate auth/opendb.go @ 195:5dc8e734487a
Introduce database schemas as privilege-based namespaces
Some privileges changed (e.g. for responsibility_areas), but additional
privileges were not left off intentionally before.
Search path settings have been replaced by schema-qualifying names in
statements to prevent object definitions from being dependend on
search path settings.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 20 Jul 2018 17:28:16 +0200 |
parents | a98a282f00e1 |
children | 0777aa6de45b |
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 } |