Re: pg_dump and grants to PUBLIC

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>

try pgdump -t <table> .... to just get the tables you want exported.

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.

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....

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
>