Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump Prevents Essential System Table Modifications

Basha <Basha@maxcontact.com> writes:
> With regards to the roles , I have added the below into the view pg_catalog.pg_roles.

> WHERE pg_has_role(CURRENT_USER, pg_authid.oid, 'member'::text) OR (pg_authid.rolname = ANY (ARRAY['postgres'::name);

> This way, it will only show the roles they are member of. This makes sure it doesn't show other db roles.

You might consider the implications of this:

regression=> do $$ begin
for uid in 1..1000000 loop
  if uid::regrole::text != uid::text then
    raise notice 'uid % is %', uid, uid::regrole;
  end if;
end loop;
end $$;
NOTICE:  uid 10 is postgres
NOTICE:  uid 3373 is pg_monitor
NOTICE:  uid 3374 is pg_read_all_settings
NOTICE:  uid 3375 is pg_read_all_stats
NOTICE:  uid 3377 is pg_stat_scan_tables
NOTICE:  uid 4200 is pg_signal_backend
NOTICE:  uid 4544 is pg_checkpoint
...

On my workstation, it takes about half a second to scan the first
million possible user OIDs, so a complete scan of the OID space
would take only half an hour.  In practice you'd seldom need to
scan the whole space.

            regards, tom lane