[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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.
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
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.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month20 days, left to enroll

873 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