I'm using PostgreSQL 7.1.3 and have seen in the 7.2 documentation:
SET SESSION AUTHORIZATION 'username'
Is this available in 7.1.3? I will be upgrading to 7.2 but not just yet.
I'm asking because I want to run a copy command from a function where the user is not a super user, I thought that I
couldset the authorization to 'postgres' run the command then set the authorization back to 'appuser' although I don't
knowif this will even work or is there another way to go about this?
Heres the function - user is appuser:
CREATE FUNCTION postgres_report(date,date) returns int as '
BEGIN
CREATE TEMPORARY TABLE temp_table AS
SELECT
p.firstname,
p.lastname,
t.purpose,
t.subpurpose,
t.issue_date,
t.date_from,
t.date_to,
k.key_code,
t.location,
t.permit_conditions
FROM person5 p
INNER JOIN (faps_permit t LEFT OUTER JOIN archive_faps_key k ON t.permit_id = k.permit_id) ON p.person_id =
t.person_id
WHERE t.date_from >= $1 AND t.date_to <= $2
ORDER BY t.issue_date;
SET SESSION AUTHORIZATION ''postgres'';
COPY temp_table TO ''/usr/local/pgsql/current_report.csv'' USING DELIMITERS '','' WITH NULL AS '' '';
SET SESSION AUTHORIZATION ''appuser'';
DROP TABLE temp_table;
return 1;
END;'
LANGUAGE 'plpgsql';
Regards,
Sharon Cowling
