Mercurial > gemma
comparison schema/gemma.sql @ 4723:baabc2b2f094
Avoid creating user profiles without matching role
The INSTEAD OF triggers on users.list_users did that already, but
profile data coming e.g. via restoring a dump had been added also
if there was no matching database role in the cluster.
This also unifies the errors occuring on creation of users with existing
role names that differed between roles with and without profile before.
Note this is no referential integrity. A dropped role still leaves an
orphaned profile behind.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 17 Oct 2019 18:56:59 +0200 |
parents | 462d8f71da62 |
children | 2440d2f86f4e |
comparison
equal
deleted
inserted
replaced
4722:462d8f71da62 | 4723:baabc2b2f094 |
---|---|
348 | 348 |
349 -- Namespace not to be accessed directly by any user | 349 -- Namespace not to be accessed directly by any user |
350 CREATE SCHEMA internal | 350 CREATE SCHEMA internal |
351 -- Profile data are only accessible via the view users.list_users. | 351 -- Profile data are only accessible via the view users.list_users. |
352 CREATE TABLE user_profiles ( | 352 CREATE TABLE user_profiles ( |
353 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63), | 353 username varchar PRIMARY KEY |
354 CHECK(octet_length(username) <= 63) | |
355 CHECK(to_regrole(quote_ident(username)) IS NOT NULL), | |
354 -- keep username length compatible with role identifier | 356 -- keep username length compatible with role identifier |
355 country char(2) NOT NULL REFERENCES countries, | 357 country char(2) NOT NULL REFERENCES countries, |
356 map_extent box2d NOT NULL, | 358 map_extent box2d NOT NULL, |
357 email_address varchar NOT NULL | 359 email_address varchar NOT NULL |
358 ) | 360 ) |