0

I recently started learning psql, right now I was trying to import some csv spreadsheets into a PostgresQL database using the COPY command.

As far as I know, after installing psql, we have a new account in the system called postgres, and every time I need to use postgres I should sudo -iu to the postgres account and then do psql.

Then I was trying to copy those csv files into the data directory of postgres. I used my account but found that I cannot access the postgres data directory. What I meant by “cannot access” is that when I type cd, nothing happened.

[azure@myVM ~]$ cd /var/lib/pgsql/13/
-bash: cd: /var/lib/pgsql/13/: Permission denied
[azure@myVM ~]$ sudo cd /var/lib/pgsql/
[azure@myVM ~]$ 

I wish to know what is the “normal” way to import data? Because I guess if I am doing everything in a standard way I shouldn’t be going around modifying user access for directories. The machine I am using is a Centos VM from Microsoft azure, and the Postgres version is 13.

3 Answers 3

1

For security reasons, the permissions for the postgresql data directory should be u=rwx (0700) or u=rwx,g=rx (0750). Otherwise, the postgresql server will refuse to start.

Thus, regular user - not the owner of the postgresql cluster (usually named postgres) or root - can neither write nor even read files inside the data directory.

Strictly speaking, the user shouldn't write anything to the data directory at all (besides editing configuration files). Everything inside the data directory is part of the DBMS and the DBMS itself manages this.


I was trying to import some csv spreadsheets into a PostgresQL database using the COPY command

But why you want to copy these CSV into data directory? The postgresql server COPY command is able to read file from filesystem (again, for security reasons, you should be superuser or be a member of pg_read_server_files group) or use client-side COPY. For example, psql \copy meta command will works for regular user.

2
  • Dear @Melkij: Thank you very much for the reply. So if I didn't misunderstand you, I am supposed to grant the user postgres with super user access and carry on with the COPY command? Cuz I always run into the permission denied command when I am using the account postgres. Sep 5, 2021 at 10:47
  • postgres is already superuser in the database. It is important not to confuse OS privileges with rights inside the database. root is OS superuser, postgres is regular OS user but DB superuser.
    – Melkij
    Sep 5, 2021 at 11:00
0

every time I need to use postgres I should sudo -iu to the postgres account and then do psql.

You should not use sudo ... psql on a regular basis. You would use it once to create a different user (and probably a database for that user), and then use that different user from then on. And then only use sudo when you need to do some kind of maintenance.

You might also want to change the auth method for non-superusers from peer to some kind of password.

(And then you would use \copy as a normal user)

0

Then I was trying to copy those csv files into the data directory of postgres.

Database != Files 

A CSV file is an external storage mechanism that is nothing whatever to do with PostgreSQL.
PostgreSQL data files, which live in the Data Directory, are a bespoke, binary format that you should never have to worry about.
The two are absolutely, totally, definitely not interchangeable.

In other words, your CSV file should go nowhere near your Data Directory.

To get the data contained in your csv file into your PostgreSQL database, you need to use a database utility (e.g. psql's \copy command) to read the file and insert the data into [one of] your tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.