Re: pg_dump and grants to PUBLIC

On Thu, 2006-11-05 at 18:44 +0200, Martijn van Oosterhout wrote:
> On Thu, May 11, 2006 at 10:41:51AM -0600, Blair Lowe wrote:
> > [root(at)www etc]# psql temp99
> > Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
> >
> > Type: \copyright for distribution terms
> > \h for help with SQL commands
> > \? for help on internal slash commands
> > \g or terminate with semicolon to execute query
> > \q to quit
> >
> > temp99=# select oid from pg_class where relname = 'bbs_auth_access';
> > oid
> > -------
> > 17736
> > (1 row)
> > temp99=# \q
> > [root(at)www etc]# pg_dump -s temp99 | grep 'CREATE.*bbs_auth_access'
> > CREATE TABLE bbs_auth_access (
> > [root(at)www etc]#
>
> Eh? All you've proved here was that a table that is in that database
> also appears in the dump. You need to show a case where the select
> return no rows ie. the table doesn't exist in the database but does
> exist in the dump.

Thanks Martijn for the help!

I think that you are misunderstanding the problem. pgdump exports data
from other users that have tables granted to public.

The table that I show above is one of them, and the owner (thanks to
your great dba tutorial on oid) is bbs_auth_access, and not template1:

select relname from pg_class where oid = 17736;
relname
-----------------
bbs_auth_access
(1 row)

phpbb_mainlandpc=> \q

To access this user's table, I can be logged on as anyone, and that is
expected in SQL when there are public grants.

I am not expecting to get everyone else's stuff when I run pg_dump, only
the stuff in their schema.

TTYL,
Blair.