75 lines
2.9 KiB
PL/PgSQL
Executable File
75 lines
2.9 KiB
PL/PgSQL
Executable File
-- # $Id$
|
|
-- # $HeadURL$
|
|
-- # $Author$
|
|
|
|
------------------------
|
|
-- Create user procedure
|
|
------------------------
|
|
DROP FUNCTION IF EXISTS create_user_if_not_exists(text);
|
|
DROP FUNCTION IF EXISTS create_user_if_not_exists(text, text);
|
|
CREATE OR REPLACE FUNCTION create_user_if_not_exists(rolename text, passw text default 'ets11de') RETURNS TEXT AS
|
|
$$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN
|
|
EXECUTE format('CREATE USER %s WITH PASSWORD ''%s''', rolename, passw);
|
|
EXECUTE format('GRANT ets_ro TO %s', rolename);
|
|
RETURN 'CREATE USER';
|
|
ELSE
|
|
RETURN format('USER ''%I'' ALREADY EXISTS', rolename);
|
|
END IF;
|
|
END;
|
|
$$
|
|
LANGUAGE plpgsql;
|
|
|
|
-- Create db users and roles
|
|
CREATE ROLE ets_ro WITH PASSWORD 'ets11de';
|
|
-- SELECT create_user_if_not_exists('ets_ro');
|
|
SELECT create_user_if_not_exists('ets_admin');
|
|
SELECT create_user_if_not_exists('ets_aes');
|
|
SELECT create_user_if_not_exists('ets_asm');
|
|
SELECT create_user_if_not_exists('ets_avm');
|
|
SELECT create_user_if_not_exists('ets_bdv');
|
|
SELECT create_user_if_not_exists('ets_core');
|
|
SELECT create_user_if_not_exists('ets_exm');
|
|
SELECT create_user_if_not_exists('ets_flm');
|
|
SELECT create_user_if_not_exists('ets_flt');
|
|
SELECT create_user_if_not_exists('ets_gna');
|
|
SELECT create_user_if_not_exists('ets_ism');
|
|
SELECT create_user_if_not_exists('ets_mdm');
|
|
SELECT create_user_if_not_exists('ets_msg');
|
|
SELECT create_user_if_not_exists('ets_nom');
|
|
SELECT create_user_if_not_exists('ets_opm');
|
|
SELECT create_user_if_not_exists('ets_pdm');
|
|
SELECT create_user_if_not_exists('ets_ppm');
|
|
SELECT create_user_if_not_exists('ets_som');
|
|
SELECT create_user_if_not_exists('ets_srm');
|
|
SELECT create_user_if_not_exists('ets_tab');
|
|
SELECT create_user_if_not_exists('ets_whg');
|
|
-- user for all exercises
|
|
SELECT create_user_if_not_exists('ets_ex_00');
|
|
-- test data (uncomment if necessary)
|
|
-- SELECT create_user_if_not_exists('ets_ex_35');
|
|
-- SELECT create_user_if_not_exists('ets_ex_42');
|
|
|
|
-- grant create role to ets_admin, ets_exm
|
|
ALTER ROLE ets_admin WITH CREATEROLE;
|
|
ALTER ROLE ets_exm WITH CREATEROLE;
|
|
|
|
-- ets_ro for all global users, able to select or reference the other global schemas
|
|
GRANT ets_ro TO ets_admin, ets_core, ets_aes, ets_asm, ets_avm, ets_bdv, ets_exm, ets_flm, ets_flt, ets_gna, ets_ism, ets_mdm, ets_msg, ets_nom, ets_opm, ets_pdm, ets_ppm, ets_som, ets_srm, ets_tab, ets_whg;
|
|
|
|
-- ets_admin able to alter tables that he does not own
|
|
GRANT ets_core, ets_aes, ets_asm, ets_avm, ets_bdv, ets_flm, ets_flt, ets_gna, ets_ism, ets_mdm, ets_msg, ets_nom, ets_opm, ets_pdm, ets_ppm, ets_som, ets_srm, ets_tab, ets_whg TO ets_admin;
|
|
|
|
-- ets_exm super user of DB (same as ets_admin)
|
|
GRANT ets_admin TO ets_exm;
|
|
|
|
-- For Convenience (Backwards Usability)
|
|
CREATE USER ctc PASSWORD 'ctc11de' CREATEROLE;
|
|
CREATE USER ctc_sv PASSWORD 'ctc11de' CREATEROLE;
|
|
CREATE USER ctc_ws PASSWORD 'ctc11de' CREATEROLE;
|
|
|
|
GRANT ets_ro TO ctc, ctc_sv;
|
|
|
|
|