I am making a query as user postgres via my current user, xyz. I am returning the results of this query as JSON. I want to save this (huge) JSON into a file owned by my current user xyz. And I do not want to login again as user postgres to chmod it etc.
Below code demonstrates what I can do so far by using COPY() TO PROGRAM 'cat>/tmp/out1 && chmod 777 /tmp/out1' . The problem is that after copying /tmp/out1 to my homedir there still is /tmp/out1 with postgres ownership which I need to login/sudo as postgres again in order to delete it. I want to avoid this second login.
chown'ing the file (as the owner user postgres) to my user is not permitted because of my particular system permissions (which are default AFAICS).
Dumping output from psql either via a cli redirect includes not only the JSON but also lots of other "rubbish".
Are there any other ways to tell psql to dump the JSON only so that I can redirect stdout to my own file?
My search has yielded this: Change permissions of postgres copy to exported file which unfortunately does not work, chgrp et.al. says Operation not permitted.
Code to play with, it is part of a much larger setting which interrogates a postgis OpenStreetMap database. My OS is linux and Pg is latest version.
psql -U postgres -d mygisdb -a <<EOS
--- something to test with, this is part of a much bigger setting
create table if not exists mytable (name VARCHAR(255));
insert into mytable (name) VALUES ('hello');
COPY(
SELECT jsonb_build_object(
'type', 'FeatureCollection',
'features', jsonb_agg(feature)
) AS geojson_output
FROM (
SELECT jsonb_build_object(
'type', 'Feature',
'properties', jsonb_build_object(
'name', name
)
) AS feature
FROM mytable
) AS subquery) TO PROGRAM 'cat > /tmp/out1 && chmod 755 /tmp/out1';
EOS
cp /tmp/out1 ~/myfilenow
# now I want to delete /tmp/out1
# this does not work
# mv /tmp/out1 ~/myfilenow
