dbrestore fails on PostgreSQL when the db user hasn't superuser permissions

[patrickhallen]

Hi,
on my system (Debian Jessie, Postgre 9.4, Django 1.10, Python 3.4) dbrestore fails withERROR: must be owner of extension plpgsql. This is after applying the changes in #212 to get an error message at all.

There are two obvious ways to fix this, both of which I don't particular like. One can either give the Django db user superuser permission or one can run the dbrestore command as the postgres superuser. To run dbrestore as the postgres user I have to give this user the permission to execute manage.py. Both of these options reduce the isolation between the user under which the Django application runs and the postgres user.

Another possible fix could be related to #190. The usage of the pg_restore command instead of pgsql gives a more fine-grained control over what to restore so one could limit the restore to the parts where the Django db user has the appropriate permissions for.

[ZuluPro]

Hello @patrickhallen
Thanks for this issue and suggestions. We will take care of this for future implementation and documentation.

[snowrry]

@ZuluPro @patrickhallen
Is there any fix available yet? Actually dbrestore cannot work for postgres database due to this...

[patrickhallen]

As I've already mentioned in the issue, there are two possible workaround. At the moment, I'm running dbrestore as the postgres user:

Add the following to your settings.py:

DBBACKUP_CONNECTORS = {
    'default': {
        'USER': 'postgres',
        'PASSWORD': '',
        'HOST': ''
    }
}

Then run dbrestore as the system user postgres, which will automatically run psql as the postgres superuser.

sudo su postgres
cd /path/to/your/django/app
source bin/activate # if you're using virtualenv
python manage.py dbrestore

Of course if you are using this database settings, you also need to run the dbbackup command as the postgres user or else the authentication to the Postgres server won't work. The postgres user requires read permissions of your Django project.

The alternative is to simply make your Django DB user a Postgres superuser:
ALTER USER djangouser WITH SUPERUSER;
This is not recommended if you have any additional databases besides your Django database on your Postgres server! IMHO, it's an acceptable and easy workaround though, if your Django app is the only user of the Postgres server.

[snowrry]

Thanks Patrickhallen , I am using a cloud db service similar to AWS Database which won't provide superuser for us. So I think it's better that django-dbbackup can use pg_dump and pg_restore.

[adi-]

yes, pg_dump and pg_restore looks like the best option. Too many problems with psql

[georgema1982]

One option is to allow more optional pg_dump options to be set. In that case, we can specify --no-owner or -O to be able to be restored by any user.

[ZuluPro]

@georgema1982 : You can aleady add options to commands: http://django-dbbackup.readthedocs.io/en/stable/databases.html#dump-suffix-and-restore-prefix

[anx-ckreuzberger]

I have the very same problem and I would really appreciate a way to handle that which does not try to delete fundamental database things like plpgsql, but only access the tables.

[VenturaFranklin]

@ZuluPro if the intended behavior is to allow dump_suffix to add additional parameters to the dump commands it does not seem to work as intended.
Specifically, pg_dump and mysqldump do not seem to allow options after the database name. mongodump does however.
And so if I set the dump_suffix I get the command:
pg_dump --host=localhost --port=5432 --username=postgres --no-password --clean DATABASENAME --no-owner
Which gives the error:

pg_dump: too many command-line arguments (first is "--no-owner")

We need the command:
pg_dump --host=localhost --port=5432 --username=postgres --no-password --clean --no-owner DATABASENAME
I was able to get this by setting the DBBACKUP_CONNECTORS setting and adjusting the dump_cmd like this:

DBBACKUP_CONNECTORS = {
    'default': {
        # 'dump_suffix': '--no-owner', # Does not work because it comes after command
        'dump_cmd': 'pg_dump --no-owner'
    }
}

If you wanted dump_suffix additions (eg. in postgresql ) to work :

cmd += ' {}'.format(self.settings['NAME'])
cmd = '{} {} {}'.format(self.dump_prefix, cmd, self.dump_suffix)

would change for pg_dump and mysqldump commands to:

cmd += ' {}'.format(self.dump_suffix)
cmd += ' {}'.format(self.settings['NAME'])
cmd = '{} {}'.format(self.dump_prefix, cmd)

However, adding the option --no-owner does not work to solve this issue.

From the issue at: https://stackoverflow.com/q/13410631/3166424

Trying the solution at: https://stackoverflow.com/a/46689304/3166424
And setting the dump_suffix to add commands after the dump_cmd:

'dump_suffix': r'| grep -v -E "(CREATE\ EXTENSION\ IF\ NOT\ EXISTS' +
                       r'\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)"',

Does not work, because the shlex.split(command) is used by the dump command
and python's Popen does not work with pipes.

Luckily, I am on a private server and so I followed the solution here: https://stackoverflow.com/a/15452741/3166424
Setting the user as a superuser, but this is a bad suggestion to make a normal user superuser, but a hack for now.

[jonathan-s]

@patrickhallen pg_restore has now been implemented (albeit it's not yet the default, it will be in the next release). Is there anything else one should know about this with regards to permissions for pg_restore?