From: | Sharon Cowling <sharon(dot)cowling(at)sslnz(dot)com> |
---|---|
To: | "Pgsql-General (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Authorization in a function |
Date: | 2002-01-31 22:42:00 |
Message-ID: | 200201312256.g0VMuMt25372@lambton.sslnz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 could set the authorization to 'postgres' run the command then set the authorization back to 'appuser' although I don't know if 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
From | Date | Subject | |
---|---|---|---|
Next Message | Syd Alsobrook | 2002-01-31 23:06:04 | Re: System commands |
Previous Message | Tom Lane | 2002-01-31 22:35:26 | Re: Function to Pivot data |