Migration - pg_dump permission denied

i feel like a total noob, but a simple command needed for peertube migration has me stuck. the migration instructions from peertube say to run the following after stopping peertube:
sudo -u peertube pg_dump -Fc peertube_prod > /tmp/peertube_prod-dump.db
however, as per the initual setup of the instant, the peertube user is very much not part of the sudoers, so no matter where i export the db to, it’s « permission denied ». running it as root user also fails because « role root does not exist ». so i’m kind of going in a merri-go-round here trying to get this done. i’m sure the fix is simple, but evidently too complicated for me to figure out with the instructions on the site.
if someone could drop the little hint i need i would be on my way.

i have also tried to make the peertube user part of the sudo group. permission still denied.

Hello,

I’m not really sure…

A user/role has to have the right to make the dump of a Postgres database; the « root » user does not have this permission by default; this is the reason why you get the « role root does not exist » message at your second attempt.

For your first try, the « permission denied » message might say that your own user (not peertube) has no permission to write the file in « /tmp ». The redirection operator (« > ») does not transfer the user rights. In other, your « sudo » command is done as the user « peertube » and the redirection to a file is done on the name of the user currently connected (the shell’s user).

So you might try this sudo -u peertube pg_dump -Fc peertube_prod | sudo tee /tmp/peertube_prod-dump.db

Knowing that the resulting backup will get root as owner and, depending of your system, no permission at all for other users.

Where did you find this command?

Usually, to run a postgresql command, you must act as the postgres user.

So, many try running (as root):

sudo -u postgres pg_dump -Fc peertube_prod > /tmp/peertube_prod-dump.db

More explanation: there are basically 2 ways to connect to the database server: using login/password (that’s how peertube does, login/password are setup in the peertube configuration file). Or you can act as the postgres user, which has default access to all databases.

Don’t do that. Never. This is a huge security risk.

on the official docs. here

so to fix it, i ran the first command, the pg_dump command, as the postgres user; an idea that i got from the second command, the pg_restore one.

i only did it to see if that was the problem, and rolled it right back :slight_smile: it does seem like a big risk.

gee. thanks for that detailed explanation though :beers:

1 « J'aime »

I just checked on my servers, just in case… It appears that sudo -u peertube pg_dump works for me.

It seems that by default, postgres grant access if the system user name match the db user name.

The installation documentation gives these lines to create the database:

sudo -u postgres createuser -P peertube
sudo -u postgres createdb -O peertube -E UTF8 -T template0 peertube_prod

So, if you used those exact lines, it should work. I don’t know why it does not in your case. Maybe you used another name for the db user?

In such case, it is possible to create a system/db username mapping:

no i can assure you, i followed the docs to the letter. i’m a windows user and a total noob at linux and web servers; i’m not adding my own twist to this, believe me. so i’m not sure what happened exactly.

this seems like it should be a reason for it to work. but it is an older install; probably 2 years old or so. maybe stuff worked different back then.

either way, it’s nice to be on version 7 now. looks real sharp :smirk:

i tested on several servers, one of them was setup many years ago. So this is probably not a change in the installation procedure.
My knowledge of postgresql is limited, so I don’t know what could explain the issue.

I let someone else propose an explanation.