PHP script with PostgreSQL commands returning NULL for JSon data

Question

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;

I tested the function within pgAdmin:

SELECT * FROM betya_ref."func_GetBetTypes"()

And it returned correct results:

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

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

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

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

Which returns:

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

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;

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?

Answer 1

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

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

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

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.

You also:

GRANT USAGE ON SCHEMA betya_ref TO betya_users;

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

Did you also:

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

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.