Re: pg_dump and grants to PUBLIC

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(at)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(at)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(at)scratch:~$
>
>
>
>