Re: pg_dump roles support [Review]

[ starting to examine this patch now... ]

Benedek László <laci@benedekl.tvnetwork.hu> writes:
> I also need some feedback about the role support in pg_restore (not
> implemented yet).  Currently pg_restore sets the role during the
> restore process according to the TOC entry in the archive. It may also
> support the --role option (just like pg_dump).  If specified it can be
> used to cancel the effect of the TOC entry and force the emitting of
> the SET ROLE ... command. With emtpy argument it can be used to omit
> the SET ROLE even if it is specified in the archieve. What do you
> think?

I think that the entire concept of putting the rolename into the archive
is broken, and we should not do that part at all.  But we *especially*
should not do it if there is no way to override it.

I see no good reason to assume that the appropriate role to use during
restore is the same as that during dump.  We don't reflect the -U
setting into the dump file, and --role is really just an auxiliary
extension to -U.  What would make sense is to have a --role switch in
pg_restore, but have that function entirely independently of what
happened at dump time, just as is true for -U.

So my thought is:

--role switch for pg_dump and pg_dumpall: sets the role used while
dumping, has no effect on the emitted archive.

--role switch for pg_restore: sets the role used while restoring,
if it's to be different from what -U says.

This ignores the case of plain-text output from pg_dump, but you
don't really need any support for that case, as you can do the
restore like so:
psql -U admin_user target_db
target_db=> SET ROLE superuser;target_db=# \i dumpfile.sql

Comments?        regards, tom lane