3

I've got an application called BigSense that uses two database users/rolls. One has full permissions and is used by my application to create/adjust table definitions. Then the application starts with the full permission user during startup for database migrations and then drops down to the regular user for operations.

I also have a virtual environment system called vSense that can create VMs for the application in Vagrant and provision them using Ansible. For the MySQL version, the permissions are done in the initial schema:

CREATE DATABASE {{ database.name }};
GRANT ALL ON {{ database.name }}.* TO '{{ database.ddl_username }}'@'{{ servers.bigsense.hostname }}' IDENTIFIED BY '{{ database.ddl_password }}';
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON {{ database.name }}.* TO '{{ database.username }}'@'{{ servers.bigsense.hostname }}' IDENTIFIED BY '{{ database.password }}';

USE {{ database.name }};
CREATE TABLE ddl_info( id INT auto_increment PRIMARY KEY, version INT, installed TIMESTAMP );

So doing a mysql dump/restore to another database/username is fairly trivial (the initialization above is done prior to loading the fixture file).

The issue comes with attempting the same thing with postgres. I use the pg_dump/pg_restore functionality with -Fc for the formatting option. Here is the initial schema before the fixtures are loaded:

CREATE USER {{ database.ddl_username }} CREATEDB PASSWORD '{{ database.ddl_password }}';
CREATE USER {{ database.username }} PASSWORD '{{ database.password }}';

CREATE DATABASE {{ database.name }}
  WITH OWNER = {{ database.ddl_username }}
       ENCODING = 'UTF8';


GRANT ALL ON DATABASE {{ database.name }} to {{ database.username }};


\connect {{ database.name }};

CREATE EXTENSION postgis;
CREATE TABLE ddl_info( id SERIAL, version INT, installed TIMESTAMP );

GRANT SELECT,INSERT,UPDATE,DELETE ON ddl_info TO {{ database.ddl_username }};
GRANT USAGE ON SEQUENCE ddl_info_id_seq TO {{ database.ddl_username }};

In both these situations, the database.name/ddl_username/ddl_password/username/password are replaced with the settings for that environment (contained in an external settings file). The MySQL fixture restore works fine because permissions are set initially for the entire database where as postgres is more fine grained for each individual table.

When I attempt to restore the postgres fixtures I get lots of complaints because it attempts to restore the database with the original database roles (which don't exist / now have different names)

TASK: [database | install fixtures (pgsql)] *********************************** 
failed: [database] => {"changed": true, "cmd": "bzcat /opt/bigsense/gls-data.postgres.bz2 | pg_restore -O -c -Fc -d bigsense", "delta": "0:00:54.358900", "end": "2015-04-28 11:53:29.027809", "rc": 1, "start": "2015-04-28 11:52:34.668909", "warnings": []}
stderr: pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 32324 FK CONSTRAINT fk_sensors_type_id db_gls_ddl
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.sensors" does not exist
    Command was: ALTER TABLE ONLY public.sensors DROP CONSTRAINT fk_sensors_type_id;

I've done a lot of searching and have tried replacing -Fc with various other formatting types, used --no-acls and a variety of other options, but I can't seem to get my postgres fixtures to load into a different database with different roles/users.

I realize the fact that I have two different users complicates this quite a bit (otherwise I should be able to simply load a dump with the user I wish to have permission over the database).

Is there an easy way to dump/restore this database with different users/roles mapped to the various permissions I need?

  • An alternative approach could be just mapping the users between the two DBs. Say, you have role_a in DB a, role_b in DB b, both of them having to act the same in their respective databases. You could create role_a in DB b, granting role_a to it, and there is no need to manipulating the dump. – dezso Apr 28 '15 at 15:32
  • The error you list above is not a permissions error. It's because there is a table that doesn't exist that's attempting to be dropped: could not execute query: ERROR: relation "public.sensors" does not exist. Do you have the full log of your attempted restore? Could you try your pg_restore with the -v flag so we could see more of what it is doing? – Kassandry Aug 30 '15 at 8:54

Your Answer

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

Browse other questions tagged or ask your own question.