Обсуждение: 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.

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