Re: pg_dump and grants to PUBLIC

On Tue, 2006-09-05 at 13:19 +1200, Brent Wood wrote:
>
> On Mon, 8 May 2006, Blair Lowe wrote:
>
> > Hi,
> >
> > I have had this problem for a while, and have not been able to find
> > anything in the archives or on search engines:
> >
> > If I want to back up a client's database on our shared web server, I
> > would type:
> >
> > pg_dump <database_name>

Thanks Brent, comments below ...
>
> try pgdump -t <table> .... to just get the tables you want exported.

Good idea, but too many clients, too many tables and too little time :)

>
> or implement a separate schema (not public) for the tables your app uses &
> use pg_dump -n <schema> to avoid all the public tables in the public
> schema.

I cannot control what my clients do, and I want to back them all up in
separate areas so that they cannot see each other's data in a backup. I
like this idea, but I would have to do this for each client, no?

>
> or fire up a new postgres server (postmaster) process at a different port
> talking to a separate Postgres database location, so other users don't
> create superfluous tables, etc in "your" database. Any application should
> take a port as an argument in the connect parameter string....

Expensive to run tons of postgres at the same time. The ultimate
solution is to run a Xen server so I don't have to worry about any
stupid things that my clients or their software packages do.

>
>
> HTH,
>
> Brent Wood
>
>
> > Since we are running a shared server, and since crappy (only because of
> > this problem) off the shelf database open source software such as
> > oscommerce, or phpBB2 grants access to public rather than the web user
> > "www" or "nobody", when I do a pg_dump for a database, I get all the
> > databases on the system that grant to PUBLIC being dumped with with
> > database that I want.
> >
> > To restore, I need to go in and prune out all the extra junk that was
> > granted to PUBLIC by other users in other databases - very time
> > consuming.
> >
> > How can I use pg_dump to get JUST the database in th argument, and not
> > other tables and databases that have granted to PUBLIC?
> >
> > Altering my client's software to grant to "nobody" is not practical.
> >
> > Thanks in advance,
> > Blair.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >