PostgreSQL Error 42501: Permission Denied for Sche

Question

I am building a user registration system in ASP.NET with a PostgreSQL database to maintain the user information. As a part of the registration process, the user is sent a confirmation message in which they must click the link to validate their e-mail address. This then takes them to a page where they can create their password.

Once the user provides a password that meets some basic criteria for security, the Web application runs a couple of PostgreSQL stored procedures (functions) to first create the user role in the database and put them into a user group, then create a record in a database with the user's "profile" details (first and last name, e-mail address, etc.).

The first of these stored procedures - the one that creates the user role - runs just fine. Then, the application disconnects from the database and reconnects as the newly created user to run the second stored procedure to create the profile record. However, this is where the application fails, returning the above mentioned error 42501: Permission denied for schema...

I have verified that the user group in which the new user is being created has been given USAGE permission on the schema, and gone over all of the permissions for each of the affected objects in the database (the profile table and the stored procedure), and everything appears to look correct, but I can't get my Web application to create the new "profile" record.

For reference, here are the two stored procedures (redacted):

1) CREATE THE DATABASE USER ROLE (This works correctly and the user is created)

CREATE OR REPLACE FUNCTION "SP_CreateUser"("User" character varying, "Pass" character varying)
  RETURNS boolean AS
$BODY$DECLARE success boolean;
BEGIN
EXECUTE 'CREATE USER ' || quote_ident($1) || ' PASSWORD ' || quote_literal($2);
EXECUTE 'GRANT systemusers TO ' || quote_ident($1);
SELECT pg_roles.rolname = $1 INTO success FROM pg_roles WHERE pg_roles.rolname = $1;
RETURN success;
END$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION "SP_CreateUser"(character varying, character varying)
  OWNER TO administrators;
GRANT EXECUTE ON FUNCTION "SP_CreateUser"(character varying, character varying) TO administrators;
GRANT EXECUTE ON FUNCTION "SP_CreateUser"(character varying, character varying) TO systemusers;
REVOKE ALL ON FUNCTION "SP_CreateUser"(character varying, character varying) FROM public;

2) CREATE THE "PROFILE" RECORD (This fails with Error 42501 when the Web application connects to the database as the newly created user)

CREATE OR REPLACE FUNCTION website."SP_CreateProfile"("EmailAddress" character varying, "FirstName" character varying, "LastName" character varying, "PhoneNumber" character varying)
  RETURNS boolean AS
$BODY$DECLARE success boolean;
BEGIN
INSERT INTO website.profiles ("UserEmail", "FirstName", "LastName", "AdministrativeUser", "Active", "PhoneNumber")
VALUES ($1, $2, $3, FALSE, TRUE, $4);
SELECT p."UserEmail" = $1 INTO success FROM website.profiles p WHERE p."UserEmail" = $1;
RETURN success;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION website."SP_CreateProfile"(character varying, character, character varying, character varying, character varying, character varying)
  OWNER TO administrators;
GRANT EXECUTE ON FUNCTION website."SP_CreateProfile"(character varying, character, character varying, character varying, character varying, character varying) TO administrators;
GRANT EXECUTE ON FUNCTION website."SP_CreateProfile"(character varying, character, character varying, character varying, character varying, character varying) TO systemusers;
REVOKE ALL ON FUNCTION website."SP_CreateProfile"(character varying, character, character varying, character varying, character varying, character varying) FROM public;

You may note that I didn't set the second stored procedure with SECURITY DEFINER. That was intentional, as I'd prefer the function run with the individual's security credentials. However, in my testing, even if I've turned on SECURITY DEFINER, I still get the same "permission denied" error.

So, the question now is this: What am I overlooking? I feel like I've tested about every permutation of permission settings, but I've obviously missed something in the process. Thanks for your help.

Answer 1

I appear to have gotten it working. I looked back over all of the permission settings on the schema, except that this time I went back through all of the "Default Privileges" as well. I set my "systemusers" group to have SELECT on tables and sequences, EXECUTE on functions and USAGE on types. When I tested again, my stored procedure ran correctly and created my profile record in the database as expected.

So, apparently I DID overlook something rather important. Even with all of the individual permission settings I had assigned on the specific objects, those default privileges were still not set the way they needed to be. Lesson learned.