Thread: alter role - superuser - permissions

I want to learn how to insert data into tables.
Using postgres 9.5.x on Fedora 24.

linux user account:  mmm
postgres database:  mmm
database mmm table: accounts

mmm=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 mmm       | Superuser, Create DB                           | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}


Tried to use the copy command to insert data from .csv file into table
named "accounts"

It appears I was successful in altering the role of user "mmm" and
making it a superuser.

But, when I try the copy command, the following error occurs:

mmm=# copy accounts
(employ_last,employ_first,service,website,userid,password) from
'/home/mmm/Documents/accounts-services.csv';
ERROR:  could not open file
"/home/mmm/Documents/accounts-services.csv" for reading: Permission
denied
mmm=#

If mmm is a superuser, and user mmm owns the mmm database,  the .csv
file is in mmm's home directory, what other permissions are required
or still incomplete?

Thank you for your help.

Best regards,

Mike

The database is running under the postgres user, and this user has no acces to this directory. You can use something like psql <db> -f <filename>



--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

On Sun, Sep 4, 2016 at 10:41 AM, Hassan Schroeder
<hassan.schroeder@gmail.com> wrote:
>
> Where a file is located has nothing to do with either ownership or
> permissions; what exactly are the perms for "accounts-services.csv"?
>

The file permissions are:  -rw-rw-r--.  1 mmm mmm  182 Aug 31 01:13
accounts-services.csv
I would think the postgres user could access/read the file content and
write it to the table.

Thanks for your response.
I'll try chown to postgres group/user and see if that changes the result.

On Sun, Sep 4, 2016 at 10:52 AM, Andreas Kretschmer
<andreas@a-kretschmer.de> wrote:
> The database is running under the postgres user, and this user has no acces
> to this directory. You can use something like psql <db> -f <filename>
>

And I'll try this too.
Thank you.

Mike <1100100@gmail.com> writes:
> But, when I try the copy command, the following error occurs:

> mmm=# copy accounts
> (employ_last,employ_first,service,website,userid,password) from
> '/home/mmm/Documents/accounts-services.csv';
> ERROR:  could not open file
> "/home/mmm/Documents/accounts-services.csv" for reading: Permission
> denied

COPY executes in the server process, so this is likely to fail unless
/home/mmm/Documents/accounts-services.csv is world-readable.  You
probably want to use psql's \copy command instead.

There's some discussion going on right now about adding a HINT to
this error message to help out people in your situation.  If you
feel like bringing a novice's-eye viewpoint to the discussion, it's
over in pgsql-hackers at

https://www.postgresql.org/message-id/flat/CAMsr+YEqtD97qPEzQDqrCt5QiqPbWP_X4hmvy2pQzWC0GWiyPA@mail.gmail.com

            regards, tom lane

Hi Tom,

Thanks for the pointer to the list discussion.
Many good brains going at it.  :-)

On Sun, Sep 4, 2016 at 11:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mike <1100100@gmail.com> writes:
>> But, when I try the copy command, the following error occurs:
>
>> mmm=# copy accounts
>> (employ_last,employ_first,service,website,userid,password) from
>> '/home/mmm/Documents/accounts-services.csv';
>> ERROR:  could not open file
>> "/home/mmm/Documents/accounts-services.csv" for reading: Permission
>> denied
>
> COPY executes in the server process, so this is likely to fail unless
> /home/mmm/Documents/accounts-services.csv is world-readable.  You
> probably want to use psql's \copy command instead.
>
> There's some discussion going on right now about adding a HINT to
> this error message to help out people in your situation.  If you
> feel like bringing a novice's-eye viewpoint to the discussion, it's
> over in pgsql-hackers at
>
> https://www.postgresql.org/message-id/flat/CAMsr+YEqtD97qPEzQDqrCt5QiqPbWP_X4hmvy2pQzWC0GWiyPA@mail.gmail.com
>
>                         regards, tom lane

I didn't get the copy command to work today; however, I did figure out
you can copy a massive list of values and put the following command in
front and paste that into a psql prompt ---

insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');
insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');
insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');
insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');
insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');
insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');
insert into accounts (employ_last, employ_first, service, website,
userid, password) VALUES ('aaa','bbb','ccc','ddd','eee','fff');

Noobish, yes; a happy discovery, yes.  :-)