Restoring A Dump As A Normal User

From: "Jeff Flowers" <duckfoo(at)fastmail(dot)fm>
To: "PGSQL-Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Restoring A Dump As A Normal User
Date: 2004-12-06 17:27:59
Message-ID: 1102354079.10622.210182885@webmail.messagingengine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I want to be able to dump and restore my personal databases without
having to be the PostgreSQL super user, so I performed the following
step using a test database:

GOO:~ jeff$ pg_dump -O birthdays > dump.txt

I then dropped the database and did the following:

GOO:~ jeff$ createdb birthdays
CREATE DATABASE
GOO:~ jeff$ psql -d birthdays -f dump.txt
SET
SET
REVOKE
psql:dump.txt:14: ERROR: permission denied for schema public
SET
CREATE TABLE
CREATE VIEW
psql:dump.txt:57: ERROR: must be owner of schema public

The lines that failed in dump.txt, with line numbers added, are:

14. GRANT ALL ON SCHEMA public TO PUBLIC;
57. COMMENT ON SCHEMA public IS 'Standard public schema';

I looked at the resulting restored birthdays database, and it looks
correct. Do I need to worry about these error messages?

My entire dump.txt file is included below.

Thanks,

Jeff Flowers

-------dump.txt listing-------

GOO:~ jeff$ cat dump.txt
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

--
-- TOC entry 4 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

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

SET search_path = public, pg_catalog;

--
-- TOC entry 5 (OID 20151)
-- Name: listing; Type: TABLE; Schema: public; Owner: jeff
--

CREATE TABLE listing (
"last" character varying(30),
"first" character varying(30),
bday date
);

--
-- TOC entry 6 (OID 20155)
-- Name: nice; Type: VIEW; Schema: public; Owner: jeff
--

CREATE VIEW nice AS
SELECT listing."last", listing."first", listing.bday FROM listing
ORDER BY listing."last", listing."first";

--
-- Data for TOC entry 7 (OID 20151)
-- Name: listing; Type: TABLE DATA; Schema: public; Owner: jeff
--

COPY listing ("last", "first", bday) FROM stdin;
Flowers Zoe 2003-05-06
Flowers Heather 1977-10-15
Flowers Jeff 1967-09-08
\.

--
-- TOC entry 3 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';

Browse pgsql-novice by date

  From Date Subject
Next Message Sudhakar Kurumella 2004-12-06 19:01:54 Import .sql file
Previous Message Keith Worthington 2004-12-06 16:51:29 loading data