/var/lib/gnumed/server/sql/gmCreateUserFunction.sql is in gnumed-server 16.17-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | -- GNUmed functions to manage database accounts
-- $Source: /home/ncq/Projekte/cvs2git/vcs-mirror/gnumed/gnumed/server/sql/gmCreateUserFunction.sql,v $
-- $Id: gmCreateUserFunction.sql,v 1.5 2006-06-17 16:46:29 ncq Exp $
-- license: GPL v2 or later
-- author: Karsten.Hilbert@gmx.net
-- ===================================================================
\set ON_ERROR_STOP 1
-- ===================================================
create or replace function gm_create_user(name, text)
returns boolean
language 'plpgsql'
security definer
as '
DECLARE
_username alias for $1;
_password alias for $2;
_database text;
_query text;
BEGIN
perform 1 from pg_user where usename = _username;
if not FOUND then
_query := ''create user '' || quote_ident(_username)
|| '' with password '' || quote_literal(_password)
|| '';'';
execute _query;
perform 1 from pg_user where usename = _username;
if not FOUND then
raise exception ''cannot create user [%]'', _username;
return false;
end if;
end if;
_query := ''alter group "gm-logins" add user '' || quote_ident(_username) || '';'';
execute _query;
_query := ''alter group "gm-doctors" add user '' || quote_ident(_username) || '';'';
execute _query;
_query := ''alter group "gm-public" add user '' || quote_ident(_username) || '';'';
execute _query;
-- satisfy "database = samegroup" in pg_hba.conf
select into _database current_database();
_query := ''alter group '' || quote_ident(_database) || '' add user '' || quote_ident(_username) || '';'';
execute _query;
return true;
END;';
revoke all on function gm_create_user(name, text) from public;
grant execute on function gm_create_user(name, text) to "gm-dbo";
comment on function gm_create_user(name, text) is
'To create users one needs to have superuser rights. We do
not want to grant superuser rights to any GNUmed account,
however. Therefore this function is owned by postgres and
is set SECURITY DEFINER. Only gm-dbo is GRANTed EXECUTE.
This way users need to know the gm-dbo (GNUmed admin) password
to execute the function. Later on roles should be used to
limit execution of this function.';
-- ===================================================
create or replace function gm_drop_user(name)
returns boolean
language 'plpgsql'
security definer
as '
DECLARE
_username alias for $1;
_query text;
BEGIN
perform 1 from pg_user where usename = _username;
if not FOUND then
return true;
end if;
_query := ''drop user '' || quote_ident(_username) || '';'';
execute _query;
perform 1 from pg_user where usename = _username;
if FOUND then
return false;
end if;
return true;
END;';
revoke all on function gm_drop_user(name) from public;
grant execute on function gm_drop_user(name) to "gm-dbo";
comment on function gm_drop_user(name) is
'To drop users one needs to have superuser rights. We do
not want to grant superuser rights to any GNUmed account,
however. Therefore this function is owned by postgres and
is set SECURITY DEFINER. Only gm-dbo is GRANTed EXECUTE.
This way users need to know the gm-dbo (GNUmed admin) password
to execute the function. Later on roles should be used to
limit execution of this function.';
-- ===================================================
create or replace function gm_disable_user(name)
returns boolean
language 'plpgsql'
security definer
as '
DECLARE
_username alias for $1;
_query text;
BEGIN
perform 1 from pg_user where usename = _username;
if not FOUND then
return true;
end if;
_query := ''alter group "gm-logins" drop user '' || quote_ident(_username) || '';'';
execute _query;
return true;
END;';
revoke all on function gm_disable_user(name) from public;
grant execute on function gm_disable_user(name) to "gm-dbo";
comment on function gm_disable_user(name) is
'To disable users one needs to have superuser rights. We do
not want to grant superuser rights to any GNUmed account,
however. Therefore this function is owned by postgres and
is set SECURITY DEFINER. Only gm-dbo is GRANTed EXECUTE.
This way users need to know the gm-dbo (GNUmed admin) password
to execute the function. Later on roles should be used to
limit execution of this function.';
-- ===================================================
--select log_script_insertion('$RCSfile: gmCreateUserFunction.sql,v $', '$Revision: 1.5 $');
-- ===================================================
-- $Log: gmCreateUserFunction.sql,v $
-- Revision 1.5 2006-06-17 16:46:29 ncq
-- - make gm_create_user() more useful: enable accounts if account pre-exists
--
-- Revision 1.4 2006/06/06 20:57:29 ncq
-- - add gm_disable_user()
--
-- Revision 1.3 2006/04/23 15:15:20 ncq
-- - add comments, all this should actually be renamed to *_role and gmManageRoleFunctions.sql
-- - add gm_drop_user()
--
-- Revision 1.2 2006/02/02 16:20:08 ncq
-- - streamline gm_create_user() and enable proper insertion
-- into database authentication groups
--
-- Revision 1.1 2006/01/28 10:37:11 ncq
-- - allow frontend to create users
--
|