Authorization in a function

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

Browse pgsql-general by date

  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