带有 PostgreSQL 命令的 PHP 脚本为 Json 数据返回 NULL [英] PHP script with PostgreSQL commands returning NULL for JSon data

问题描述

我创建了一个非常简单的 PostgreSQL 函数:

I created a very simple PostgreSQL function:

CREATE OR REPLACE FUNCTION betya_ref."func_GetBetTypes"()
 RETURNS SETOF betya_ref."tbl_BET_TYPES" AS
$BODY$
SELECT * FROM betya_ref."tbl_BET_TYPES";    
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100
ROWS 20;
ALTER FUNCTION betya_ref."func_GetBetTypes"() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION betya_ref."func_GetBetTypes"() TO public;
GRANT EXECUTE ON FUNCTION betya_ref."func_GetBetTypes"() TO postgres;
GRANT EXECUTE ON FUNCTION betya_ref."func_GetBetTypes"() TO dummy_users;

我在 pgAdmin 中测试了该功能:

I tested the function within pgAdmin:

SELECT * FROM betya_ref."func_GetBetTypes"()

它返回了正确的结果:

ID  NAME     
1   WIN/LOSE
2   TRUE/FALSE"
3   TRUE/ALTERN TRUE"
4   RIGHT/WRONG"

我创建了一个 PHP 脚本，位于Apache2.2\htdocs\server\betya_ref\getBetTypes.php"目录下的 Apache 2.2 服务器中:

I created a PHP script, sitting in an Apache 2.2 server under the directory "Apache2.2\htdocs\server\betya_ref\getBetTypes.php":

$dbconn = pg_connect("host=192.168.1.222 port=5432 dbname=betya user=dummy_user password=dummy_pass")
    or die('Could not connect: ' . pg_last_error());
print('connect ... ' . $dbconn); //debugging
 $result=pg_query($dbconn, 'SELECT * FROM betya_ref."func_GetBetTypes"()');
 print(' ***  result ... ' . $result); //debugging  
 while($e=pg_fetch_row($result))
$output[]=$e;
print('  *** output ... ' . $output . '... *** JSon ...'); //debugging
print(json_encode($output));

pg_free_result($result);

pg_close($dbconn);

我使用此 URL 在浏览器中调用 PHP 脚本:

I call the PHP script in a browser using this URL:

http://localhost/server/betya_ref/getBetTypes.php

哪个返回:

connect ... Resource id #2 *** result ... *** output ... ... *** JSon ...null

我运行的其他(非常相似)现有脚本使用相同的连接字符串和来自相同目录但来自同一数据库中的不同架构的返回并正确显示 JSon 数据.这个新架构具有以下权限；

Other (very similar) existing scripts I run return and display the JSon data correctly with the same connection string and from the same directory, but from a different schema within the same database. This new schema has the following permissions;

GRANT ALL ON SCHEMA betya_ref TO postgres;
GRANT USAGE ON SCHEMA betya_ref TO betya_users;

我认为 PHP 脚本中存在错误，但看不出我错在哪里，考虑到一切都很简单，而且直接来自 PHP 手册.什么我没有考虑过?

I'm thinking there is an error in the PHP script, but can't see where I am wrong, considering everything is so simple, and straight from the PHP manual. What haven't I considered?

推荐答案

GRANT EXECUTE ON FUNCTION betya_ref."func_GetBetTypes"() TO public;

会覆盖它.除了你甚至不需要那个.手册:

would cover it. Except that you don't even need that. The manual:

对于其他类型的对象，授予PUBLIC的默认权限如下: [...] EXECUTE 函数和过程的权限；

For other types of objects, the default privileges granted to PUBLIC are as follows: [...] EXECUTE privilege for functions and procedures;

无需GRANT任何更多的功能权限.对 postgres 的 GRANT 也只是噪音，因为 postgres 是一个忽略特权的超级用户.

No need to GRANT any more privileges on the function. The GRANT to postgres is just noise, too, because postgres is a superuser who ignores privileges.

你也:

GRANT USAGE ON SCHEMA betya_ref TO betya_users;

betya_users 是否应该与 dummy_users 相同?
你是否GRANT dummy_users TO dummy_user(单数)?

Is betya_users supposed to be the same as dummy_users?
And did you GRANT dummy_users TO dummy_user (singular)?

你是否也:

GRANT SELECT ON TABLE betya_ref."tbl_BET_TYPES" TO `dummy_user`

直接还是间接?一个函数的权限不覆盖所涉及的表的权限，除非你让函数SECURITY DEFINER.在这种情况下，请务必阅读在手册中安全地编写SECURITY DEFINER函数".

directly or indirectly? Privileges for a function do not cover privileges on the involved tables, unless you make the function SECURITY DEFINER. In this case, be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.

不过，我没有看到错误消息.如果缺少权限，应该会出现错误消息.

I don't see an error message, though. With missing privileges there should be an error message.

这篇关于带有 PostgreSQL 命令的 PHP 脚本为 Json 数据返回 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！