Solved

postgreSQL COPY command and rights

Posted on 2014-02-12
8
1,029 Views
Last Modified: 2014-02-25
Hello!

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.
0
Comment
Question by:molune
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:Surrano
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).
0
 

Author Comment

by:molune
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?
0
 
LVL 8

Accepted Solution

by:
Surrano earned 300 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.
0
 

Author Comment

by:molune
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 ...
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 8

Expert Comment

by:Surrano
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?
0
 

Assisted Solution

by:molune
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.
0
 
LVL 22

Assisted Solution

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

http://www.postgresql.org/docs/9.2/static/app-psql.html
http://www.postgresql.org/docs/9.2/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY

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

filename

    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.
0
 

Author Closing Comment

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now