non superuser creating flat files
I have data that is being updated in a table that I need to export to a flat
file via a database trigger on insert or update. The user performing the
update will not be a superuser. I've tried to use COPY TO, but that doesn't
work for non-superusers. Is there some other functionality that can write
out flat files? I'm looking for something similar to the Oracle UTL file
functionality, as ideally I'd want to append the data to an existing file
and be able to manipulate the data before I wrote it out.
Thanks
Terri
Terri Reid
BCT Software Solutions
Mail: treid@edp.co.uk
Tel: 01925 732359
"Terri Reid" <treid@bct-solutions.co.uk> writes:
I have data that is being updated in a table that I need to export to a flat
file via a database trigger on insert or update. The user performing the
update will not be a superuser. I've tried to use COPY TO, but that doesn't
work for non-superusers.
It will if invoked in a trigger function that's marked SECURITY DEFINER
and owned by a superuser.
regards, tom lane
Tom,
Thank you very much, that works now.
Terri Reid
01925 732359
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 13 August 2007 15:50
To: Terri Reid
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] non superuser creating flat files
"Terri Reid" <treid@bct-solutions.co.uk> writes:
I have data that is being updated in a table that I need to export to a
flat
file via a database trigger on insert or update. The user performing the
update will not be a superuser. I've tried to use COPY TO, but that
doesn't
work for non-superusers.
It will if invoked in a trigger function that's marked SECURITY DEFINER
and owned by a superuser.
regards, tom lane
On Aug 13, 2007, at 9:50 AM, Tom Lane wrote:
"Terri Reid" <treid@bct-solutions.co.uk> writes:
I have data that is being updated in a table that I need to export
to a flat
file via a database trigger on insert or update. The user
performing the
update will not be a superuser. I've tried to use COPY TO, but
that doesn't
work for non-superusers.It will if invoked in a trigger function that's marked SECURITY
DEFINER
and owned by a superuser.
Also/Or, make sure the that the correct filesystem write permissions
are set on the file/directory to which you're writing for which ever
user whose permissions the trigger function is executed as.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
On 8/13/07, Erik Jones <erik@myemma.com> wrote:
On Aug 13, 2007, at 9:50 AM, Tom Lane wrote:
"Terri Reid" <treid@bct-solutions.co.uk> writes:
I have data that is being updated in a table that I need to export
to a flat
file via a database trigger on insert or update. The user
performing the
update will not be a superuser. I've tried to use COPY TO, but
that doesn't
work for non-superusers.It will if invoked in a trigger function that's marked SECURITY
DEFINER
and owned by a superuser.Also/Or, make sure the that the correct filesystem write permissions
are set on the file/directory to which you're writing for which ever
user whose permissions the trigger function is executed as.
The trigger will always write as the use the postgres server is
running under (typically the postgres user). You will need to make
sure that that account can write to the file and that whoever else
needs to read it can read it.