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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq