Mercurial > gemma

changeset 452:1ebc174a1c53

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge, because not having pulled earlier.
author Bernhard Reiter <bernhard@intevation.de>
date Wed, 22 Aug 2018 10:04:52 +0200
parents ed615a62466c (current diff) a74b8c2a4e75 (diff)
children a7dc68d8e22f
files pkg/controllers/types.go
diffstat 17 files changed, 479 insertions(+), 271 deletions(-) [+]
line wrap: on
line diff
--- a/cmd/gemma/geoserver.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/cmd/gemma/geoserver.go	Wed Aug 22 10:04:52 2018 +0200
@@ -10,6 +10,7 @@
 
 	"gemma.intevation.de/gemma/pkg/config"
 	"gemma.intevation.de/gemma/pkg/misc"
+	"gemma.intevation.de/gemma/pkg/models"
 )
 
 const (
@@ -179,9 +180,9 @@
 		user     = config.GeoServerUser()
 		password = config.GeoServerPassword()
 		auth     = basicAuth(user, password)
-		tables   = config.GeoServerTables()
 	)
 
+	tables := models.PublishedServices.Filter(models.PublishedWFS)
 	if len(tables) == 0 {
 		log.Println("info: no tables to publish")
 		return nil
@@ -233,7 +234,9 @@
 		hasFeature = func(string) bool { return false }
 	}
 
-	for _, table := range tables {
+	for i := range tables {
+		table := tables[i].Name
+
 		if hasFeature(table) {
 			log.Printf("info: featuretype %s already exists.\n", table)
 			continue
@@ -286,7 +289,7 @@
 func prepareGeoServer() error {
 
 	if config.MetamorphDBUser() == "" {
-		log.Panicln("info: Need metamorphic db user to configure GeoServer")
+		log.Println("info: Need metamorphic db user to configure GeoServer")
 		return nil
 	}
 
--- a/pkg/auth/middleware.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/auth/middleware.go	Wed Aug 22 10:04:52 2018 +0200
@@ -63,12 +63,7 @@
 
 func HasRole(roles ...string) func(*Session) bool {
 	return func(session *Session) bool {
-		for _, r1 := range roles {
-			if session.Roles.Has(r1) {
-				return true
-			}
-		}
-		return false
+		return session.Roles.HasAny(roles...)
 	}
 }
 
--- a/pkg/auth/opendb.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/auth/opendb.go	Wed Aug 22 10:04:52 2018 +0200
@@ -2,6 +2,8 @@
 
 import (
 	"database/sql"
+	"errors"
+	"strings"
 
 	"github.com/jackc/pgx"
 	"github.com/jackc/pgx/stdlib"
@@ -38,7 +40,11 @@
 SELECT rolname FROM pg_roles
 WHERE oid IN (SELECT oid FROM cte) AND rolname <> current_user`
 
-func AllOtherRoles(user, password string) ([]string, error) {
+const InvalidRoleCharacters = `\"':;`
+
+var ErrInvalidRoleCharacters = errors.New("rolename contains invalid character")
+
+func AllOtherRoles(user, password string) (Roles, error) {
 	db, err := OpenDB(user, password)
 	if err != nil {
 		return nil, err
@@ -50,7 +56,7 @@
 	}
 	defer rows.Close()
 
-	roles := []string{} // explicit empty by intention.
+	roles := Roles{} // explicit empty by intention.
 
 	for rows.Next() {
 		var role string
@@ -61,3 +67,18 @@
 	}
 	return roles, rows.Err()
 }
+
+func RunAs(role string, fn func(*sql.DB) error) error {
+	if strings.Contains(role, InvalidRoleCharacters) {
+		return ErrInvalidRoleCharacters
+	}
+	db, err := OpenDB(config.MetamorphDBUser(), config.MetamorhpDBPassword())
+	if err != nil {
+		return nil
+	}
+	defer db.Close()
+	if _, err := db.Exec(`SET ROLE "` + role + `"`); err != nil {
+		return err
+	}
+	return fn(db)
+}
--- a/pkg/auth/session.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/auth/session.go	Wed Aug 22 10:04:52 2018 +0200
@@ -2,6 +2,7 @@
 
 import (
 	"encoding/base64"
+	"errors"
 	"io"
 	"time"
 
@@ -27,12 +28,21 @@
 	return false
 }
 
+func (r Roles) HasAny(roles ...string) bool {
+	for _, y := range roles {
+		if r.Has(y) {
+			return true
+		}
+	}
+	return false
+}
+
 const (
 	sessionKeyLength = 20
 	maxTokenValid    = time.Hour * 3
 )
 
-func NewSession(user, password string, roles []string) *Session {
+func NewSession(user, password string, roles Roles) *Session {
 
 	// Create the Claims
 	return &Session{
@@ -78,11 +88,16 @@
 		common.GenerateRandomKey(sessionKeyLength))
 }
 
+var ErrInvalidRole = errors.New("Invalid role")
+
 func GenerateSession(user, password string) (string, *Session, error) {
 	roles, err := AllOtherRoles(user, password)
 	if err != nil {
 		return "", nil, err
 	}
+	if !roles.HasAny("sys_admin", "waterway_admin", "waterway_user") {
+		return "", nil, ErrInvalidRole
+	}
 	token := GenerateSessionKey()
 	session := NewSession(user, password, roles)
 	ConnPool.Add(token, session)
--- a/pkg/config/config.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/config/config.go	Wed Aug 22 10:04:52 2018 +0200
@@ -27,9 +27,6 @@
 func WebHost() string      { return viper.GetString("host") }
 func WebPort() uint        { return uint(viper.GetInt32("port")) }
 
-func ServiceUser() string     { return viper.GetString("service-user") }
-func ServicePassword() string { return viper.GetString("service-password") }
-
 func MetamorphDBUser() string     { return viper.GetString("metamorph-db-user") }
 func MetamorhpDBPassword() string { return viper.GetString("metamorph-db-password") }
 
@@ -47,7 +44,6 @@
 func GeoServerURL() string      { return viper.GetString("geoserver-url") }
 func GeoServerUser() string     { return viper.GetString("geoserver-user") }
 func GeoServerPassword() string { return viper.GetString("geoserver-password") }
-func GeoServerTables() []string { return viper.GetStringSlice("geoserver-tables") }
 
 var (
 	proxyKeyOnce sync.Once
@@ -92,10 +88,6 @@
 	// TODO: Fill me!
 }
 
-var geoTables = []string{
-	"fairway_dimensions",
-}
-
 func init() {
 	cobra.OnInitialize(initConfig)
 	fl := RootCmd.PersistentFlags()
@@ -135,9 +127,6 @@
 	strP("host", "o", "localhost", "host of the web app")
 	uiP("port", "p", 8000, "port of the web app")
 
-	str("service-user", "postgres", "user to do service tasks")
-	str("service-password", "", "password of user to do service tasks")
-
 	str("mail-host", "localhost", "server to send mail with")
 	ui("mail-port", 465, "port of server to send mail with")
 	str("mail-user", "gemma", "user to send mail with")
@@ -150,7 +139,6 @@
 	str("geoserver-url", "http://localhost:8080/geoserver", "URL to GeoServer")
 	str("geoserver-user", "admin", "GeoServer user")
 	str("geoserver-password", "geoserver", "GeoServer password")
-	strSl("geoserver-tables", geoTables, "tables to publish with GeoServer")
 
 	str("metamorph-db-user", "", "Metamorphic database user")
 	str("metamorph-db-password", "", "Metamorphic database user password")
--- a/pkg/controllers/proxy.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/controllers/proxy.go	Wed Aug 22 10:04:52 2018 +0200
@@ -35,29 +35,6 @@
 	"http://schemas.opengis.net/gml":            struct{}{},
 }
 
-func findProxy(key string) func(string) (string, bool) {
-	entries := config.Proxies(key)
-	return func(entry string) (string, bool) {
-		if entries == nil || len(entries) == 0 {
-			return "", false
-		}
-		alias, found := entries[entry]
-		if !found {
-			return "", false
-		}
-		data, ok := alias.(map[string]interface{})
-		if !ok {
-			return "", false
-		}
-		urlS, found := data["url"]
-		if !found {
-			return "", false
-		}
-		url, ok := urlS.(string)
-		return url, ok
-	}
-}
-
 func proxyDirector(lookup func(string) (string, bool)) func(*http.Request) {
 
 	return func(req *http.Request) {
--- a/pkg/controllers/pwreset.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/controllers/pwreset.go	Wed Aug 22 10:04:52 2018 +0200
@@ -15,8 +15,8 @@
 
 	"gemma.intevation.de/gemma/pkg/auth"
 	"gemma.intevation.de/gemma/pkg/common"
-	"gemma.intevation.de/gemma/pkg/config"
 	"gemma.intevation.de/gemma/pkg/misc"
+	"gemma.intevation.de/gemma/pkg/models"
 )
 
 const (
@@ -55,6 +55,8 @@
 	cleanupPause               = 15 * time.Minute
 )
 
+const pwResetRole = "pw_reset"
+
 var (
 	passwordResetRequestMailTmpl = template.Must(
 		template.New("request").Parse(`You have requested a password change
@@ -83,15 +85,6 @@
     Your service team`))
 )
 
-func asServiceUser(fn func(*sql.DB) error) error {
-	db, err := auth.OpenDB(config.ServiceUser(), config.ServicePassword())
-	if err == nil {
-		defer db.Close()
-		err = fn(db)
-	}
-	return err
-}
-
 func init() {
 	go removeOutdated()
 }
@@ -99,7 +92,7 @@
 func removeOutdated() {
 	for {
 		time.Sleep(cleanupPause)
-		err := asServiceUser(func(db *sql.DB) error {
+		err := auth.RunAs(pwResetRole, func(db *sql.DB) error {
 			good := time.Now().Add(-passwordResetValid)
 			_, err := db.Exec(cleanupRequestsSQL, good)
 			return err
@@ -175,7 +168,7 @@
 	_ *sql.DB,
 ) (jr JSONResult, err error) {
 
-	user := input.(*PWResetUser)
+	user := input.(*models.PWResetUser)
 
 	if user.User == "" {
 		err = JSONError{http.StatusBadRequest, "Invalid user name"}
@@ -184,7 +177,7 @@
 
 	var hash, email string
 
-	if err = asServiceUser(func(db *sql.DB) error {
+	if err = auth.RunAs(pwResetRole, func(db *sql.DB) error {
 
 		var count int64
 		if err := db.QueryRow(countRequestsSQL).Scan(&count); err != nil {
@@ -249,7 +242,7 @@
 
 	var email, user, password string
 
-	if err = asServiceUser(func(db *sql.DB) error {
+	if err = auth.RunAs(pwResetRole, func(db *sql.DB) error {
 		err := db.QueryRow(findRequestSQL, hash).Scan(&email, &user)
 		switch {
 		case err == sql.ErrNoRows:
--- a/pkg/controllers/routes.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/controllers/routes.go	Wed Aug 22 10:04:52 2018 +0200
@@ -8,6 +8,7 @@
 
 	"gemma.intevation.de/gemma/pkg/auth"
 	"gemma.intevation.de/gemma/pkg/middleware"
+	"gemma.intevation.de/gemma/pkg/models"
 )
 
 func BindRoutes(m *mux.Router) {
@@ -25,7 +26,7 @@
 	})).Methods(http.MethodGet)
 
 	api.Handle("/users", sysAdmin(&JSONHandler{
-		Input:  func() interface{} { return new(User) },
+		Input:  func() interface{} { return new(models.User) },
 		Handle: createUser,
 	})).Methods(http.MethodPost)
 
@@ -34,7 +35,7 @@
 	})).Methods(http.MethodGet)
 
 	api.Handle("/users/{user}", all(&JSONHandler{
-		Input:  func() interface{} { return new(User) },
+		Input:  func() interface{} { return new(models.User) },
 		Handle: updateUser,
 	})).Methods(http.MethodPut)
 
@@ -44,7 +45,7 @@
 
 	// Password resets.
 	api.Handle("/users/passwordreset", &JSONHandler{
-		Input:  func() interface{} { return new(PWResetUser) },
+		Input:  func() interface{} { return new(models.PWResetUser) },
 		Handle: passwordResetRequest,
 	}).Methods(http.MethodPost)
 
@@ -53,24 +54,26 @@
 	}).Methods(http.MethodGet)
 
 	// External proxies.
-	proxy := &httputil.ReverseProxy{
-		Director:       proxyDirector(findProxy("external")),
+	external := &httputil.ReverseProxy{
+		Director:       proxyDirector(models.ExternalServices.Find),
 		ModifyResponse: proxyModifyResponse("/api/external/"),
 	}
 
-	api.Handle("/external/{hash}/{url}", proxy).
+	externalAuth := all(external)
+
+	api.Handle("/external/{hash}/{url}", externalAuth).
 		Methods(
 			http.MethodGet, http.MethodPost,
 			http.MethodPut, http.MethodDelete)
 
-	api.Handle("/external/{entry}", proxy).
+	api.Handle("/external/{entry}", externalAuth).
 		Methods(
 			http.MethodGet, http.MethodPost,
 			http.MethodPut, http.MethodDelete)
 
 	// Internal proxies.
 	internal := &httputil.ReverseProxy{
-		Director:       proxyDirector(findProxy("internal")),
+		Director:       proxyDirector(models.PublishedServices.Find),
 		ModifyResponse: proxyModifyResponse("/api/internal/"),
 	}
 
--- a/pkg/controllers/token.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/controllers/token.go	Wed Aug 22 10:04:52 2018 +0200
@@ -7,6 +7,7 @@
 	"net/http"
 
 	"gemma.intevation.de/gemma/pkg/auth"
+	"gemma.intevation.de/gemma/pkg/models"
 )
 
 func sendJSON(rw http.ResponseWriter, data interface{}) {
@@ -63,7 +64,7 @@
 		password = req.FormValue("password")
 	)
 
-	if !UserName(user).isValid() || password == "" {
+	if !models.UserName(user).IsValid() || password == "" {
 		http.Error(rw, "Invalid credentials", http.StatusBadRequest)
 		return
 	}
--- a/pkg/controllers/types.go	Wed Aug 22 10:04:04 2018 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,182 +0,0 @@
-package controllers
-
-import (
-	"database/sql/driver"
-	"encoding/json"
-	"errors"
-	"regexp"
-	"strings"
-)
-
-type (
-	Email    string
-	Country  string
-	Role     string
-	UserName string
-
-	BoundingBox struct {
-		X1 float64 `json:"x1"`
-		Y1 float64 `json:"y1"`
-		X2 float64 `json:"x2"`
-		Y2 float64 `json:"y2"`
-	}
-
-	User struct {
-		User     UserName     `json:"user"`
-		Role     Role         `json:"role"`
-		Password string       `json:"password,omitempty"`
-		Email    Email        `json:"email"`
-		Country  Country      `json:"country"`
-		Extent   *BoundingBox `json:"extent"`
-	}
-
-	PWResetUser struct {
-		User string `json:"user"`
-	}
-)
-
-var (
-	// https://stackoverflow.com/questions/201323/how-to-validate-an-email-address-using-a-regular-expression
-	emailRe = regexp.MustCompile(
-		`(?:[a-z0-9!#$%&'*+/=?^_` + "`" +
-			`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_` + "`" +
-			`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]` +
-			`|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")` +
-			`@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?` +
-			`|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}` +
-			`(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]` +
-			`:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]` +
-			`|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])`)
-
-	errNoEmailAddress = errors.New("Not a valid email address")
-	errNoString       = errors.New("Not a string")
-)
-
-func (e *Email) UnmarshalJSON(data []byte) error {
-	var s string
-	if err := json.Unmarshal(data, &s); err != nil {
-		return err
-	}
-	if !emailRe.MatchString(s) {
-		return errNoEmailAddress
-	}
-	*e = Email(s)
-	return nil
-}
-
-func (e Email) Value() (driver.Value, error) {
-	return string(e), nil
-}
-
-func (e *Email) Scan(src interface{}) (err error) {
-	if s, ok := src.(string); ok {
-		*e = Email(s)
-	} else {
-		err = errNoString
-	}
-	return
-}
-
-var errNoValidUser = errors.New("Not a valid user")
-
-func (u UserName) isValid() bool {
-	return u != "" && !strings.ContainsAny(string(u), `\"':;`)
-}
-
-func (u *UserName) UnmarshalJSON(data []byte) error {
-	var s string
-	if err := json.Unmarshal(data, &s); err != nil {
-		return err
-	}
-	if !emailRe.MatchString(s) {
-		return errNoEmailAddress
-	}
-	user := UserName(s)
-	if !user.isValid() {
-		return errNoValidUser
-	}
-	*u = user
-	return nil
-}
-
-func (u *UserName) Scan(src interface{}) (err error) {
-	if s, ok := src.(string); ok {
-		*u = UserName(s)
-	} else {
-		err = errNoString
-	}
-	return
-}
-
-var (
-	validCountries = []string{
-		"AT", "BG", "DE", "HU", "HR",
-		"MD", "RO", "RS", "SK", "UA",
-	}
-	errNoValidCountry = errors.New("Not a valid country")
-)
-
-func (c *Country) UnmarshalJSON(data []byte) error {
-	var s string
-	if err := json.Unmarshal(data, &s); err != nil {
-		return err
-	}
-	s = strings.ToUpper(s)
-	for _, v := range validCountries {
-		if v == s {
-			*c = Country(v)
-			return nil
-		}
-	}
-	return errNoValidCountry
-}
-
-func (c Country) Value() (driver.Value, error) {
-	return string(c), nil
-}
-
-func (c *Country) Scan(src interface{}) (err error) {
-	if s, ok := src.(string); ok {
-		*c = Country(s)
-	} else {
-		err = errNoString
-	}
-	return
-}
-
-var (
-	validRoles = []string{
-		"waterway_user",
-		"waterway_admin",
-		"sys_admin",
-	}
-	errNoValidRole = errors.New("Not a valid role")
-)
-
-func (r Role) Value() (driver.Value, error) {
-	return string(r), nil
-}
-
-func (r *Role) Scan(src interface{}) (err error) {
-	if s, ok := src.(string); ok {
-		*r = Role(s)
-	} else {
-		err = errNoString
-	}
-	return
-}
-
-func (r *Role) UnmarshalJSON(data []byte) error {
-	var s string
-	if err := json.Unmarshal(data, &s); err != nil {
-		return err
-	}
-	s = strings.ToLower(s)
-	for _, v := range validRoles {
-		if v == s {
-			*r = Role(v)
-			return nil
-		}
-	}
-	return errNoValidRole
-}
--- a/pkg/controllers/user.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/controllers/user.go	Wed Aug 22 10:04:52 2018 +0200
@@ -8,6 +8,7 @@
 	"github.com/gorilla/mux"
 
 	"gemma.intevation.de/gemma/pkg/auth"
+	"gemma.intevation.de/gemma/pkg/models"
 )
 
 const (
@@ -57,7 +58,7 @@
 ) (jr JSONResult, err error) {
 
 	user := mux.Vars(req)["user"]
-	if !UserName(user).isValid() {
+	if !models.UserName(user).IsValid() {
 		err = JSONError{http.StatusBadRequest, "error: user invalid"}
 		return
 	}
@@ -94,13 +95,13 @@
 	db *sql.DB,
 ) (jr JSONResult, err error) {
 
-	user := UserName(mux.Vars(req)["user"])
-	if !user.isValid() {
+	user := models.UserName(mux.Vars(req)["user"])
+	if !user.IsValid() {
 		err = JSONError{http.StatusBadRequest, "error: user invalid"}
 		return
 	}
 
-	newUser := input.(*User)
+	newUser := input.(*models.User)
 	var res sql.Result
 
 	if s, _ := auth.GetSession(req); s.Roles.Has("sys_admin") {
@@ -173,7 +174,7 @@
 	db *sql.DB,
 ) (jr JSONResult, err error) {
 
-	user := input.(*User)
+	user := input.(*models.User)
 
 	if user.Extent == nil {
 		_, err = db.Exec(
@@ -223,10 +224,10 @@
 	}
 	defer rows.Close()
 
-	var users []*User
+	var users []*models.User
 
 	for rows.Next() {
-		user := &User{Extent: &BoundingBox{}}
+		user := &models.User{Extent: &models.BoundingBox{}}
 		if err = rows.Scan(
 			&user.Role,
 			&user.User,
@@ -242,7 +243,7 @@
 
 	jr = JSONResult{
 		Result: struct {
-			Users []*User `json:"users"`
+			Users []*models.User `json:"users"`
 		}{users},
 	}
 	return
@@ -253,15 +254,15 @@
 	db *sql.DB,
 ) (jr JSONResult, err error) {
 
-	user := UserName(mux.Vars(req)["user"])
-	if !user.isValid() {
+	user := models.UserName(mux.Vars(req)["user"])
+	if !user.IsValid() {
 		err = JSONError{http.StatusBadRequest, "error: user invalid"}
 		return
 	}
 
-	result := &User{
+	result := &models.User{
 		User:   user,
-		Extent: &BoundingBox{},
+		Extent: &models.BoundingBox{},
 	}
 
 	err = db.QueryRow(listUserSQL, user).Scan(
--- a/pkg/middleware/modifyquery.go	Wed Aug 22 10:04:04 2018 +0200
+++ b/pkg/middleware/modifyquery.go	Wed Aug 22 10:04:52 2018 +0200
@@ -81,7 +81,7 @@
 	parameters.Del("env")
 
 	session, ok := auth.GetSession(req)
-	if ok && !strings.ContainsAny(session.User, `\"':;`) {
+	if ok && !strings.ContainsAny(session.User, auth.InvalidRoleCharacters) {
 		log.Printf("Injecting user %s\n", session.User)
 		parameters.Set("env", "user:"+session.User)
 	}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/models/extservices.go	Wed Aug 22 10:04:52 2018 +0200
@@ -0,0 +1,96 @@
+package models
+
+import (
+	"database/sql"
+	"log"
+	"sort"
+	"sync"
+
+	"gemma.intevation.de/gemma/pkg/auth"
+)
+
+type ExtEntry struct {
+	Name string
+	URL  string
+	WFS  bool
+}
+
+type ExtServices struct {
+	mu      sync.Mutex
+	entries []ExtEntry
+}
+
+var ExternalServices = &ExtServices{}
+
+const selectExternalServices = `SELECT local_name, remote_url, is_wfs
+FROM sys_admin.external_services ORDER BY local_name`
+
+func (es *ExtServices) Find(name string) (string, bool) {
+	es.mu.Lock()
+	defer es.mu.Unlock()
+
+	if es.entries == nil {
+		if err := es.load(); err != nil {
+			log.Printf("error: %v\n", err)
+			return "", false
+		}
+	}
+	n := sort.Search(len(es.entries), func(i int) bool {
+		return es.entries[i].Name >= name
+	})
+	if n == len(es.entries) || es.entries[n].Name != name {
+		return "", false
+	}
+	return es.entries[n].URL, true
+}
+
+func (es *ExtServices) load() error {
+	// make empty slice to prevent retry if slice is empty.
+	es.entries = []ExtEntry{}
+	return auth.RunAs("sys_admin", func(db *sql.DB) error {
+		rows, err := db.Query(selectExternalServices)
+		if err != nil {
+			return err
+		}
+		defer rows.Close()
+		for rows.Next() {
+			var entry ExtEntry
+			if err := rows.Scan(
+				&entry.Name,
+				&entry.URL,
+				&entry.WFS,
+			); err != nil {
+				return err
+			}
+			es.entries = append(es.entries, entry)
+		}
+		return rows.Err()
+	})
+}
+
+func (es *ExtServices) Invalidate() {
+	es.mu.Lock()
+	es.entries = nil
+	es.mu.Unlock()
+}
+
+func ExternalWMS(entry PubEntry) bool { return !entry.WFS }
+func ExternalWFS(entry PubEntry) bool { return entry.WFS }
+
+func (es *ExtServices) Filter(accept func(ExtEntry) bool) []ExtEntry {
+	es.mu.Lock()
+	defer es.mu.Unlock()
+	if es.entries == nil {
+		if err := es.load(); err != nil {
+			log.Printf("error: %v\n", err)
+			return nil
+		}
+	}
+	ee := make([]ExtEntry, 0, len(es.entries))
+	for _, e := range es.entries {
+		if accept(e) {
+			ee = append(ee, e)
+		}
+	}
+	return ee
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/models/pubservices.go	Wed Aug 22 10:04:52 2018 +0200
@@ -0,0 +1,99 @@
+package models
+
+import (
+	"database/sql"
+	"log"
+	"sort"
+	"sync"
+
+	"gemma.intevation.de/gemma/pkg/auth"
+)
+
+type PubEntry struct {
+	Name  string
+	Style sql.NullString
+	WMS   bool
+	WFS   bool
+}
+
+type PubServices struct {
+	entries []PubEntry
+	mu      sync.Mutex
+}
+
+const selectPublishedServices = `SELECT name, style, as_wms, as_wfs
+FROM sys_admin.published_services ORDER by name`
+
+var PublishedServices = &PubServices{}
+
+func (ps *PubServices) Find(name string) (string, bool) {
+	ps.mu.Lock()
+	defer ps.mu.Unlock()
+
+	if ps.entries == nil {
+		if err := ps.load(); err != nil {
+			log.Printf("error: %v\n", err)
+			return "", false
+		}
+	}
+
+	n := sort.Search(len(ps.entries), func(i int) bool {
+		return ps.entries[i].Name >= name
+	})
+	if n == len(ps.entries) || ps.entries[n].Name != name {
+		return "", false
+	}
+	return name, true
+}
+
+func (ps *PubServices) load() error {
+	// make empty slice to prevent retry if slice is empty.
+	ps.entries = []PubEntry{}
+	return auth.RunAs("sys_admin", func(db *sql.DB) error {
+		rows, err := db.Query(selectPublishedServices)
+		if err != nil {
+			return err
+		}
+		defer rows.Close()
+		for rows.Next() {
+			var entry PubEntry
+			if err := rows.Scan(
+				&entry.Name, &entry.Style,
+				&entry.WFS, &entry.WFS,
+			); err != nil {
+				return err
+			}
+			ps.entries = append(ps.entries, entry)
+		}
+		return rows.Err()
+	})
+	return nil
+}
+
+func (ps *PubServices) Invalidate() {
+	ps.mu.Lock()
+	ps.entries = nil
+	ps.mu.Unlock()
+}
+
+func PublishedWMS(entry PubEntry) bool { return entry.WMS }
+func PublishedWFS(entry PubEntry) bool { return entry.WFS }
+
+func (ps *PubServices) Filter(accept func(PubEntry) bool) []PubEntry {
+	ps.mu.Lock()
+	defer ps.mu.Unlock()
+	if ps.entries == nil {
+		if err := ps.load(); err != nil {
+			log.Printf("error: %v\n", err)
+			return nil
+		}
+	}
+	pe := make([]PubEntry, 0, len(ps.entries))
+	for _, e := range ps.entries {
+		if accept(e) {
+			pe = append(pe, e)
+		}
+	}
+
+	return pe
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/models/types.go	Wed Aug 22 10:04:52 2018 +0200
@@ -0,0 +1,185 @@
+package models
+
+import (
+	"database/sql/driver"
+	"encoding/json"
+	"errors"
+	"regexp"
+	"strings"
+
+	"gemma.intevation.de/gemma/pkg/auth"
+)
+
+type (
+	Email    string
+	Country  string
+	Role     string
+	UserName string
+
+	BoundingBox struct {
+		X1 float64 `json:"x1"`
+		Y1 float64 `json:"y1"`
+		X2 float64 `json:"x2"`
+		Y2 float64 `json:"y2"`
+	}
+
+	User struct {
+		User     UserName     `json:"user"`
+		Role     Role         `json:"role"`
+		Password string       `json:"password,omitempty"`
+		Email    Email        `json:"email"`
+		Country  Country      `json:"country"`
+		Extent   *BoundingBox `json:"extent"`
+	}
+
+	PWResetUser struct {
+		User string `json:"user"`
+	}
+)
+
+var (
+	// https://stackoverflow.com/questions/201323/how-to-validate-an-email-address-using-a-regular-expression
+	emailRe = regexp.MustCompile(
+		`(?:[a-z0-9!#$%&'*+/=?^_` + "`" +
+			`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_` + "`" +
+			`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]` +
+			`|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")` +
+			`@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?` +
+			`|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}` +
+			`(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]` +
+			`:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]` +
+			`|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])`)
+
+	errNoEmailAddress = errors.New("Not a valid email address")
+	errNoString       = errors.New("Not a string")
+)
+
+func (e *Email) UnmarshalJSON(data []byte) error {
+	var s string
+	if err := json.Unmarshal(data, &s); err != nil {
+		return err
+	}
+	if !emailRe.MatchString(s) {
+		return errNoEmailAddress
+	}
+	*e = Email(s)
+	return nil
+}
+
+func (e Email) Value() (driver.Value, error) {
+	return string(e), nil
+}
+
+func (e *Email) Scan(src interface{}) (err error) {
+	if s, ok := src.(string); ok {
+		*e = Email(s)
+	} else {
+		err = errNoString
+	}
+	return
+}
+
+var errNoValidUser = errors.New("Not a valid user")
+
+func (u UserName) IsValid() bool {
+	return u != "" &&
+		!strings.ContainsAny(string(u), auth.InvalidRoleCharacters)
+}
+
+func (u *UserName) UnmarshalJSON(data []byte) error {
+	var s string
+	if err := json.Unmarshal(data, &s); err != nil {
+		return err
+	}
+	if !emailRe.MatchString(s) {
+		return errNoEmailAddress
+	}
+	user := UserName(s)
+	if !user.IsValid() {
+		return errNoValidUser
+	}
+	*u = user
+	return nil
+}
+
+func (u *UserName) Scan(src interface{}) (err error) {
+	if s, ok := src.(string); ok {
+		*u = UserName(s)
+	} else {
+		err = errNoString
+	}
+	return
+}
+
+var (
+	validCountries = []string{
+		"AT", "BG", "DE", "HU", "HR",
+		"MD", "RO", "RS", "SK", "UA",
+	}
+	errNoValidCountry = errors.New("Not a valid country")
+)
+
+func (c *Country) UnmarshalJSON(data []byte) error {
+	var s string
+	if err := json.Unmarshal(data, &s); err != nil {
+		return err
+	}
+	s = strings.ToUpper(s)
+	for _, v := range validCountries {
+		if v == s {
+			*c = Country(v)
+			return nil
+		}
+	}
+	return errNoValidCountry
+}
+
+func (c Country) Value() (driver.Value, error) {
+	return string(c), nil
+}
+
+func (c *Country) Scan(src interface{}) (err error) {
+	if s, ok := src.(string); ok {
+		*c = Country(s)
+	} else {
+		err = errNoString
+	}
+	return
+}
+
+var (
+	validRoles = []string{
+		"waterway_user",
+		"waterway_admin",
+		"sys_admin",
+	}
+	errNoValidRole = errors.New("Not a valid role")
+)
+
+func (r Role) Value() (driver.Value, error) {
+	return string(r), nil
+}
+
+func (r *Role) Scan(src interface{}) (err error) {
+	if s, ok := src.(string); ok {
+		*r = Role(s)
+	} else {
+		err = errNoString
+	}
+	return
+}
+
+func (r *Role) UnmarshalJSON(data []byte) error {
+	var s string
+	if err := json.Unmarshal(data, &s); err != nil {
+		return err
+	}
+	s = strings.ToLower(s)
+	for _, v := range validRoles {
+		if v == s {
+			*r = Role(v)
+			return nil
+		}
+	}
+	return errNoValidRole
+}
--- a/schema/gemma.sql	Wed Aug 22 10:04:04 2018 +0200
+++ b/schema/gemma.sql	Wed Aug 22 10:04:52 2018 +0200
@@ -55,8 +55,24 @@
         config_key varchar PRIMARY KEY,
         config_val varchar
     )
+
+    CREATE TABLE sys_admin.external_services (
+        local_name varchar PRIMARY KEY,
+        remote_url varchar NOT NULL,
+        is_wfs     boolean NOT NULL DEFAULT TRUE
+    )
+
+    CREATE TABLE sys_admin.published_services (
+        name varchar PRIMARY KEY,
+        style bytea,
+        as_wms boolean NOT NULL DEFAULT TRUE,
+        as_wfs boolean NOT NULL DEFAULT TRUE
+    )
 ;
 
+-- Tables with geo data to be published with GeoServer.
+INSERT INTO sys_admin.published_services (name) VALUES ('fairway_dimensions');
+
 --
 -- Look-up tables with data that are static in a running system
 --
--- a/schema/install-db.sh	Wed Aug 22 10:04:04 2018 +0200
+++ b/schema/install-db.sh	Wed Aug 22 10:04:52 2018 +0200
@@ -128,12 +128,9 @@
   read a
   if [[ $a == "yes" ]] ; then
     dropdb -p "$port" "$db"
-    for r in `psql -p $port -t -c '\du' | awk -F '|' \
-          '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|pw_reset/ \
-           {print $1}'`
-    do
-      dropuser -p "$port" "$r"
-    done
+    psql -p $port -A -t -c '\du' | awk -F '|' -v port=$port \
+        '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|pw_reset/ \
+	    { system("dropuser -p " port " \"" $1 "\"") }'
   else
     echo "No harm done."
   fi