Creating read-only PostgreSQL database users for pg_dump and pg_dumpall

See also mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces. I’m linking this because I use both articles on a regular basis.

Sometimes you want a user which can only dump the database, but nothing else. Fortunately, I searched, and found a solution. I’m writing it down so I only have to search this blog.

I want the user rsyncer to have read-rights on the database freebsddiary.org, so it can run pg_dump.

Similarly, I want the same user to be able to run pg_dumpall -g and get users/roles/passwords/etc.

Here is what you’ll get if you try to dump the globals without the required permissions.

[rsyncer@dbclone ~]$ pg_dumpall -g
pg_dumpall: could not connect to database "template1": FATAL:  role "rsyncer" does not exist

[rsyncer@dbclone ~]$ 

Oh, yeah, let’s create the user first.

psql postgres
create user rsyncer with password 'notmyactualpassword';

Now, with that user created, we get farther, but not all the way:

[rsyncer@dbclone ~]$ pg_dumpall -g
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'SQL_ASCII';
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, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2
[rsyncer@dbclone ~]$

Depending on the database version, the message might be:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid

The difference being table, not relation.

To solve that permissions issue, I originally did this:

psql postgres
grant select on all tables    in schema pg_catalog to rsyncer;
grant select on all sequences in schema pg_catalog to rsyncer;

EDIT: 2020-12-31 but now I do this:

[dan@pg03:~] $ sudo su -l postgres
$ psql postgres
psql (13.1)
Type "help" for help.

postgres=# grant select on pg_authid to rsyncer;

Now when we try the dump, we get:

[rsyncer@dbclone ~]$ pg_dumpall -g
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE rsyncer;
ALTER ROLE rsyncer WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5cd3276a9ed743fdfa0695607ffab6496';






--
-- PostgreSQL database cluster dump complete
--

[rsyncer@dbclone ~]$ 

There, now we can get all the globals.

Next, we want the user to have read-only access on the database[s] we dump.

My solution involves granting read-only accesss on tables and sequences. That’s enough for my databases.

psql freebsddiary.org
grant connect on database "freebsddiary.org" to rsyncer;

grant select on all tables    in schema public to rsyncer;
grant select on all sequences in schema public to rsyncer;

I also added this to the pg_hba.conf file for this PostgreSQL server:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   freebsddiary.org rsyncer                                md5
local   template1        rsyncer                                md5