Thread: help about the function
Hello!
I have implemented solution for enabling regular user (from group "ADMINS") to create new users in predefined groups, by your modified function:
CREATE OR REPLACE FUNCTION "public"."alter_group" (name, boolean, name, varchar, timestamp) RETURNS boolean AS
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity ALIAS FOR $5;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity ALIAS FOR $5;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
Validity is set in the table public."VALIDITY" in the field "VALIDITY" timestamp. There is also a view called "VALIDITY_VIEW" which reads the actual validity value from the table. It returns only one row-one field:
CREATE OR REPLACE VIEW "VALIDITY_VIEW" AS
SELECT "VALIDITY"."VALIDITY"
FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY"
FROM "VALIDITY"
GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
ALTER TABLE "VALIDITY_VIEW" OWNER TO zmatic;
MS Access front-end calls this function through VBA code. Access creates a recordset on the base of the VALIDITY_VIEW to inspect the value of validity time, assigns it to a variable and then passes as the parameter l_validity to the server function "alter_group". It works nice, but I realized that it is not safe, because someone from the group "ADMINS" could create its own query in Access with different "validity" and execute it without restriction. Therefore I think that "l_validity" timestamp ($5) should not be input parameter for the function "alter_group", but rather declared variable that reads the value of validity from the table directly.
I tried to modify the function into something like this:
CREATE OR REPLACE FUNCTION "public"."alter_group" (name, boolean, name, varchar) RETURNS boolean AS
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity timestamp;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity timestamp;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
l_validity := SELECT "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
..but it seems that I can't assign the value returned by query to l_validity directly (I could do it in Access by recordset)...How to modify the following in order to work ?
l_validity := SELECT "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
Thank you in advance.
Zlatko
Hi,
Why not :
SELECT INTO l_validity "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
Regards,
Patrick
-------------------------------------------------------------------------------------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
-------------------------------------------------------------------------------------------
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Zlatko Matić
Sent: jeudi 23 juin 2005 09:59
To: btober@seaworthysys.com
Cc: pgsql-general@postgresql.org
Subject: [GENERAL] help about the function
Importance: HighHello!I have implemented solution for enabling regular user (from group "ADMINS") to create new users in predefined groups, by your modified function:CREATE OR REPLACE FUNCTION "public"."alter_group" (name, boolean, name, varchar, timestamp) RETURNS boolean AS
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity ALIAS FOR $5;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;Validity is set in the table public."VALIDITY" in the field "VALIDITY" timestamp. There is also a view called "VALIDITY_VIEW" which reads the actual validity value from the table. It returns only one row-one field:
CREATE OR REPLACE VIEW "VALIDITY_VIEW" AS
SELECT "VALIDITY"."VALIDITY"
FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY"
FROM "VALIDITY"
GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;ALTER TABLE "VALIDITY_VIEW" OWNER TO zmatic;MS Access front-end calls this function through VBA code. Access creates a recordset on the base of the VALIDITY_VIEW to inspect the value of validity time, assigns it to a variable and then passes as the parameter l_validity to the server function "alter_group". It works nice, but I realized that it is not safe, because someone from the group "ADMINS" could create its own query in Access with different "validity" and execute it without restriction. Therefore I think that "l_validity" timestamp ($5) should not be input parameter for the function "alter_group", but rather declared variable that reads the value of validity from the table directly.I tried to modify the function into something like this:CREATE OR REPLACE FUNCTION "public"."alter_group" (name, boolean, name, varchar) RETURNS boolean AS
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity timestamp;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGINl_validity := SELECT "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;..but it seems that I can't assign the value returned by query to l_validity directly (I could do it in Access by recordset)...How to modify the following in order to work ?l_validity := SELECT "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;Thank you in advance.Zlatko
Great! It works.
Thanks.
----- Original Message -----Sent: Thursday, June 23, 2005 10:18 AMSubject: Re: [GENERAL] help about the functionHi,Why not :SELECT INTO l_validity "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;Regards,Patrick-------------------------------------------------------------------------------------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
------------------------------------------------------------------------------------------------Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Zlatko Matić
Sent: jeudi 23 juin 2005 09:59
To: btober@seaworthysys.com
Cc: pgsql-general@postgresql.org
Subject: [GENERAL] help about the function
Importance: HighHello!I have implemented solution for enabling regular user (from group "ADMINS") to create new users in predefined groups, by your modified function:CREATE OR REPLACE FUNCTION "public"."alter_group" (name, boolean, name, varchar, timestamp) RETURNS boolean AS
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity ALIAS FOR $5;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;Validity is set in the table public."VALIDITY" in the field "VALIDITY" timestamp. There is also a view called "VALIDITY_VIEW" which reads the actual validity value from the table. It returns only one row-one field:
CREATE OR REPLACE VIEW "VALIDITY_VIEW" AS
SELECT "VALIDITY"."VALIDITY"
FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY"
FROM "VALIDITY"
GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;ALTER TABLE "VALIDITY_VIEW" OWNER TO zmatic;MS Access front-end calls this function through VBA code. Access creates a recordset on the base of the VALIDITY_VIEW to inspect the value of validity time, assigns it to a variable and then passes as the parameter l_validity to the server function "alter_group". It works nice, but I realized that it is not safe, because someone from the group "ADMINS" could create its own query in Access with different "validity" and execute it without restriction. Therefore I think that "l_validity" timestamp ($5) should not be input parameter for the function "alter_group", but rather declared variable that reads the value of validity from the table directly.I tried to modify the function into something like this:CREATE OR REPLACE FUNCTION "public"."alter_group" (name, boolean, name, varchar) RETURNS boolean AS
$body$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity timestamp;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGINl_validity := SELECT "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' || l_validity || '''';
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;..but it seems that I can't assign the value returned by query to l_validity directly (I could do it in Access by recordset)...How to modify the following in order to work ?l_validity := SELECT "VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;Thank you in advance.Zlatko