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

SET search_path TO public, gemma, gemma_waterway, gemma_fairway;

SET SESSION AUTHORIZATION test_admin_at;

SELECT throws_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0')
    $$,
    42501, NULL,
    'Less privileged user cannot call function in schema sys_admin');

SET SESSION AUTHORIZATION test_sys_admin1;

--
-- Role listing
--
SELECT isnt_empty($$
    SELECT * FROM sys_admin.list_users
    $$,
    'List of users can be queried');

--
-- Role creation
--
SELECT lives_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
    $$,
    'New waterway user can be added');

SELECT throws_ok($$
    SELECT sys_admin.create_user(
        'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
    $$,
    42704, NULL,
    'Valid role name has to be provided');

SELECT throws_ok($$
    SELECT sys_admin.create_user(
        '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($$
    SELECT sys_admin.create_user(
        'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
    $$,
    42710, NULL,
    'Reserved role names cannot be used as username');

SELECT throws_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
    $$,
    23505, NULL,
    'No duplicate user name is allowed');

-- Test password policy
SELECT throws_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
    $$,
    '28P01', NULL,
    'Password with less than 8 characters is not accepted');

SELECT throws_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
    $$,
    '28P01', NULL,
    'Password without non-alphanumeric character is not accepted');

SELECT throws_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
    $$,
    '28P01', NULL,
    'Password without digit is not accepted');

--
-- Role update
--
SELECT lives_ok($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
    SELECT sys_admin.update_user('test2',
        'waterway_user', 'test2_new', 'new_secret1$', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test5')
    $$,
    'Existing user can be updated');

SELECT throws_ok($$
    SELECT sys_admin.update_user('test_non_existent',
        'waterway_user', 'test_non_existent', '', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test5')
    $$,
    42704, NULL,
    'Non-existent user cannot be updated');

SELECT throws_ok($$
    SELECT sys_admin.update_user(CAST(current_user AS varchar),
        'waterway_user', 'test_new_name', 'secret1$', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test6')
    $$,
    '0A000', NULL,
    'Name of current user cannot be altered');

SELECT throws_ok($$
    SELECT sys_admin.update_user('test_user_at',
        'invalid', 'test2', 'secret1$', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test2')
    $$,
    42704, NULL,
    'Valid role name has to be provided');

SELECT throws_ok($$
    SELECT sys_admin.update_user('test_user_at',
        'waterway_user', NULL, 'secret1$', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test3')
    $$,
    23502, NULL,
    'New username is mandatory');
-- Though other arguments are mandatory, too, there are no explicit tests

SELECT throws_ok($$
    SELECT sys_admin.update_user('test_user_at',
        'waterway_user', 'waterway_user', 'secret1$', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test4')
    $$,
    42710, NULL,
    'Reserved role names cannot be used as username');

SELECT throws_ok($$
    SELECT sys_admin.update_user('test_user_at',
        'waterway_user', 'test_user_ro', 'secret1$', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test4')
    $$,
    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($$
    SELECT sys_admin.update_user('test_user_at',
        'waterway_user', 'test_user_at', 'secret', 'AT',
        (SELECT map_extent FROM users.user_profiles
            WHERE username = 'test_user_at'), 'test4')
    $$,
    '28P01', NULL,
    'Non-compliant password is not accepted');

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

CREATE TEMP TABLE old_pw_hash AS
    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';

SELECT sys_admin.update_user('test_user_at',
    'waterway_user', 'test_user_at', NULL, 'AT',
    (SELECT map_extent FROM users.user_profiles
        WHERE username = 'test_user_at'), 'xxx');
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');

SELECT sys_admin.update_user('test_user_at',
    'waterway_user', 'test_user_at', '', 'AT',
    (SELECT map_extent FROM users.user_profiles
        WHERE username = 'test_user_at'), 'xxx');
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');

SELECT sys_admin.update_user('test_user_at',
    'waterway_user', 'test_user_at', 'new_pw1$', 'AT',
    (SELECT map_extent FROM users.user_profiles
        WHERE username = 'test_user_at'), 'xxx');
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($$
    SELECT sys_admin.create_user(
        'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
    SELECT sys_admin.delete_user('test3')
    $$,
    'Existing user can be deleted');

SELECT throws_ok($$
    SELECT sys_admin.delete_user('test_non_existent')
    $$,
    42704, NULL,
    'Non-existent user cannot be deleted');

SELECT throws_ok($$
    SELECT sys_admin.delete_user(CAST(current_user AS varchar))
    $$,
    55006, NULL,
    'Current user cannot be deleted');