postgreSQL COPY command and rights

I think there is some ambiguity in your concept.

A pl/pgsql function is executed on the server side but it can be invoked from any client.
So how can a client specify the path that is on the server? If it is indeed a server path then I recommend simply using a predefined upload directory and don't care about client's complaints about how badly they would like to put their files elsewhere.

As for permissions to (create and) populate a temporary table; do you expect different users (I mean postgresql users, not real-life people) to invoke this script? All of them having upload permissions to the directory mentioned above? If yes then you need:

-- to create temp table in a database (everybody has permissions by default but you can explicity define it)
GRANT TEMP ON DATABASE mydatabase TO myuser;
-- to create (non-temp) table
GRANT CREATE ON SCHEMA myschemafortemptables TO myuser;
-- to insert into existing table (including copy from csv file into this table)
GRANT INSERT ON mytable TO myuser;

Select allOpen in new window

In all cases, you can specify more than one objects or users separated by commas.
You can use the keyword PUBLIC instead of a (list of) user(s).

Thank you for your answer.

I’m not sure to understand ... I’m trying to improve my skills in managing rights. Could you correct the following point of view?

>do you expect different users (I mean postgresql users, not real-life people) to invoke this script?
>All of them having upload permissions to the directory mentioned above?

I think that I don’t understand the permissions needed to upload the file and the permissions needed to "open" the file.

In my mind:
- A web application (for example) uploads the file from the client directory to a predefined directory csv_uploads_client_1 for example.
Under what conditions is that always possible?
- I don’t know how to grant to a role the good privilege on the csv_uploads_client_1 directory in order the role to have rights to get data from files of this directory (but I understand how to grant to the role the rights to create and fill the table).
- Does this predefined directory csv_uploads_client_1 need to be in the database server, I mean does it need to be a subdirectory of C:\Program Files\PostgreSQL\9.2 for example?
Does a tablespace need to be created for this purpose?

Hello Murano and thank you again.
I didn't understand the two last sentences but you'll certainly explain again in your next answer ... so I'll wait for your answer on friday.

In my Windows context, PosgreSQL is running as a service under an account.
I have two postgreSQL services running.
postgresql server 9.0 running under .\postgres
postgresql server 9.2 running under Network Service

When displaying all users in the computer management window, I see postgres. I guess this user was created when installing postgresql server 9.0.

I didn't control that when installing and I don't know whether it is important to install the service under an account that is created by me rather than the default. Nor I don't know whether somewhere this account has to do with my function import_file_csv_1(file_path VARCHAR) being able to read a file. Same question when connecting under a role that is different of postgres. Are there some defaults parameters that I'm not aware of?
So ... I'm a bit confused and I'll really appreciate an accurate explanation ...

Hello molune,

Let's move from theory to practice.
Have you tried the grants I proposed earlier? Do you get some error messages?

No comprehensive solution given. I'm adding this hint that isn't a solution