Mercurial > gemma
changeset 247:946baea3d280
Add view to list user profiles with role
To be able to GRANT privileges ON ALL TABLES IN SCHEMA,
use auth.sql last during database setup.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 26 Jul 2018 19:33:42 +0200 |
| parents | 299568ad3c37 |
| children | 21cf31a7d9e1 |
| files | schema/auth.sql schema/install-db.sh schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh |
| diffstat | 5 files changed, 23 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Thu Jul 26 19:22:07 2018 +0200 +++ b/schema/auth.sql Thu Jul 26 19:33:42 2018 +0200 @@ -27,6 +27,7 @@ GRANT INSERT, UPDATE, DELETE ON users.responsibility_areas TO sys_admin; GRANT USAGE ON SCHEMA sys_admin TO sys_admin; +GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin; GRANT UPDATE ON sys_admin.system_config TO sys_admin; --
--- a/schema/install-db.sh Thu Jul 26 19:22:07 2018 +0200 +++ b/schema/install-db.sh Thu Jul 26 19:33:42 2018 +0200 @@ -71,8 +71,11 @@ psql -p "$port" -f "$BASEDIR/roles.sql" createdb -p "$port" "$db" -psql -p "$port" -f "$BASEDIR/gemma.sql" \ - -f "$BASEDIR/auth.sql" -f "$BASEDIR/manage_users.sql" -d "$db" +psql -p "$port" -d "$db" \ + -f "$BASEDIR/gemma.sql" \ + -f "$BASEDIR/manage_users.sql" \ + -f "$BASEDIR/auth.sql" + if [[ $demo -eq 1 ]] ; then psql -p "$port" -f "$BASEDIR/demo-data/responsibility_areas.sql" \ -d "$db"
--- a/schema/manage_users.sql Thu Jul 26 19:22:07 2018 +0200 +++ b/schema/manage_users.sql Thu Jul 26 19:33:42 2018 +0200 @@ -3,6 +3,14 @@ -- exposing it to privileged users -- +CREATE OR REPLACE VIEW sys_admin.list_users AS + SELECT r.rolname, p.* + FROM users.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; + + CREATE OR REPLACE FUNCTION sys_admin.create_user( userrole varchar, username users.user_profiles.username%TYPE,
--- a/schema/manage_users_tests.sql Thu Jul 26 19:22:07 2018 +0200 +++ b/schema/manage_users_tests.sql Thu Jul 26 19:33:42 2018 +0200 @@ -16,6 +16,14 @@ 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($$
--- a/schema/run_tests.sh Thu Jul 26 19:22:07 2018 +0200 +++ b/schema/run_tests.sh Thu Jul 26 19:33:42 2018 +0200 @@ -15,7 +15,7 @@ psql -qXv ON_ERROR_STOP= -v -d gemma_test \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(35)' \ + -c 'SELECT plan(36)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'