* Implement and use CREATE ROLE IF NOT EXISTS and just throw a warning or handle the "role already exists" error message gracefully.

+

* A) Implement and use CREATE ROLE IF NOT EXISTS and just throw a warning or handle the "role already exists" error message gracefully.

−

* Use a new option i.e. ''--reuse-roles-in-conflict'' to behave like suggestion a). If this option is not used, pg_restore will stop with a fatal error when a role already exist.

+

* B) Use a new option i.e. ''--reuse-roles-in-conflict'' to behave like suggestion a). If this option is not used, pg_restore will stop with a fatal error when a role already exist.

−

* Use a new option i.e. ''--on-roles-error-stop'' to stop with a fatal error when a role already exist. If this option is not used pg_restore will behave like suggestion a).

+

* C) Use a new option i.e. ''--on-roles-error-stop'' to stop with a fatal error when a role already exist. If this option is not used pg_restore will behave like suggestion a).

−

* Use a new option i.e. ''--rename-roles-in-conflict'' to rename the roles that already exists. If this option is not used, pg_restore will stop with a fatal error when a role already exist.

+

* D) Use a new option i.e. ''--rename-roles-in-conflict'' to rename the roles that already exists. If this option is not used, pg_restore will stop with a fatal error when a role already exist.

</div>

</div>

Revision as of 15:43, 12 November 2013

Background

Currently only pg_dumpall emits database and roles attributes, such as CREATE ROLE, ALTER ROLE, ALTER DATABASE SET and ALTER ROLE SET commands.

We would like to have a pg_dump solution that can generate in one step all the necessary pieces of information needed when restoring or cloning a database (schema, data, privileges, users and alter database/role data). A discussion and a proposal has been started via pgsql-hackers@postgresql.org and this page attempts to summarize what has been discussed there.

Proposal

pg_dump will also deliver information about "ALTER DATABASE SET" data for a given database when the option --create is used.

pg_dump will deliver information about ROLES used and "ALTER ROLE SET" data for a given database when a new option i.e. --roles-global is used.

pg_restore will restore ROLE information when used with a new option i.e. --roles-global and "ALTER DATABASE SET" information when used with the --create option.

We need to do something with how pg_restore will handle ROLES information because some security concerns when restoring roles that already exists on the target server.

Some of the suggestions to handle this are:

A) Implement and use CREATE ROLE IF NOT EXISTS and just throw a warning or handle the "role already exists" error message gracefully.

B) Use a new option i.e. --reuse-roles-in-conflict to behave like suggestion a). If this option is not used, pg_restore will stop with a fatal error when a role already exist.

C) Use a new option i.e. --on-roles-error-stop to stop with a fatal error when a role already exist. If this option is not used pg_restore will behave like suggestion a).

D) Use a new option i.e. --rename-roles-in-conflict to rename the roles that already exists. If this option is not used, pg_restore will stop with a fatal error when a role already exist.