Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add pg_read_all_data and pg_write_all_data roles
- 6c3ffd697e22 14.0 cited

BUG #19379: Role pg_read_all_data don't allowed read large objects

PG Bug reporting form <noreply@postgresql.org> — 2026-01-15T11:57:14Z

The following bug has been logged on the website:

Bug reference:      19379
Logged by:          Misha Shaygu
Email address:      long76.git@mail.ru
PostgreSQL version: 17.7
Operating system:   Kubuntu 24.04
Description:        

My goal: create role for backup any database on server

Steps:
1. CREATE USER backup_user;
2. GRANT pg_read_all_data TO backup_user;
3. pg_dump my_db
4. got error to read large object

Following by links
https://www.postgresql.org/docs/17/predefined-roles.html
https://www.postgresql.org/docs/17/lo-implementation.html
"SELECT privileges are required to read a large object" and role
"pg_read_all_data" grant it, but it don't work!

Please fix it, thanks!

BUG #19379: Role pg_read_all_data don't allowed read large objects

David G. Johnston <david.g.johnston@gmail.com> — 2026-01-15T13:36:35Z

On Thursday, January 15, 2026, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      19379
> Logged by:          Misha Shaygu
> Email address:      long76.git@mail.ru
> PostgreSQL version: 17.7
> Operating system:   Kubuntu 24.04
> Description:
>
> My goal: create role for backup any database on server
>
> Steps:
> 1. CREATE USER backup_user;
> 2. GRANT pg_read_all_data TO backup_user;
> 3. pg_dump my_db
> 4. got error to read large object
>
> Following by links
> https://www.postgresql.org/docs/17/predefined-roles.html
> https://www.postgresql.org/docs/17/lo-implementation.html
> "SELECT privileges are required to read a large object" and role
> "pg_read_all_data" grant it, but it don't work!
>
> Please fix it, thanks!
>

The docs you link note that all data is “tables, views, sequences”.  Large
objects are not listed.  Maybe that means the name is a bit misleading but
it’s working as documented.

Likewise, the LO page doesn’t say anything about read all being applicable.

David J.

Re: BUG #19379: Role pg_read_all_data don't allowed read large objects

Andres Freund <andres@anarazel.de> — 2026-01-15T13:45:40Z

Hi,

On 2026-01-15 06:36:35 -0700, David G. Johnston wrote:
> On Thursday, January 15, 2026, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>
> > The following bug has been logged on the website:
> >
> > Bug reference:      19379
> > Logged by:          Misha Shaygu
> > Email address:      long76.git@mail.ru
> > PostgreSQL version: 17.7
> > Operating system:   Kubuntu 24.04
> > Description:
> >
> > My goal: create role for backup any database on server
> >
> > Steps:
> > 1. CREATE USER backup_user;
> > 2. GRANT pg_read_all_data TO backup_user;
> > 3. pg_dump my_db
> > 4. got error to read large object
> >
> > Following by links
> > https://www.postgresql.org/docs/17/predefined-roles.html
> > https://www.postgresql.org/docs/17/lo-implementation.html
> > "SELECT privileges are required to read a large object" and role
> > "pg_read_all_data" grant it, but it don't work!
> >
> > Please fix it, thanks!
> >
>
> The docs you link note that all data is “tables, views, sequences”.  Large
> objects are not listed.  Maybe that means the name is a bit misleading but
> it’s working as documented.
>
> Likewise, the LO page doesn’t say anything about read all being applicable.

It's not contradicting our docs, but I think it likely still is an
oversight. The goal of pg_read_all_data [1] was to allow running pg_dump
without having to grant granular access, not being able to run pg_dump
successfully due to LOs prevents that.

This doesn't seem like something we're going to fix in a minor version
though...

Greetings,

Andres Freund

[1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6c3ffd697e2242f5497ea4b40fffc8f6f922ff60
> A commonly requested use-case is to have a role who can run an
> unfettered pg_dump without having to explicitly GRANT that user access
> to all tables, schemas, et al, without that role being a superuser.

Re: BUG #19379: Role pg_read_all_data don't allowed read large objects

pierre.forstmann@gmail.com — 2026-01-15T13:47:42Z

It could be interesting to have a new predefined role pg_read_all_lo to 
be able to read all LO.

Maybe it could be done with an extension with some background task to 
take into account newly created LO but it should be easier to have this 
coded without an extension.

Le 15/01/2026 à 14:36, David G. Johnston a écrit :
> On Thursday, January 15, 2026, PG Bug reporting form 
> <noreply@postgresql.org> wrote:
>
>     The following bug has been logged on the website:
>
>     Bug reference:      19379
>     Logged by:          Misha Shaygu
>     Email address: long76.git@mail.ru
>     PostgreSQL version: 17.7
>     Operating system:   Kubuntu 24.04
>     Description:
>
>     My goal: create role for backup any database on server
>
>     Steps:
>     1. CREATE USER backup_user;
>     2. GRANT pg_read_all_data TO backup_user;
>     3. pg_dump my_db
>     4. got error to read large object
>
>     Following by links
>     https://www.postgresql.org/docs/17/predefined-roles.html
>     <https://www.postgresql.org/docs/17/predefined-roles.html>
>     https://www.postgresql.org/docs/17/lo-implementation.html
>     <https://www.postgresql.org/docs/17/lo-implementation.html>
>     "SELECT privileges are required to read a large object" and role
>     "pg_read_all_data" grant it, but it don't work!
>
>     Please fix it, thanks!
>
>
> The docs you link note that all data is “tables, views, sequences”.  
> Large objects are not listed.  Maybe that means the name is a bit 
> misleading but it’s working as documented.
>
> Likewise, the LO page doesn’t say anything about read all being 
> applicable.
>
> David J.

Re[2]: BUG #19379: Role pg_read_all_data don't allowed read large objects

Long76 <long76.git@mail.ru> — 2026-01-15T14:05:07Z

Thanks for answers. And maybe pg_read_all_data without read lo don't allow read all data because need read data from lo. It's makes role pg_read_all_data useless. I hope for a speedy fix. четверг, 15 января 2026г., 16:47 +03:00 от Pierre Forstmann  pierre.forstmann@gmail.com :

>It could be interesting to have a new predefined role
>      pg_read_all_lo to be able to read all LO.
>Maybe it could be done with an extension with some background
>      task to take into account newly created LO but it should be easier
>      to have this coded without an extension.
>Le 15/01/2026 à 14:36, David G.
>      Johnston a écrit :
>>On Thursday, January 15, 2026, PG Bug reporting form < noreply@postgresql.org> wrote:
>>>The
>>>        following bug has been logged on the website:
>>>
>>>Bug reference:      19379
>>>Logged by:          Misha Shaygu
>>>Email address:       long76.git@mail.ru
>>>PostgreSQL version: 17.7
>>>Operating system:   Kubuntu 24.04
>>>Description:        
>>>
>>>My goal: create role for backup any database on server
>>>
>>>Steps:
>>>1. CREATE USER backup_user;
>>>2. GRANT pg_read_all_data TO backup_user;
>>>3. pg_dump my_db
>>>4. got error to read large object
>>>
>>>Following by links
>>>https://www.postgresql.org/docs/17/predefined-roles.html
>>>https://www.postgresql.org/docs/17/lo-implementation.html
>>>"SELECT privileges are required to read a large object" and role
>>>"pg_read_all_data" grant it, but it don't work!
>>>
>>>Please fix it, thanks!
>>
>>The docs you link note that all data is “tables, views,
>>        sequences”.  Large objects are not listed.  Maybe that means the
>>        name is a bit misleading but it’s working as documented.
>>
>>Likewise, the LO page doesn’t say anything about read all
>>        being applicable.
>>
>>David J.
>>

Re: BUG #19379: Role pg_read_all_data don't allowed read large objects

Long76 <long76.git@mail.ru> — 2026-01-15T15:56:56Z

Thanks for answers.
Maybe pg_read_all_data without read lo don't allow read all data because need read lo.It's makes role pg_read_all_data useless.
 
I hope for a speedy fix.
 
>Четверг, 15 января 2026, 16:47 +03:00 от Pierre Forstmann < pierre.forstmann@gmail.com >:
> 
>It could be interesting to have a new predefined role pg_read_all_lo to be able to read all LO.
>Maybe it could be done with an extension with some background task to take into account newly created LO but it should be easier to have this coded without an extension.
>Le 15/01/2026 à 14:36, David G. Johnston a écrit :
>>On Thursday, January 15, 2026, PG Bug reporting form < noreply@postgresql.org > wrote:
>>>The following bug has been logged on the website:
>>>
>>>Bug reference:      19379
>>>Logged by:          Misha Shaygu
>>>Email address:       long76.git@mail.ru
>>>PostgreSQL version: 17.7
>>>Operating system:   Kubuntu 24.04
>>>Description:       
>>>
>>>My goal: create role for backup any database on server
>>>
>>>Steps:
>>>1. CREATE USER backup_user;
>>>2. GRANT pg_read_all_data TO backup_user;
>>>3. pg_dump my_db
>>>4. got error to read large object
>>>
>>>Following by links
>>>https://www.postgresql.org/docs/17/predefined-roles.html
>>>https://www.postgresql.org/docs/17/lo-implementation.html
>>>"SELECT privileges are required to read a large object" and role
>>>"pg_read_all_data" grant it, but it don't work!
>>>
>>>Please fix it, thanks!
>> 
>>The docs you link note that all data is “tables, views, sequences”.  Large objects are not listed.  Maybe that means the name is a bit misleading but it’s working as documented.
>> 
>>Likewise, the LO page doesn’t say anything about read all being applicable.
>> 
>>David J.
>> 
--
Long76

Re: BUG #19379: Role pg_read_all_data don't allowed read large objects

Nitin Motiani <nitinmotiani@google.com> — 2026-02-05T10:05:26Z

On Thu, Jan 15, 2026 at 7:15 PM Andres Freund <andres@anarazel.de> wrote:
>
> >
> > The docs you link note that all data is “tables, views, sequences”.  Large
> > objects are not listed.  Maybe that means the name is a bit misleading but
> > it’s working as documented.
> >
> > Likewise, the LO page doesn’t say anything about read all being applicable.
>
> It's not contradicting our docs, but I think it likely still is an
> oversight. The goal of pg_read_all_data [1] was to allow running pg_dump
> without having to grant granular access, not being able to run pg_dump
> successfully due to LOs prevents that.
>

Hi,

I have proposed a fix for this on pgsql-hackers [1]. And tested that
with the fix, pg_read_all_data can dump large objects. Please take a
look and let me know what you think.

Thanks & Regards,
Nitin Motiani
Google

[1] https://www.postgresql.org/message-id/CAH5HC96dxAEvP78s1-JK_nDABH5c4w2MDfyx4vEWxBEfofGWsw%40mail.gmail.com

BUG #19379: Role pg_read_all_data don't allowed read large objects

David G. Johnston <david.g.johnston@gmail.com> — 2026-05-19T15:35:04Z

On Tuesday, May 19, 2026, Long76 <long76.git@mail.ru> wrote:

> Hello, i waited 17.10 release with fix https://commitfest.postgre
> sql.org/patch/6485/ but seems it wasn’t backported. Why? It’s will be
> fixed only in 19 version?
>

It does not qualify as something that can be back-ported.  It is an
entirely new behavior.  The existing behaviors, while maybe not ideal, are
working as documented.

David J.