In this article I will use $MY_RDS_SU to denote the RDS user managing the RDS instance.

Problem

pg_dumpall is a quick and easy to use tool to export your PostgreSQL database. Unfortunately it does not work well on Amazon RDS.

pg_dumpall -h $MY_RDS_HOST --port $MY_RDS_PORT -U $MY_RDS_SU

Password:
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

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

pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2

Even pg_dump will run into permission issues if you try to dump the database from a user other than the owner of the particular database.

pg_dump -h $MY_RDS_HOST --port $MY_RDS_PORT -U $MY_RDS_SU $MY_RDS_DB

Password:
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation ___________
pg_dump: [archiver (db)] query was: LOCK TABLE ________ IN ACCESS SHARE MODE

Solution

Amazon RDS restricts the capabilities of each RDS user such that no true PostgreSQL superuser exists.

For each database we wish to export we will change the owner of each table to $MY_RDS_SU to work around this issue.

do $$ declare r record;
begin for r in 
select 
  tablename 
from 
  pg_tables 
where 
  schemaname = 'public' loop execute format(
    'alter table public.%I owner to $MY_RDS_SU', 
    r.tablename
  );
end loop;
end;
$$;

After this pg_dump can be used by $MY_RDS_SU. I prefer to include --no-owner with pg_dump if I am going to be importing the database into another PostgreSQL instance with a user other than $MY_RDS_SU

pg_dump --no-owner -h $MY_RDS_HOST --port $MY_RDS_PORT -U $MY_RDS_SU $MY_RDS_DB > $MY_RDS_DB.sql

Comments

comments powered by Disqus