Thread: [BUGS] BUG #14817: pg_dumpall is not generating create database statements

The following bug has been logged on the website:

Bug reference:      14817
Logged by:          Alexander Scott
Email address:      alex@yuscott.co.uk
PostgreSQL version: 9.6.5
Operating system:   CentOS Linux release 7.3.1611 (Core)
Description:

I have a fresh install of 9.6.5 with a default postgres database.

I have REVOKED ALL FROM public, created a couple of roles and 1 initial
user.

I wanted to take a dump of the entire cluster to store but the output file
lacks create statements for the databases.  It happily dumps the contents,
and even grant privileges to roles at DB level (CREATE and TEMP).

I literally call with no parameters.
Output is as follows

--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE basic_user;
ALTER ROLE basic_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE developer;
ALTER ROLE developer WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE manager;
ALTER ROLE manager WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN
NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
REPLICATION BYPASSRLS PASSWORD '<password>';
CREATE ROLE initial_user;
ALTER ROLE initial_userWITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION NOBYPASSRLS PASSWORD '<password>';


--
-- Role memberships
--

GRANT basic_user TO developer GRANTED BY postgres;
GRANT developer TO manager GRANTED BY postgres;
GRANT manager TO tony_phillips GRANTED BY postgres;




--
-- Database creation
--

REVOKE CONNECT,TEMPORARY ON DATABASE postgres FROM PUBLIC;
GRANT CREATE ON DATABASE postgres TO manager;
GRANT TEMPORARY ON DATABASE postgres TO developer;
REVOKE CONNECT,TEMPORARY ON DATABASE template1 FROM PUBLIC;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;


\connect postgres

SET default_transaction_read_only = off;

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.5
-- Dumped by pg_dump version 9.6.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE postgres IS 'default administrative connection
database';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

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


--
-- PostgreSQL database dump complete
--

\connect template1

SET default_transaction_read_only = off;

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.5
-- Dumped by pg_dump version 9.6.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: template1; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE template1 IS 'default template for new databases';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database cluster dump complete
--

Thanks

Alex


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

David,

Thanks for the answer.  That makes perfect sense and I feel slightly embarassed that that did not occur to me.

As far as I am concerned, there is a perfectly logic explanation for the behaviour so case closed!

Alex

On Fri, Sep 15, 2017 at 8:20 AM, <alex@yuscott.co.uk> wrote:

The following bug has been logged on the website:

Bug reference:      14817
Logged by:          Alexander Scott
Email address:      alex@yuscott.co.uk
PostgreSQL version: 9.6.5
Operating system:   CentOS Linux release 7.3.1611 (Core)
Description:

I have a fresh install of 9.6.5 with a default postgres database.

I have REVOKED ALL FROM public, created a couple of roles and 1 initial
user.

I wanted to take a dump of the entire cluster to store but the output file
lacks create statements for the databases.

Working as intended (at least per code comments in pg_dumpall.c @1464) - the only databases that exist (postgres, template0, template1) in your installation are the default databases.  pg_dumpall is assuming that the target cluster that you will be restoring into will already contain them (and a freshly created cluster should) and so decides it does not need to create them itself.

You will probably need to describe a more complete use case, with an actual failure, in order to convince someone to change this.

David J.