Обсуждение: pg_dump and grants to PUBLIC

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.

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/
> 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, 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>
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;

>

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

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

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/
> 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?

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

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

You are confusing "The Schema" with "Schemas". It is a common mistake:

  -n, --schema=SCHEMA      dump the named schema only


Joshua D. Drake

>
> Thanks,
> Blair
>
>


--

            === 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 Friday 12 May 2006 09:50 am, Blair Lowe wrote:
See following-
-n schema
--schema=schema

    Dump the contents of schema only. If this option is not specified, all
non-system schemas in the target database will be dumped.

        Note: In this mode, pg_dump makes no attempt to dump any other
database objects that objects in the selected schema may depend upon.
Therefore, there is no guarantee that the results of a single-schema dump can
be successfully restored by themselves into a clean database.
> >
> > 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Adrian Klaver
aklaver@comcast.net

On Fri, 2006-12-05 at 10:08 -0700, Adrian Klaver wrote:
> On Friday 12 May 2006 09:50 am, Blair Lowe wrote:
> See following-
> -n schema
> --schema=schema
>
>     Dump the contents of schema only. If this option is not specified, all
> non-system schemas in the target database will be dumped.
>
>         Note: In this mode, pg_dump makes no attempt to dump any other
> database objects that objects in the selected schema may depend upon.
> Therefore, there is no guarantee that the results of a single-schema dump can
> be successfully restored by themselves into a clean database.

In postgreSQL version 7.3.4:

user# pg_dump -n <schema_name> > tmp95.sql
pg_dump: invalid option -- n
Try 'pg_dump --help' for more information.

phpbb only works with version 7.x.

TTYL,
Blair.

This option shows up in 7.4.
On Friday 12 May 2006 10:16 am, Blair Lowe wrote:
> On Fri, 2006-12-05 at 10:08 -0700, Adrian Klaver wrote:
> > On Friday 12 May 2006 09:50 am, Blair Lowe wrote:
> > See following-
> > -n schema
> > --schema=schema
> >
> >     Dump the contents of schema only. If this option is not specified,
> > all non-system schemas in the target database will be dumped.
> >
> >         Note: In this mode, pg_dump makes no attempt to dump any other
> > database objects that objects in the selected schema may depend upon.
> > Therefore, there is no guarantee that the results of a single-schema dump
> > can be successfully restored by themselves into a clean database.
>
> In postgreSQL version 7.3.4:
>
> user# pg_dump -n <schema_name> > tmp95.sql
> pg_dump: invalid option -- n
> Try 'pg_dump --help' for more information.
>
> phpbb only works with version 7.x.
>
> TTYL,
> Blair.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Adrian Klaver
aklaver@comcast.net

>> user# pg_dump -n <schema_name> > tmp95.sql
>> pg_dump: invalid option -- n
>> Try 'pg_dump --help' for more information.
>>
>> phpbb only works with version 7.x.


Oh... if you are running 7.3 your pretty much hosed... You need to
upgrade to 7.4 at a minimum.

Sincerely,

Joshua D. Drake

>>
>> TTYL,
>> Blair.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>


--

            === 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 Fri, 2006-12-05 at 10:51 -0700, Joshua D. Drake wrote:
> >> user# pg_dump -n <schema_name> > tmp95.sql
> >> pg_dump: invalid option -- n
> >> Try 'pg_dump --help' for more information.
> >>
> >> phpbb only works with version 7.x.
>
>
> Oh... if you are running 7.3 your pretty much hosed... You need to
> upgrade to 7.4 at a minimum.
>

Sounds doable. Will such an upgrade hose all my client's stuff, or will
a simple rpm -Fvh, and a /etc/init.d/postgreSQL restart do it?

ie:
- are there any .conf files that will be overwritten (this might be an
RPM issue)?

- is the underlying data structure the same, or are there some major
architectural changes that will toast existing applications?

Thanks,
Blair.

Blair Lowe wrote:
> On Fri, 2006-12-05 at 10:51 -0700, Joshua D. Drake wrote:
>>>> user# pg_dump -n <schema_name> > tmp95.sql
>>>> pg_dump: invalid option -- n
>>>> Try 'pg_dump --help' for more information.
>>>>
>>>> phpbb only works with version 7.x.
>>
>> Oh... if you are running 7.3 your pretty much hosed... You need to
>> upgrade to 7.4 at a minimum.
>>
>
> Sounds doable. Will such an upgrade hose all my client's stuff, or will
> a simple rpm -Fvh, and a /etc/init.d/postgreSQL restart do it?

Ehhh. no. You will need to do a full dump, install 7.4 and restore. I
strongly suggest that you use a consultant for this you don't want to
make a mistake.

For full disclosure I run a consulting company for PostgreSQL but there
are many others on this list who I am sure would also be willing to help
you.

> - is the underlying data structure the same, or are there some major
> architectural changes that will toast existing applications?

I recall (I think) that a big difference between 7.3 and 7.4 is some
timestamp / timezone issues.

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 Fri, 2006-12-05 at 10:57 -0700, Joshua D. Drake wrote:
> Blair Lowe wrote:
> > On Fri, 2006-12-05 at 10:51 -0700, Joshua D. Drake wrote:
> >>>> user# pg_dump -n <schema_name> > tmp95.sql
> >>>> pg_dump: invalid option -- n
> >>>> Try 'pg_dump --help' for more information.
> >>>>
> >>>> phpbb only works with version 7.x.
> >>
> >> Oh... if you are running 7.3 your pretty much hosed... You need to
> >> upgrade to 7.4 at a minimum.
> >>
> >
> > Sounds doable. Will such an upgrade hose all my client's stuff, or will
> > a simple rpm -Fvh, and a /etc/init.d/postgreSQL restart do it?
>
> Ehhh. no. You will need to do a full dump, install 7.4 and restore. I
> strongly suggest that you use a consultant for this you don't want to
> make a mistake.

So pg_dump does not work well in 7.3.4, and I have to pg_dump or the
global equivalent to upgrade to 7.4. A bit of a catch 22 in there.

>
> For full disclosure I run a consulting company for PostgreSQL but there
> are many others on this list who I am sure would also be willing to help
> you.

I also run a consulting company, I just have dumb questions before I
leap into chasms, that's all ;)

Been doing Database stuff for 16 years now and I still don't know it
all ;)

>
> > - is the underlying data structure the same, or are there some major
> > architectural changes that will toast existing applications?
>
> I recall (I think) that a big difference between 7.3 and 7.4 is some
> timestamp / timezone issues.

OK. Sounds like I am hosed until I build a new home for the clients so
that they can test the new environment (possibly postgreSQL v.8).

TTYL,
Blair.

> So pg_dump does not work well in 7.3.4, and I have to pg_dump or the
> global equivalent to upgrade to 7.4. A bit of a catch 22 in there.

No, because you will use the 7.4 dump to dump and restore to 7.4 from
the 7.3.

>
>> For full disclosure I run a consulting company for PostgreSQL but there
>> are many others on this list who I am sure would also be willing to help
>> you.
>
> I also run a consulting company, I just have dumb questions before I
> leap into chasms, that's all ;)
>
> Been doing Database stuff for 16 years now and I still don't know it
> all ;)
>
>>> - is the underlying data structure the same, or are there some major
>>> architectural changes that will toast existing applications?
>> I recall (I think) that a big difference between 7.3 and 7.4 is some
>> timestamp / timezone issues.
>
> OK. Sounds like I am hosed until I build a new home for the clients so
> that they can test the new environment (possibly postgreSQL v.8).
>
> TTYL,
> Blair.
>


--

            === 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/