Mercurial > gemma
view auth/opendb.go @ 334:df1fc589ad9d
Prevent Waterway Admins from updating users from their country
We need to ensure this in the trigger function, because we cannot apply
different rules for SELECT and UPDATE like with RLS policies on real tables.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 03 Aug 2018 17:23:55 +0200 |
parents | 394fafeb4052 |
children |
line wrap: on
line source
package auth import ( "database/sql" "fmt" "strings" "gemma.intevation.de/gemma/config" _ "github.com/jackc/pgx/stdlib" ) const driver = "pgx" // dbQuote quotes strings to be able to contain whitespace // and backslashes in database DSN strings. var dbQuote = strings.NewReplacer(`\`, `\\`, `'`, `\'`).Replace // dbDSN creates a data source name suitable for sql.Open on // PostgreSQL databases. func dbDSN(host string, port uint, dbname, user, password string, sslmode string) string { return fmt.Sprintf("host=%s port=%d dbname=%s user=%s password=%s sslmode=%s", dbQuote(host), port, dbQuote(dbname), dbQuote(user), dbQuote(password), sslmode) } func OpenDB(user, password string) (*sql.DB, error) { dsn := dbDSN( config.DBHost(), config.DBPort(), config.DBName(), user, password, config.DBSSLMode()) return sql.Open(driver, dsn) } const allRoles = ` WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = current_user UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT rolname FROM pg_roles WHERE oid IN (SELECT oid FROM cte) AND rolname <> current_user` func AllOtherRoles(user, password string) ([]string, error) { db, err := OpenDB(user, password) if err != nil { return nil, err } defer db.Close() rows, err := db.Query(allRoles) if err != nil { return nil, err } defer rows.Close() roles := []string{} // explicit empty by intention. for rows.Next() { var role string if err := rows.Scan(&role); err != nil { return nil, err } roles = append(roles, role) } return roles, rows.Err() }