[Webinar] Streamline your web hosting managementRegister Today


postgreSQL COPY command and rights

Posted on 2014-02-12
Medium Priority
Last Modified: 2014-02-25

I wrote a PL/pgSQL function:
import_file_csv_1(file_path VARCHAR)
that imports the lines of a CSV file in a postgreSQL table.
The function is using COPY to copy the lines of the CSV file in a temporary table.

Under what conditions does the COPY command work for sure?
- grant some privilege to the role under which the function is called?
- impose the CSV file to be in a given directory? Which one?
- What is the connection with the account under which the postgreSQL service is running?
What is the best strategy when trying to manage fine access rights?

Thank you in advance for your help.
Question by:molune
  • 4
  • 3

Expert Comment

ID: 39853280
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;

Open 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).

Author Comment

ID: 39853710
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?

Accepted Solution

Surrano earned 600 total points
ID: 39854193
This absolutely makes sense and effectively it is what I had in mind; the client uploads some file to server and then invokes some stored proc on server.

Does the webapp always use the same username to connect to PostgreSQL database?
If yes, then you have to grant the permissions I described above for that particular user.

As for how to grant privilege for a role to read from some directory I haven't tried it (can try on Friday so other experts may have to jump in if you need it earlier) but my assumption is that whatever the postmaster (a.k.a. server backend) process can read can be read by all roles within the database. This defaults to user "postgres" regardless whether the server runs on Unix or Windows.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.


Author Comment

ID: 39855684
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 ...

Expert Comment

ID: 39858488
Hello molune,

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

Assisted Solution

molune earned 0 total points
ID: 39860985
Hello Murano.

I've practiced  and ... learned

Report :

When connecting to postgreSQL under postgres
The COPY doesn't work. The file is on a computer that can be read by navigating through the network but not from the function. The questions are:
- what makes the file readable from the function when the file lies on my computer, whatever the directory. Where can I see that rights?
- is it possible to make the file readable when it is in an directory on the other computer and how?

When connecting under the regular user "appusers":
The function doesn't work since COPY has to be run by a superuser ...
I specified my function as SECURITY DEFINER and of course it works.
Is it possible to improve that?
... - wrap the "COPY FROM" function in a "COPY FROM" function specified as SECURITY DEFINER? Is it possible with all options available? Is it more dangerous than wrapping my function as I did?

Incidatelly, I didn't understand how to make my regular user "appsers" be able to INSERT in tempory tables created by the function in the "special" schema where they are created by postgreSQL. I don't know whether it is automatic since I focused my attention on "SECURITY DEFINER" functions.
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 200 total points
ID: 39866137
If you need to load a file from a client connection you can use psql.exe and \copy command.


If you want to use the SQL COPY command then you have to be a little careful how you specify the filename ie


    The absolute path name of the input or output file. Windows users might need to use an E'' string and double any backslashes used in the path name.

Only super users can use COPY.

Author Closing Comment

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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question