Thread: 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.
Attachment
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.
Attachment
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.
Attachment
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/