--- a/schema/gemma.sql	Fri Oct 18 12:01:21 2019 +0200
+++ b/schema/gemma.sql	Fri Oct 18 12:04:04 2019 +0200
@@ -440,9 +440,43 @@
     )
     CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+
+    CREATE VIEW users.list_users WITH (security_barrier) AS
+        SELECT
+            r.rolname,
+            p.username,
+            CAST('' AS varchar) AS pw,
+            p.country,
+            p.map_extent,
+            p.email_address
+        FROM internal.user_profiles p
+            JOIN pg_roles u ON p.username = u.rolname
+            JOIN pg_auth_members a ON u.oid = a.member
+            JOIN pg_roles r ON a.roleid = r.oid
+        WHERE p.username = current_user
+            OR pg_has_role('waterway_admin', 'MEMBER')
+                AND p.country = (
+                    SELECT country FROM internal.user_profiles
+                        WHERE username = current_user)
+            OR pg_has_role('sys_admin', 'MEMBER')
 ;
 
 
+--
+-- Functions to be used in DEFAULT expresions
+--
+
+-- Return current_user's country code
+CREATE FUNCTION users.user_country(user_name name DEFAULT current_user)
+    RETURNS internal.user_profiles.country%TYPE
+    AS $$
+        SELECT country FROM users.list_users
+            WHERE username = user_name
+    $$
+    LANGUAGE SQL
+    STABLE PARALLEL SAFE;
+
+
 -- Namespace for waterway data that can change in a running system
 CREATE SCHEMA waterway
 
@@ -589,6 +623,8 @@
             CHECK(ST_IsValid(CAST(area AS geometry))),
         objnam varchar NOT NULL,
         nobjnam varchar,
+        country char(2) NOT NULL REFERENCES countries
+            DEFAULT users.user_country(),
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL,
         staging_done boolean NOT NULL DEFAULT false,