Re: non superuser creating flat files

On 8/13/07, Erik Jones <erik(at)myemma(dot)com> wrote:
>
> On Aug 13, 2007, at 9:50 AM, Tom Lane wrote:
>
> > "Terri Reid" <treid(at)bct-solutions(dot)co(dot)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.