[GENERAL] Loading a database, why do I need superuser?
======== Original Message ========
I'm in the process of loading a database on a virtual server.
Because I do not have root privileges (technically it is a shared machine),
I am encountering all sorts of hiccups. Trying the copy command to
load the database with comma delimited data, I encounter this complaint:
ERROR: You must have Postgres superuser privilege to do a COPY
directly to or from a file. Anyone can COPY to stdout or from stdin
There is NO mention of this in the man page or documentation that I have
perused. Perhaps I have a privilege problem?
Charles Gilley
======== Fwd by: Evan Howarth ========
Correct. You lack the 'super' privilege -- one of four privileges listed in
the 'pg_user' table. Unfortunately, your system administrator may not wish
to grant you that privilege because it also gives you the right to change
passwords and view/modify other user's tables. So the problem boils down to
a lack of granularity in user privileges.
One workaround might be asking your system administrator to create an
account for COPY to/from files. Your admin can give you the password before
each COPY session. Then after 15 minutes or so, he could change the
password. If he's worried about your access, he could monitor the activity
during that time. (This suggestion assumes you do not need COPY very
frequently.)
Evan Howarth
Halt, stop, cease this discussion! :) Seriously, I meant to say Postgres
superuser, I really do understand the differences between root and the
normal
pgsql account. Unfortunately, I don't own the Postgres account either.
Sorry for the ambiguity.
Back to the copy issue: Okay, I understand the security issue with regards
to the backend. Let me guess - that's why one has the option of the
stdin/out
feature.
About this front end copy command... from psql I try:
\copy tblauthors from tblAuthors.txt;
which reports a broken pipe. I note that the syntax for the front
end copy command does not mention quoting the filename or having
the option of saying 'using delimiters...'.
From the command line, I try the same command:
cat tblAuthors.txt | psql -d gilley -c copy tblAuthors.txt from
stdin using delimiters ','
or
cat tblAuthors.txt | psql -d gilley -c copy tblAuthors.txt from
stdin
which yield
ERROR: parser: syntax error at or near ""
Broken Pipe
What is the correct magic spell?
Charles Gilley - by the way, thanks for the quick responses.
Show quoted text
-----Original Message-----
From: James Olin Oden [SMTP:joden@lee.k12.nc.us]
Sent: Friday, November 20, 1998 9:48 AM
To: Gilley, Charles H.
Subject: Re: [GENERAL] Loading a database, why do I need superuser?"Gilley, Charles H." wrote:
I'm in the process of loading a database on a virtual server.
Because I do not have root privileges (technically it is a sharedmachine),
I am encountering all sorts of hiccups. Trying the copy command to
load the database with comma delimited data, I encounter this complaint:ERROR: You must have Postgres superuser privilege to do a COPY
directly to or from a file. Anyone can COPY to stdout or fromstdin
There is NO mention of this in the man page or documentation that I have
perused. Perhaps I have a privilege problem?Charles Gilley
Hi Charles,
Really you should never manage the postgress database as root at all. If
you
read the README and other installation notes that come with the
distribution you
will see that it _strongly_ suggests you create a postgres administrative
account (you can call it whatever you want, most of the world calls it
postgres), create the directory where you are going to install the
postgress
binaries, and then set it to be owned by this postgres administrative
user.I am not sure of all the repercussions, but the docs to express that there
are
some serious security risks to both your data and your system if you run
the
postgress application as root. Anyway, it really is all over the
docs...especially the installation docs.Hope this is helpful...james
Import Notes
Resolved by subject fallback