Re: pg_dump roles support

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Far as I can tell anyway. What would you suggest? The point here is
> > that joesysadmin shouldn't get full postgres privs on login since most
> > of the time he won't need them.
>
> It's sane to set up a manually-used admin account that way, I agree.
> What doesn't follow is that an account configured for manual use should
> be used for non-interactive stuff like pg_dump.

I strongly disagree that pg_dump isn't to be used manually, which I
think is what you were getting at. We use it a great deal to dump
individual schemas and copy them between systems. We *don't* use it
anymore for full database dumps (something which was done in the past)
because we use PITR instead. In fact, I encourage my users to use it to
copy schema structures around when they need a seperate area for
development or testing.

What's frustrating is when an admin needs to copy a schema that he
doesn't directly have rights to (another user's schema, or a schema used
by a site or something) he has to login to the database server
(something I'd like to minimize anyway- almost everything can easily be
done from psql), su to root, su to postgres, do the pg_dump on the box,
and then work out copying it off the box.

I suppose I could write my own pg_dump that actually used psql
underneath or add it's functionality to our perl toolkit (it's damn
handy to be able to change permissions on every table in a schema with
one command, and it takes role as an argument, heh), but rewriting the
dependency handling and whatnot isn't something I'm really keen to do.

Thanks,

Stephen