Postgresql – How to allow users to run a function, but not access objects the function is accessing

Couldn't you have procedure A run with execute as owner or execute as 'db user tied to security account associated with linked server'? That way the rights of the caller don't have to transfer...

Or, you can create a login on the remote server that only has the ability to execute the queries you want, and add an impersonation account to the existing linked server on Server A to have the local webserver login impersonate the new login on server B.

I think this is because you only ever return the first row from the query's result.

The select ... into ... will only retrieve one row and the query select * from result returns only that single record:

You also don't need a PL/pgSQL function, a plain SQL function will work just fine:

CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS 
   setof test_messageq_table 
AS 
$$ 
  WITH patient_msg_in_branches AS (
      select distinct test_messageq_table.master_id AS patient_id,
             test_patient_table.site_held_at as site_id
      from test_messageq_table 
         join test_patient_table ON test_messageq_table.master_id = test_patient_table.entity_id 
                                and site_held_at = ANY (string_to_array(ids,',')::int[]) 
  ),
  messages_for_patients AS
  (
    select * 
    from test_messageq_table 
    where master_id in (select patient_msg_in_branches.patient_id 
                        from patient_msg_in_branches)
  )
  select * 
  from messages_for_patients;
$$ 
LANGUAGE sql;

Note that the order by inside the CTE is not really useful. You have to sort the final select, not the intermediate steps.

If you do need PL/pgSQL because you are doing more stuff in the function, you should simply change it to:

begin
  ....
  return query
    WITH patient_msg_in_branches AS (
        select distinct test_messageq_table.master_id AS patient_id,
        test_patient_table.site_held_at as site_id
        from test_messageq_table 
        inner join test_patient_table 
        ON test_messageq_table.master_id = test_patient_table.entity_id 
        and site_held_at = ANY(sites) order by patient_id
    ),
    messages_for_patients AS(
        select * from test_messageq_table where master_id in 
            (select patient_msg_in_branches.patient_id 
                from patient_msg_in_branches)
    )
    select * from messages_for_patients;
end;