pg_dump and grants to PUBLIC

On Mon, 08 May 2006 15:47:13 -0600
Blair Lowe <postgresql@zedemail.ca> 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>

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

Blair,

<UNTESTED>
How about setting up a seperate schema (private), adding your
customer's database to it (leaving it in public) and then backing up
private.customerdb?
</UNTESTED>

John Purser

--
You need more time; and you probably always will.

On Mon, May 08, 2006 at 03:47:13PM -0600, Blair Lowe wrote:

If I want to back up a client's database on our shared web server, I
would type:

pg_dump <database_name>

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.

pg_dump only ever connects to a single database, so it seems very
unlikely that it would dump a different database. Could you be a little
more specific about what is happening? Oh, and the version of postgres
and platform you are using would also be helpful.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

From each according to his ability. To each according to his ability to litigate.

On Tue, 2006-09-05 at 00:09 +0200, Martijn van Oosterhout wrote:

On Mon, May 08, 2006 at 03:47:13PM -0600, Blair Lowe wrote:

If I want to back up a client's database on our shared web server, I
would type:

pg_dump <database_name>

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.

pg_dump only ever connects to a single database, so it seems very
unlikely that it would dump a different database. Could you be a little
more specific about what is happening? Oh, and the version of postgres
and platform you are using would also be helpful.

postgreSQL 7.3.4 on rh 9

Not sure how else to describe this: basically if you type in pg_dump
<databasename> then all databases on the system that have table grants
to public are also in the backup .sql file with connect commands to the
other database users.

Restoring, therefor, is not possible by that user because the restore
procedure wants to restore other databases that are not owned by the
owner of the database that was supposed to be backed up.

TTYL,
Blair.

Not sure how else to describe this: basically if you type in pg_dump
<databasename> then all databases on the system that have table grants
to public are also in the backup .sql file with connect commands to the
other database users.

Do you mean pg_dumpall? Because pg_dump will ONLY dump the database you
specify, nothing else.

Joshua D. Drake

Restoring, therefor, is not possible by that user because the restore
procedure wants to restore other databases that are not owned by the
owner of the database that was supposed to be backed up.

TTYL,
Blair.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Mon, 2006-08-05 at 15:54 -0700, Joshua D. Drake wrote:

Not sure how else to describe this: basically if you type in pg_dump
<databasename> then all databases on the system that have table grants
to public are also in the backup .sql file with connect commands to the
other database users.

Do you mean pg_dumpall? Because pg_dump will ONLY dump the database you
specify, nothing else.

That would be nice, but it is not true.

Try this:

as user1: createdb test1
psql test1

create table stuff1 (

a CHAR(3)
);

as user2: createdb test2
pgsql test2

create table stuff2 (

a CHAR(3)
);

grant all on stuff2 to PUBLIC;

now again as user1:
pg_dump test1

You should see the table stuff2 of test2 in there.

phpBB2 and many other mysql based software packages grant all tables to
PUBLIC so that the web user can alter stuff.

Blair.

Blair Lowe <postgresql@zedemail.ca> writes:

On Mon, 2006-08-05 at 15:54 -0700, Joshua D. Drake wrote:

Do you mean pg_dumpall? Because pg_dump will ONLY dump the database you
specify, nothing else.

That would be nice, but it is not true.

Joshua is 100% correct about this; I do not know what you are seeing
exactly, but your interpretation of it is wrong (unless someone has
actually substituted pg_dumpall for pg_dump on your machine).

My best theory at the moment is that somewhere along the way you (or
someone) mistakenly created a bunch of stuff in the template1
database, and it is therefore getting copied into any new databases
made by CREATE DATABASE. See the discussion of template databases
in the manual.

If this is what has happened, it's possible to drop template1 and
recreate a pristine version from template0; there's a step-by-step
recipe on techdocs IIRC.

regards, tom lane

Hello,

What version of PostgreSQL is this that you are using? Because it
behaves like no other postgresql I have ever seen. Please see below:

postgres@scratch:~$ bin/initdb -D data2
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.

creating directory data2 ... ok
creating directory data2/global ... ok
creating directory data2/pg_xlog ... ok
creating directory data2/pg_xlog/archive_status ... ok
creating directory data2/pg_clog ... ok
creating directory data2/pg_subtrans ... ok
creating directory data2/pg_twophase ... ok
creating directory data2/pg_multixact/members ... ok
creating directory data2/pg_multixact/offsets ... ok
creating directory data2/base ... ok
creating directory data2/base/1 ... ok
creating directory data2/pg_tblspc ... ok
selecting default max_connections ... 100
selecting default shared_buffers ...
bin1000
creating configuration files ... /ok
creating template1 database in data2/base/1 ... ok
initializing pg_authid ... pgok
enabling unlimited row size for system tables ... _ctok
initializing dependencies ... l -ok
creating system views ... D daok
loading pg_description ... ta2ok
creating conversions ... start
ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

bin/postmaster -D data2
or
bin/pg_ctl -D data2 -l logfile start

postgres@scratch:~$
postgres@scratch:~$ bin/pg_ctl -D data2 start
postmaster starting
postgres@scratch:~$ LOG: database system was shut down at 2006-05-08
16:28:44 PDT
LOG: checkpoint record is at 0/38FFE0
LOG: redo record is at 0/38FFE0; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 565; next OID: 10794
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"

postgres@scratch:~$ bin/psql -U postgres
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# create user test1;
CREATE ROLE
postgres=# create user test2;
CREATE ROLE
postgres=# create database test1 owner test1;
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
CREATE DATABASE
postgres=# create database test2 owner test2;
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
CREATE DATABASE
postgres=# \q
postgres@scratch:~$ psql -U test1 test1;
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test1=> create table foo(id bigserial);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
CREATE TABLE
test1=> \q
postgres@scratch:~$ psql -U test2 test2;
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test2=> create table foo2(id bigserial);
NOTICE: CREATE TABLE will create implicit sequence "foo2_id_seq" for
serial column "foo2.id"
NOTICE: CREATE TABLE will create implicit sequence "foo2_id_seq" for
serial column "foo2.id"
CREATE TABLE
test2=> \q
postgres@scratch:~$ pg_dump test1;
--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: foo; Type: TABLE; Schema: public; Owner: test1; Tablespace:
--

CREATE TABLE foo (
id bigserial NOT NULL
);

ALTER TABLE public.foo OWNER TO test1;

--
-- Name: foo_id_seq; Type: SEQUENCE SET; Schema: public; Owner: test1
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('foo', 'id'),
1, false);

--
-- Data for Name: foo; Type: TABLE DATA; Schema: public; Owner: test1
--

COPY foo (id) FROM stdin;
\.

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

postgres@scratch:~$

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

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

Show quoted text

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

On Mon, 2006-08-05 at 16:32 -0700, Joshua D. Drake wrote:

Hello,

What version of PostgreSQL is this that you are using? Because it

]# rpm -qi postgresql
Name : postgresql Relocations: (not
relocateable)
Version : 7.3.4 Vendor: Red Hat, Inc.
Release : 3.rhl9 Build Date: Tue 04 Nov 2003
13:45:53 MST
Install Date: Mon 29 Dec 2003 15:52:53 MST Build Host:
porky.devel.redhat.com
Group : Applications/Databases Source RPM:
postgresql-7.3.4-3.rhl9.src.rpm
Size : 6332200 License: BSD
Signature : DSA/SHA1, Tue 11 Nov 2003 15:48:30 MST, Key ID
219180cddb42a60e
Packager : Red Hat, Inc. <http://bugzilla.redhat.com/bugzilla&gt;
URL : http://www.postgresql.org/
Summary : PostgreSQL client programs and libraries.
Description :
PostgreSQL is an advanced Object-Relational database management system
(DBMS) that supports almost all SQL constructs, including
transactions, subselects, and user-defined types and functions. The
postgresql package includes the client programs and libraries that you
need to access a PostgreSQL DBMS server. These PostgreSQL client
programs are programs that directly manipulate the internal structure
of PostgreSQL databases on a PostgreSQL server.These client programs
can be located on the same machine with the PostgreSQL server, or may
be on a remote machine which accesses a PostgreSQL server over a
network connection. This package contains the client libraries for C
and C++, as well as command-line utilities for managing PostgreSQL
databases on a PostgreSQL server.

If you want to manipulate a PostgreSQL database on a remote PostgreSQL
server, you need this package. You also need to install this package
if you are installing the postgresql-server package.

postgres@scratch:~$ psql -U test2 test2;
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test2=> create table foo2(id bigserial);
NOTICE: CREATE TABLE will create implicit sequence "foo2_id_seq" for
serial column "foo2.id"
NOTICE: CREATE TABLE will create implicit sequence "foo2_id_seq" for
serial column "foo2.id"
CREATE TABLE

Now you need to grant that table to PUBLIC at this point.

in PSQL: GRANT ALL ON test2 TO PUBLIC;

Show quoted text

test2=> \q
postgres@scratch:~$ pg_dump test1;
--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: foo; Type: TABLE; Schema: public; Owner: test1; Tablespace:
--

CREATE TABLE foo (
id bigserial NOT NULL
);

ALTER TABLE public.foo OWNER TO test1;

--
-- Name: foo_id_seq; Type: SEQUENCE SET; Schema: public; Owner: test1
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('foo', 'id'),
1, false);

--
-- Data for Name: foo; Type: TABLE DATA; Schema: public; Owner: test1
--

COPY foo (id) FROM stdin;
\.

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

postgres@scratch:~$

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.

Show quoted text

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

Blair Lowe <postgresql@zedemail.ca> writes:

On Mon, 2006-08-05 at 16:32 -0700, Joshua D. Drake wrote:

What version of PostgreSQL is this that you are using? Because it

]# rpm -qi postgresql
Name : postgresql Relocations: (not
relocateable)
Version : 7.3.4 Vendor: Red Hat, Inc.

^^^^^

That does not square with this:

postgres@scratch:~$ psql -U test2 test2;
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

^^^^^

and the psql session is talking to test2 but you're dumping an
unrelated database:

postgres@scratch:~$ pg_dump test1;

I see no evidence here that test1 didn't already have "foo" in it.

regards, tom lane

On Tue, 2006-09-05 at 12:25 -0400, Tom Lane wrote:

Blair Lowe <postgresql@zedemail.ca> writes:

On Mon, 2006-08-05 at 16:32 -0700, Joshua D. Drake wrote:

What version of PostgreSQL is this that you are using? Because it

]# rpm -qi postgresql
Name : postgresql Relocations: (not
relocateable)
Version : 7.3.4 Vendor: Red Hat, Inc.

^^^^^

That does not square with this:

postgres@scratch:~$ psql -U test2 test2;
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

^^^^^

and the psql session is talking to test2 but you're dumping an
unrelated database:

Please read the ">>" that was from Joshua who was testing on 8.1.3, not
me. I am running 7.3.4.

In my test I do not see stuff2 either. The problem here is that I have
sensitive production data, so my tests are hard to read, and not able to
submit here.

So how do I recreate this template thing without killing production
data?

TTYL,
Blair.

Tom Lane wrote:

Blair Lowe <postgresql@zedemail.ca> writes:

On Mon, 2006-08-05 at 16:32 -0700, Joshua D. Drake wrote:

What version of PostgreSQL is this that you are using? Because it

]# rpm -qi postgresql
Name : postgresql Relocations: (not
relocateable)
Version : 7.3.4 Vendor: Red Hat, Inc.

^^^^^

That does not square with this:

postgres@scratch:~$ psql -U test2 test2;
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

^^^^^

and the psql session is talking to test2 but you're dumping an
unrelated database:

postgres@scratch:~$ pg_dump test1;

I see no evidence here that test1 didn't already have "foo" in it.

regards, tom lane

Tom, you are commenting on my example of why his doesn't make sense :).
I used 8.1.3 to test his theory, but he is running 7.3.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Tue, May 09, 2006 at 10:52:32AM -0600, Blair Lowe wrote:

In my test I do not see stuff2 either. The problem here is that I have
sensitive production data, so my tests are hard to read, and not able to
submit here.

You don't need to show any data, just the schema will be enough. An
example you could show us would be something like below. Replace
'mydatabase' with a database and 'sometable' with a table name you know
is not is 'mydatabase' and so should not be in the dump but you say is
because it's in some other database.

$ psql mydatabase
psql version x.x.x
mydatabase> select oid from pg_class where relname = 'sometable';
oid
-------
(0 rows)
mydatabase> \q
$ pg_dump -s mydatabase | grep 'CREATE.*sometable'
< show us the output here >

If it turns out it is in template1, you can fix this without deleting
any production data. Easiest is just login and delete stuff, though you
can recreate it using the steps in the docs.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

From each according to his ability. To each according to his ability to litigate.

On Tue, 2006-09-05 at 21:08 +0200, Martijn van Oosterhout wrote:

On Tue, May 09, 2006 at 10:52:32AM -0600, Blair Lowe wrote:

In my test I do not see stuff2 either. The problem here is that I have
sensitive production data, so my tests are hard to read, and not able to
submit here.

You don't need to show any data, just the schema will be enough. An
example you could show us would be something like below. Replace
'mydatabase' with a database and 'sometable' with a table name you know
is not is 'mydatabase' and so should not be in the dump but you say is
because it's in some other database.

$ psql mydatabase
psql version x.x.x
mydatabase> select oid from pg_class where relname = 'sometable';
oid
-------
(0 rows)
mydatabase> \q
$ pg_dump -s mydatabase | grep 'CREATE.*sometable'
< show us the output here >

[root@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@www etc]# pg_dump -s temp99 | grep 'CREATE.*bbs_auth_access'
CREATE TABLE bbs_auth_access (
[root@www etc]#

If it turns out it is in template1, you can fix this without deleting
any production data. Easiest is just login and delete stuff, though you
can recreate it using the steps in the docs.

What is the SQL to find the oid 17736?

Show quoted text

Hope this helps,

On Thu, May 11, 2006 at 10:41:51AM -0600, Blair Lowe wrote:

[root@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@www etc]# pg_dump -s temp99 | grep 'CREATE.*bbs_auth_access'
CREATE TABLE bbs_auth_access (
[root@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.

What is the SQL to find the oid 17736?

Well, the inverse of what you did above.

select relname from pg_class where oid = <oid>;

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

From each according to his ability. To each according to his ability to litigate.

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@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@www etc]# pg_dump -s temp99 | grep 'CREATE.*bbs_auth_access'
CREATE TABLE bbs_auth_access (
[root@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.

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.

O.k. if you are actually using schemas, then pass the schema parameter
to pg_dump and you won't get other information.

Joshua D. Drake

TTYL,
Blair.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Thu, 2006-11-05 at 11:09 -0700, Joshua D. Drake wrote:

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.

O.k. if you are actually using schemas, then pass the schema parameter
to pg_dump and you won't get other information.

Thanks Joshua,

According to the man page:
-s
--schema-only
Dump only the schema (data definitions), no data.

I need data too.

What are the command line options to get a user's schema, and a user's data?

Thanks,
Blair