Re: pg_dump and grants to PUBLIC

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/