--
-- pgTAP test script for user management functions
--

SET search_path TO public, gemma, gemma_waterway, gemma_fairway;

--
-- Role listing
--
SET SESSION AUTHORIZATION test_user_at;
SELECT results_eq($$
    SELECT username FROM users.list_users
    $$,
    $$
    SELECT CAST(current_user AS varchar)
    $$,
    'User should only see his own profile');

SET SESSION AUTHORIZATION test_admin_at;
SELECT set_eq($$
    SELECT DISTINCT country FROM users.list_users
    $$,
    ARRAY['AT'],
    'Waterway admin should only see profiles of his country');

SET SESSION AUTHORIZATION test_sys_admin1;
SELECT set_eq($$
    SELECT count(*) FROM users.list_users
    $$,
    ARRAY[4],
    'System admin can see all users');

--
-- Role creation
--
SELECT lives_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
    $$,
    'New waterway user can be added');

SELECT throws_ok($$
    INSERT INTO users.list_users VALUES (
        'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
    $$,
    42704, NULL,
    'Valid role name has to be provided');

SELECT throws_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
    $$,
    23502, NULL,
    'username is mandatory');
-- Though other arguments are mandatory, too, there are no explicit tests

SELECT throws_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
    $$,
    42710, NULL,
    'Reserved role names cannot be used as username');

SELECT throws_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
    $$,
    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 (
        'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
    $$,
    '28P01', NULL,
    'Password with less than 8 characters is not accepted');

SELECT throws_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
    $$,
    '28P01', NULL,
    'Password without non-alphanumeric character is not accepted');

SELECT throws_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
    $$,
    '28P01', NULL,
    'Password without digit is not accepted');

--
-- Role update
--

SET SESSION AUTHORIZATION test_user_at;

SELECT results_eq($$
    UPDATE users.list_users
        SET (pw, map_extent, email_address)
            = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
        RETURNING username
    $$,
    $$
    SELECT CAST('test_user_at' AS varchar)
    $$,
    'Waterway user can update own password, map extent and email address');

SELECT throws_ok($$
    UPDATE users.list_users
        SET username = 'test_rename', rolname = 'test'
    $$,
    42501, NULL,
    'Waterway user cannot update arbitrary user attributes');

SET SESSION AUTHORIZATION test_admin_at;

SELECT results_eq($$
    UPDATE users.list_users
        SET (pw, map_extent, email_address)
            = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
        WHERE country = users.current_user_country()
            AND username <> current_user
        RETURNING *
    $$,
    $$
    SELECT '' WHERE false -- Empty result set
    $$,
    'Waterway admin cannot update attributes of other users in country');

-- The above test will pass even if the password is actually updated in case
-- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed.
RESET SESSION AUTHORIZATION;
CREATE TEMP TABLE old_pw_hash AS
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
SET SESSION AUTHORIZATION test_admin_at;
UPDATE users.list_users
    SET pw = 'test_user_at2!'
    WHERE username = 'test_user_at';
RESET SESSION AUTHORIZATION;
SELECT set_eq($$
    SELECT rolpassword FROM old_pw_hash
    $$,
    $$
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
    $$,
    'Waterway admin cannot update password of other users in country');


SET SESSION AUTHORIZATION test_sys_admin1;

SELECT lives_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test5')
        WHERE username = 'test2'
    $$,
    'Existing user can be updated');

SELECT throws_ok($$
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test6')
        WHERE username = CAST(current_user AS varchar)
    $$,
    '0A000', NULL,
    'Name of current user cannot be altered');

SELECT throws_ok($$
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('invalid', 'test2', 'secret1$', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test2')
        WHERE username = 'test_user_at'
    $$,
    42704, NULL,
    'Valid role name has to be provided');

SELECT throws_ok($$
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('waterway_user', NULL, 'secret1$', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test3')
        WHERE username = 'test_user_at'
    $$,
    23502, NULL,
    'New username is mandatory');
-- Though other arguments are mandatory, too, there are no explicit tests

SELECT throws_ok($$
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test4')
        WHERE username = 'test_user_at'
    $$,
    42710, NULL,
    'Reserved role names cannot be used as username');

SELECT throws_ok($$
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test4')
        WHERE username = 'test_user_at'
    $$,
    23505, NULL,
    'No duplicate user name is allowed');

-- Test password policy (only one rule to ensure it's also used on update)
SELECT throws_ok($$
    UPDATE users.list_users
        SET (rolname, username, pw, country, map_extent, email_address)
            = ('waterway_user', 'test_user_at', 'secret', 'AT',
                (SELECT map_extent FROM users.list_users
                    WHERE username = 'test_user_at'), 'test4')
        WHERE username = 'test_user_at'
    $$,
    '28P01', NULL,
    'Non-compliant password is not accepted');

-- To compare passwords, we need to run the following tests as superuser
RESET SESSION AUTHORIZATION;

UPDATE old_pw_hash SET rolpassword = (
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at');

UPDATE users.list_users
    SET (rolname, username, pw, country, map_extent, email_address)
        = ('waterway_user', 'test_user_at', NULL, 'AT',
            (SELECT map_extent FROM internal.user_profiles
                WHERE username = 'test_user_at'), 'xxx')
    WHERE username = 'test_user_at';
SELECT set_eq($$
    SELECT rolpassword FROM old_pw_hash
    $$,
    $$
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
    $$,
    'Giving NULL password does not change password');

UPDATE users.list_users
    SET (rolname, username, pw, country, map_extent, email_address)
        = ('waterway_user', 'test_user_at', '', 'AT',
            (SELECT map_extent FROM internal.user_profiles
                WHERE username = 'test_user_at'), 'xxx')
    WHERE username = 'test_user_at';
SELECT set_eq($$
    SELECT rolpassword FROM old_pw_hash
    $$,
    $$
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
    $$,
    'Giving empty string as password does not change password');

UPDATE users.list_users
    SET (rolname, username, pw, country, map_extent, email_address)
        = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
            (SELECT map_extent FROM internal.user_profiles
                WHERE username = 'test_user_at'), 'xxx')
    WHERE username = 'test_user_at';
SELECT set_ne($$
    SELECT rolpassword FROM old_pw_hash
    $$,
    $$
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
    $$,
    'Giving a non-empty password string changes password');

SET SESSION AUTHORIZATION test_sys_admin1;

--
-- Role deletion
--
-- Note: backend termination is not tested in the following.
-- See also comments in function definition.
SELECT lives_ok($$
    INSERT INTO users.list_users VALUES (
        'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
    DELETE FROM users.list_users WHERE username = 'test3'
    $$,
    'Existing user can be deleted');

SELECT throws_ok($$
    DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar)
    $$,
    55006, NULL,
    'Current user cannot be deleted');


--
-- Password reset
--

-- Workaround broken relocatability of pgtap (otherwise we could
-- put pgtap in its own schema and GRANT USAGE to PUBLIC on it)
RESET SESSION AUTHORIZATION;
GRANT USAGE ON SCHEMA public TO pw_reset;

SET SESSION AUTHORIZATION test_pw_reset;

SELECT isnt_empty($$
    SELECT username, email_address FROM pw_reset.list_users
    $$,
    'Special role can see users with their email addresses');

SELECT results_eq($$
    UPDATE pw_reset.list_users
        SET pw = 'user_at2!' WHERE username = 'test_user_at'
        RETURNING email_address
    $$,
    $$
    SELECT email_address FROM pw_reset.list_users
        WHERE username = 'test_user_at'
    $$,
    'Special role can update password');

SELECT throws_ok($$
    UPDATE pw_reset.list_users
        SET username = 'test_rename', email_address = 'test'
    $$,
    42501, NULL,
    'Special role cannot update arbitrary user attributes');