Postgres Bug #8545 pg_dump fails to dump database grants

1. Postgres Bug #8545pg_dump fails to dump database grants By: Lloyd Albin 11/5/2013

2. Performing backups Most people use pg_dumpall to dump the servers global objects such as roles and tablespaces but no databases. Then they will use pg_dump to backup each individual database in a compressed format and possibly multi-job. pg_dumpall does not do either of these. When upgrading to a new server, we noticed that some of our permissions did not transfer and after looking into it we found that pg_dump fails to dump the database level grants. I have tested and found this to be an issue with Postgres 9.0.12, 9.2.4, 9.3.0, & 9.3.1

3. Create a test database The first thing we need to do is to create a test databases for our example. createdb -h sqltestlloyd_test CREATE DATABASE lloyd_test WITH OWNER = postgres ENCODING = 'UTF8' TEMPLATE = template0;

4. Creating an extra user Here are two different ways to add a second user to the server. createuser –h sqltest –e dw CREATE ROLE dwWITH LOGIN PASSWORD 'test';

5. Database Grant This allows user/role dw to be able to create new schemas within the lloyd_test database. GRANT CREATE ON DATABASE lloyd_testTO dw;

6. Backing up the Globals This is the normal command that most people will use to backup their global information, ROLES, TABLESPACES, etc. pg_dumpall --globals-only -h sqltest > cluster.sql

7. Backing up the database The deletion fails just as expected, due to the foreign key relationship. pg_dump -h sqltest -f lloyd_test.pgdump -Fc lloyd_test

8. Looking at the cluster Here is the contents of the cluster.sql file. No database grants here and we would not expect them here anyways because the database has not been created yet. psql –h sqltest –d lloyd_test_1 –f delete.sql

9. How to look at the pgdump file Here are two ways to look at the contents of the compressed dump file format. The directory/index list of the file. Output the SQL into a file. pg_restore -l lloyd_test.pgdump pg_restorelloyd_test.pgdump > lloyd_test.sql

10. The List Here is the list format of the pgdump file and it does not show the database grant. ; ; Archive created at Mon Oct 14 12:08:42 2013 ; dbname: lloyd_test ; TOC Entries: 8 ; Compression: -1 ; Dump Version: 1.12-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 9.3.0 ; Dumped by pg_dump version: 9.3.0 ; ; ; Selected TOC Entries: ; 2527; 1262 9595228 DATABASE – lloyd_testpostgres 5; 2615 2200 SCHEMA - public postgres 2528; 0 0 COMMENT - SCHEMA public postgres 2529; 0 0 ACL - public postgres 170; 3079 12350 EXTENSION - plpgsql 2530; 0 0 COMMENT - EXTENSION plpgsql

11. The SQL Here is the SQL that would be run during a pg_restore process. I have removed some of the comments and blank lines so that the file easily fits on this slide. Please note that this does not show the database grant. -- PostgreSQL database dump SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- 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; 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

12. Response about the issue On 21-10-2013 19:17, lalbin@fhcrc.org wrote: Most people I know, including myself, use pg_dumpall -g to dump out the globals such as user accounts and then use pg_dump -Fc or -Fd to dump out their databases in the compressed formats. When doing this method the database level grants have been missed, although if people used the pg_dumpall to dump the entire server they will be dumped, but not in a compressed format that they can use with pg_restores multi-job. Lloyd, this is not a bug. pg_dump doesn't dump global objects such as databases, roles and tablespaces. Hence grants are not presented in your dump. If you want global objects use pg_dumpall. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

13. My response to them Euler, Basically pg_dump is to dump everything about a database, and since the grant is on the database itself, it should be part of the database dump especially since it is not a global value across databases like roles and tablespaces. But let's go with you theory that it is a global object that pg_dumpall should dump. Well it does not dump it when you dump the globals via pg_dumpall via the -g because pg_dumpall does not consider databases and their objects to be global objects, see the pg_dumpall docs. In fact there is no dump flag to get this information dumped so that it can be recreated on a new server or an upgraded server. This is the true bug that I am showing, that the backup methods that most people use, will not backup this database object and they will lose these permissions when upgrading servers unless they manually document and execute the changes that they need to make. Lloyd

14. Silence