Thread: Function problem

I have a problem with a function containing the copy command.

The command below does exactly what I need it to do:
\COPY person2 TO '/usr/local/pgsql/report.csv' USING DELIMITERS ','

However I need this in a function to call from the front-end code, it creates fine but errors when I call it:


user=# CREATE FUNCTION getreport10() returns int as '
user'# BEGIN
user'# \COPY person2 TO ''/usr/local/pgsql/report.csv'' USING DELIMITERS '','';
user'# return 1;
user'# END;'
user-# LANGUAGE 'plpgsql';
CREATE

user=# select getreport10();
ERROR:  You must have Postgres superuser privilege to do a COPY directly to or from a file.  Anyone can COPY to stdout
orfrom stdin.  Psql's \copy command also works for anyone. 

It does work if I give the user superuser privileges but I don't really want to do this!
Any suggestions?

Regards,

Sharon Cowling

Sharon,

you can set the session user using set session authorisation.

see:
http://developer.postgresql.org/docs/postgres/sql-set-session-authorization.
html and
        http://developer.postgresql.org/docs/postgres/sql-copy.html

regards

steve boyle

P.S. did you mean to include the \ in the function below?

----- Original Message -----
From: "Sharon Cowling" <sharon.cowling@sslnz.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, January 17, 2002 9:42 PM
Subject: [GENERAL] Function problem


> I have a problem with a function containing the copy command.
>
> The command below does exactly what I need it to do:
> \COPY person2 TO '/usr/local/pgsql/report.csv' USING DELIMITERS ','
>
> However I need this in a function to call from the front-end code, it
creates fine but errors when I call it:
>
>
> user=# CREATE FUNCTION getreport10() returns int as '
> user'# BEGIN
> user'# \COPY person2 TO ''/usr/local/pgsql/report.csv'' USING DELIMITERS
'','';
> user'# return 1;
> user'# END;'
> user-# LANGUAGE 'plpgsql';
> CREATE
>
> user=# select getreport10();
> ERROR:  You must have Postgres superuser privilege to do a COPY directly
to or from a file.  Anyone can COPY to stdout or from stdin.  Psql's \copy
command also works for anyone.
>
> It does work if I give the user superuser privileges but I don't really
want to do this!
> Any suggestions?
>
> Regards,
>
> Sharon Cowling
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Sharon,

having thought about this some more I think you will need to use an
untrustred procedural language to let you interact with the file system.

I think you would have the option of perlu, tclu or pythonu (although I
haven't used them) as I don't think there's an untrusted version of
plpgsql??.

http://www.postgresql.org/idocs/index.php?xplang.html

Sorry for the earlier red herring

sb

ps. pls post when you get this working.
If I get more time I'll mess about with it over the weekend.  I suppose you
could write a generic function such as:

export_sql_to_file ( [pSQLStatement] , [pFileName] , [Format] ,
[Delimiter] )
(
    --Drop temp table if exists
    --Create temp table from pSQLStatement
    --Export temp table data to Filename in Format using Delimiter
    --Drop temp table
)

----- Original Message -----
From: "Steve Boyle (Roselink)" <boylesa@roselink.co.uk>
To: "Sharon Cowling" <sharon.cowling@sslnz.com>;
<pgsql-general@postgresql.org>
Sent: Thursday, January 17, 2002 10:19 PM
Subject: Re: [GENERAL] Function problem


> Sharon,
>
> you can set the session user using set session authorisation.
>
> see:
>
http://developer.postgresql.org/docs/postgres/sql-set-session-authorization.
> html and
>         http://developer.postgresql.org/docs/postgres/sql-copy.html
>
> regards
>
> steve boyle
>
> P.S. did you mean to include the \ in the function below?
>
> ----- Original Message -----
> From: "Sharon Cowling" <sharon.cowling@sslnz.com>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, January 17, 2002 9:42 PM
> Subject: [GENERAL] Function problem
>
>
> > I have a problem with a function containing the copy command.
> >
> > The command below does exactly what I need it to do:
> > \COPY person2 TO '/usr/local/pgsql/report.csv' USING DELIMITERS ','
> >
> > However I need this in a function to call from the front-end code, it
> creates fine but errors when I call it:
> >
> >
> > user=# CREATE FUNCTION getreport10() returns int as '
> > user'# BEGIN
> > user'# \COPY person2 TO ''/usr/local/pgsql/report.csv'' USING DELIMITERS
> '','';
> > user'# return 1;
> > user'# END;'
> > user-# LANGUAGE 'plpgsql';
> > CREATE
> >
> > user=# select getreport10();
> > ERROR:  You must have Postgres superuser privilege to do a COPY directly
> to or from a file.  Anyone can COPY to stdout or from stdin.  Psql's \copy
> command also works for anyone.
> >
> > It does work if I give the user superuser privileges but I don't really
> want to do this!
> > Any suggestions?
> >
> > Regards,
> >
> > Sharon Cowling
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>