PostgreSQL的错误42501：权限拒绝为架构 [英] PostgreSQL Error 42501: Permission Denied for Schema

问题描述

我在构建ASP.NET用户注册系统，PostgreSQL数据库维护用户信息。作为注册过程的一部分，向用户发送了他们必须点击链接以验证他们的电子邮件地址的确认消息。这就需要他们到一个页面，在这里他们可以创建自己的密码。

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.

一旦用户提供符合安全的一些基本条件的密码，Web应用程序运行的几个PostgreSQL的存储过程（函数），首先在数据库中创建用户角色，并把它们放入一个用户组，然后创建一个纪录与用户的个人资料的详细信息（姓和名，电子邮件地址等）。

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.).

其中第一个存储过程 - 运行良好 - 创建用户角色之一。然后，应用程序从数据库断开并重新连接为新创建的用户运行第二个存储过程创建配置文件记录。然而，这是其中应用程序失败，返回上面提到的错误 42501：权限被拒绝的架构...

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...

我已经验证，在其中创建新的用户被赋予用法许可的模式，并且走遍所有的权限为每个用户组在数据库中受影响的对象（个人资料表和存储过程），并且一切似乎看起来是正确的，但我不能让我的Web应用程序来创建新的配置文件的纪录。

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）创建数据库用户角色（这正常工作和创建用户）

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）建立档案记录（这将失败，错误42501 当Web应用程序连接到数据库作为新创建的用户）

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;

您可能注意到，我没有设置与第二个存储过程SECURITY DEFINER 。这是故意的，因为我想preFER与个人的安全凭据运行的功能。然而，在我的测试，即使我已经打开 SECURITY DEFINER ，我仍然得到同样的权限被拒绝的错误。

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.

推荐答案

我似乎已经得到了它的工作。我回头一看在所有对架构的权限设置，不同的是这次我回去通过所有的默认权限为好。我把我的systemusers组有 SELECT 对表和序列，执行的功能和用法的类型。当我再次测试了，我的存储过程的正确运行和预期数据库中创建我的档案记录。

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.

所以，显然我的 DID 的忽视东西，而重要的。即使所有的我已经对特定对象分配单独的权限设置，这些默认权限仍然没有设置他们需要成为的方式。教训。

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.

这篇关于PostgreSQL的错误42501：权限拒绝为架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！