LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 03-01-2005, 10:34 AM   #1
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Rep: Reputation: 62
Populating tables in Postgresql


I am trying to convert some MS Access DBs to Postgresql on FC3 with the primary goal of learning Postgresql, but I keep running into problems on what should be easy stuff.

I've created a db & a table. I've moved Access table data to a plain text file (comma delimited) with a permission code of 644. That text file is in my home directory which is also where my PSql DB is.

I want to use the COPY command to move data from the text file into my PSql table.

Here's what happens:
mydb=# COPY table1 FROM '/home/myself/textfile' WITH DELIMITER ',' ;
ERROR: could not open file "/home/myself/textfile" for reading: Permission denied

Your patience and help is appreciated.
 
Old 03-01-2005, 01:03 PM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,417

Rep: Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984Reputation: 1984
who are you connecting to postgresql as? often people will connect each time with a sginle "postgres" user or similar. if you're not using your normal user, then while the file in your home dir is readable, your home directory itself won't be.
 
Old 03-01-2005, 01:27 PM   #3
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
I'm logged in as 'myself', defined by 'postgres' as a superuser in this database. It's the same user that created the postgresql table, and the owner of the text file. When I look at the system monitor, I see 'postgres' as the owner of the postmaster process, but 'myself' as owner of the psql process.

Is it possible that I need to define postgres as a user in the same group as 'myself'?

OTOH, the file I'm trying to read is open to the world for read access.

...or maybe I just don't understand what you're trying to tell me .... lol
 
Old 03-01-2005, 05:22 PM   #4
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
Hope this isn't considered bad manners, but I'm going to bounce it thru page one again.
 
Old 03-01-2005, 05:56 PM   #5
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,568

Rep: Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865
Yes it is. The rules say 24H before you can bump.

All I can is it worked on my system using a database and a file created with my user. I basically created the file from an existing table, changed permissions to 644 and copied it back. The file was located in my /home/users directory.

I suppose you have verified the file's permissions are really 644? Who owns the file?
 
Old 03-01-2005, 06:14 PM   #6
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
<The rules say 24H before you can bump. >

Sorry, I won't do it again.

When I ran initdb, It said 'myself' would have to own all assosiated tables, etc. On your advice, I su'd to postgres and made 'myself' a superuser. 'Myself' can now create DB's and Tables. The plain text file I am trying to read into the table is also owned by 'myself'.

Is it possible that 'myself' is a different user to Postgresql than it is to Linux?

The DB is located in /home/myself/ and so is the flat file, but the flat file is not part of the DB.
 
Old 03-01-2005, 10:57 PM   #7
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,568

Rep: Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865
AFAIK where the database is located should not matter. It even worked as the postgres user. I only have one regular user on my network. This will require some additional thinking....
 
Old 03-01-2005, 11:36 PM   #8
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
I thought perhaps the error message was misleading, but I've tried a bunch of different things, even to typing the data from the 1st 4 or 5 records, saving them to a different file, then trying the copy. Still get a protection error. Tried explicity namIng the fields in the COPY statement, same thing. Can INSERT data into the table fine, just can't COPY.

Had the idea that the fact that my original flat file was created in Windows might have something to do with it, but seem to have eliminated that possibility by typing my own file in Linux.

I'm puzzled, but not ready to give up.
 
Old 03-02-2005, 01:43 PM   #9
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
I believe this problem has something to do with the fact that 'postgres' is not an actual OS defined user. He seems to exist only in the Postgresql environment. He appears on the System Monitor as the owner of the postmaster process. When I su to him from root, I get a $ prompt, but from that prompt, I can only issue postgresql commands. If I try to list a directory or anything else, I get a permission denied error.

Assuming that I need to add him as a regular user, does he just get a regular /home/ directory & password? No special privileges?
 
Old 03-02-2005, 06:53 PM   #10
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
OK. Still studying. Learning, but I'm not a real fast learner.

User: postgres exists. He's a System User evidently created by Fedora when I chose to install postgresql at the initial Fedora installation. When I'm looged in as him, I can navigate thru the various system directories including /home/, but when I try to access /home/myuserid/, I get a permission denied error. I tried making postgres a part of my group, but that didn't help. The only group he is in is the postgres group.

The permissions on my /home/myuserid/ directory look like this, drwx------. Since the file I can't COPY FROM is in that directory, I'm still assuming that postgres needs some additional privleges to access it.

< From the postgresql docs: Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. >

A few possible solutions occur to me.
1. Add user: postgres to one or more administrative groups (like root) so he can access my directory.
2. Change my directory permissions to drwxr--r--, so other unprivileged users can read it.
3. Add user: postgres to my group and change my directory permissions to drwxr-----.

I'm not sophisticated enough in Linux to understand the potential hazards in any of those options. OTOH, I may be completely off track anyway. Suggestions?
 
Old 03-02-2005, 09:57 PM   #11
michaelk
Moderator
 
Registered: Aug 2002
Posts: 25,568

Rep: Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865Reputation: 5865
Before change my home directory's permissions I would create another directory for the files like /database or something with "world" permissions.
 
Old 03-02-2005, 10:34 PM   #12
rickh
Senior Member
 
Registered: May 2004
Location: Albuquerque, NM USA
Distribution: Debian-Lenny/Sid 32/64 Desktop: Generic AMD64-EVGA 680i Laptop: Generic Intel SIS-AC97
Posts: 4,250

Original Poster
Rep: Reputation: 62
For the record, my option 3 above worked, but your idea is obviously better ... a directory for temporary files that could be empty except when needed.

Am now busily engaged in the clean-up necessary to prepare Access data for manipulation in Linux. ... I know how to do that, tho. Thanks again for your help.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
what are ip tables? master Linux - Security 4 01-24-2005 07:59 PM
populating /dev class_struggle Linux - Newbie 3 07-25-2004 06:39 PM
PERL: populating a drop down box from the DB vous Programming 3 08-27-2003 07:41 AM
Ip Tables Mag|c Linux - Security 3 06-26-2003 10:06 PM
postgresql -odbc & postgresql-jdbc installation kjsubbu Linux - Software 0 06-19-2003 02:50 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 05:22 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration