Mercurial > gemma
changeset 361:f5087cebc740
Enforce PostgreSQL identifier length on username
A name longer than 63 bytes will be truncated on role creation,
rendering user_profiles.username and the actual name of the role
out of sync. The new CHECK constraint prevents such situation.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 08 Aug 2018 16:31:23 +0200 |
| parents | 6d145ee0a097 |
| children | 45d4399f6c15 |
| files | schema/gemma.sql schema/manage_users_tests.sql schema/run_tests.sh |
| diffstat | 3 files changed, 12 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Aug 08 15:47:44 2018 +0200 +++ b/schema/gemma.sql Wed Aug 08 16:31:23 2018 +0200 @@ -40,7 +40,8 @@ CREATE SCHEMA internal -- Profile data are only accessible via the view users.list_users. CREATE TABLE user_profiles ( - username varchar PRIMARY KEY, + username varchar PRIMARY KEY CHECK(octet_length(username) <= 63), + -- keep username length compatible with role identifier map_extent box2d NOT NULL, email_address varchar NOT NULL )
--- a/schema/manage_users_tests.sql Wed Aug 08 15:47:44 2018 +0200 +++ b/schema/manage_users_tests.sql Wed Aug 08 16:31:23 2018 +0200 @@ -68,6 +68,15 @@ 23505, NULL, 'No duplicate user name is allowed'); +SELECT throws_ok($$ + INSERT INTO users.list_users VALUES ( + 'waterway_user', + 'Test Nutzer AT, Test User RO, Täst Nützer ÄT, Täst Üser RÖ', + 'secret1$', 'AT', NULL, 'test4') + $$, + 23514, NULL, + 'User name length is restricted to 63 bytes'); + -- Test password policy SELECT throws_ok($$ INSERT INTO users.list_users VALUES (
--- a/schema/run_tests.sh Wed Aug 08 15:47:44 2018 +0200 +++ b/schema/run_tests.sh Wed Aug 08 16:31:23 2018 +0200 @@ -16,7 +16,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(43)' \ + -c 'SELECT plan(44)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'