Postgres Pro
Downloads
Home   >   mailing lists

Thread: non superuser creating flat files

non superuser creating flat files

From
"Terri Reid"
Date:

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

 

 

 

 

Re: non superuser creating flat files

From
Tom Lane
Date:
"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

Re: non superuser creating flat files

From
Erik Jones
Date:
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

Re: non superuser creating flat files

From
"Scott Marlowe"
Date:
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.

Re: non superuser creating flat files

From
"Terri Reid"
Date:
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