Re: pg_dump roles support

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> =?ISO-8859-1?Q?Benedek_L=E1szl=F3?= <laci(at)benedekl(dot)tvnetwork(dot)hu> writes:
> > pg_dumpall now just passes the --role option to pg_dump. What do you
> > think, is it enough
> > or it should issue the SET ROLE TO ... command in its own session too?
>
> I think it would have to, in the general case. Consider the possibility
> that someone has restricted access to the system catalogs, for instance.

I would agree with this. pg_dumpall should do the 'set role' in its
session as well.

> You have missed an important component of Stephen's original proposal,
> which was the point that something similar is needed on the restore
> side. This is a little bit tricky since the context at restore time
> is not necessarily the same as the context at dump time. When using
> an archive file it's not a problem: the behavior can be driven off a
> --role switch to pg_restore, and this is independent of what pg_dump
> did. In a dump to plain text, though, I'm not sure what to do. The
> simplest design would have pg_dump's --role switch control both
> what it does in its own connection to the source database, and what it
> puts into the output script. I'm not sure that's adequate though.

This makes sense to me and I feel it's adequate. If necessary, people
can post-process their .sql files using sed or something similar.
That's made reasonably easy by having a 'set role' in the .sql file. I
actively dislike the idea that pg_restore would modify the input stream
from a text file, even if it was passed a --role switch.

> Is it worth having two different switches for the two cases? If we
> think it's a corner case to need different role IDs, we could just
> leave it like that and tell anyone who needs different behaviors that
> they have to go through an archive file and pg_restore. Stephen,
> you were the one who wanted this in the first place, what's your
> use-cases look like?

My primary use cases are performing a pg_dump when logging in as one
user but needing the permissions of another role, followed by loading
the data into another system when logging in as one user and needing to
set role first to another. In at least 90% of those cases, that role is
postgres, and in the other 10% most, if not all, are the same role on
both sides. There are a few cases where we might change the restore-as
role away from the dumped-as role, but we're happy to use pg_restore to
handle that, or take care of changing the role in the .sql file (which
is what we tend to use, honestly) using sed or similar.

Alot of this is driven from the fact that we don't allow admins to
remotely connect directly as postgres (akin to disabling remote root
logins in sshd_config via PermitRootLogin, and for the same reasons).
They must authenticate and connect as their own user first and then use
'set role postgres;' to gain superuser rights. Not being able to have
pg_dump do that set role has been quite frustrating as we use it
extensively for transferring data between systems.

> Some other review nitpicking:

I agree with the other comments.

Thanks,

Stephen