Default read only user in Postgres can't make backup in fly.io

[Ppallesen]

In order to avoid a situation where a developer deletes the production database I’m looking to make a dedicated read only user that can be shared by the developers such that they can take a production dump and load it in to there local machine. In postgres 14 this became standardlized to

CREATE ROLE backup WITH ROLE pg_read_all_data LOGIN PASSWORD '<password>';

How can I create readonly user for backups in PostgreSQL?

But when then create it and run a pg_dump I get the following error:

pg_dump postgres://read_only_user:<password>@localhost:6543/my-db > dump.sql

pg_dump: error: query failed: ERROR: permission denied for schema repmgr
pg_dump: detail: Query was: LOCK TABLE repmgr.nodes IN ACCESS SHARE MODE

Can you help fix it for me?

[mayailurus]

@Ppallesen

CREATE ROLE backup WITH ROLE pg_read_all_data LOGIN PASSWORD '<password>';

It’s counterintuitive, , due to Postgres’s unification of users and groups, but the above actually did the opposite of what you intended: it made pg_read_all_data be a member of backup.

As a simpler example,

# create role tomato with role pg_read_all_data;
# \dg tomato
            List of roles
 Role name |  Attributes  | Member of 
-----------+--------------+-----------
 tomato    | Cannot login | {}          ← not there!

# \dg pg_read_all_data
                List of roles
    Role name     |  Attributes  | Member of 
------------------+--------------+-----------
 pg_read_all_data | Cannot login | {tomato}  ← oops...

Try the unusual-looking WITH IN ROLE syntax (which was formerly WITH IN GROUP) instead of plain WITH ROLE…

# create role parsnip with in role pg_read_all_data;
# \dg parsnip
                 List of roles
 Role name |  Attributes  |     Member of      
-----------+--------------+--------------------
 parsnip   | Cannot login | {pg_read_all_data}