From: Tom Lane <tgl@sss.pgh.pa.us>
To: Basha <Basha@maxcontact.com>
cc: Christophe Pettus <xof@thebuild.com>, Joe Conway <mail@joeconway.com>,
        PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Subject: Re: [EXT]: Re: BUG #18604: Regression in PostgreSQL 16.4: pg_dump
 Prevents Essential System Table Modifications
In-reply-to: 
 <GV1P194MB2356A3EF7A7014DA620B4C77D89F2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
References: <18604-04d64b68e981ced6@postgresql.org>
 <3B462E2E-88B6-43B9-943F-90E883D33357@thebuild.com>
 <GV1P194MB2356E0520E83B40CE57D4D2FD89E2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
 <183A6734-06B2-48E9-A5C9-235EF87B0DFB@thebuild.com>
 <GV1P194MB235610E122081099FBC480ECD89E2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
 <6860E1BC-DA5A-4576-9FB6-35ED31E6F017@thebuild.com>
 <GV1P194MB2356082D55A66B1F659F1AA5D89F2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
 <73AD2F56-1009-49C7-8752-39C45325A9BB@thebuild.com>
 <GV1P194MB2356E46170B9C91E45E6987FD89F2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
 <01CF2C94-D153-4687-B2EF-EF4B4B1890DF@thebuild.com>
 <2410942.1725675471@sss.pgh.pa.us>
 <8D1E6C08-1C4B-4056-846F-C33C0056249E@thebuild.com>
 <172a934d-6a5d-4c57-9935-43862025b842@joeconway.com>
 <2503009.1725722934@sss.pgh.pa.us>
 <GV1P194MB2356EC281D6CB132537B7DD6D89F2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
 <2514770.1725729411@sss.pgh.pa.us>
 <831E0C7F-4E37-45F9-AA05-04648077FD8D@thebuild.com> <GV1P194MB2356E!
 ! 8A0BCE25A4EB08AF8B7D89F2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
 <2522013.1725733293@sss.pgh.pa.us>
 <GV1P194MB2356A3EF7A7014DA620B4C77D89F2@GV1P194MB2356.EURP194.PROD.OUTLOOK.COM>
Comments: In-reply-to Basha <Basha@maxcontact.com>
	message dated "Sat, 07 Sep 2024 18:57:25 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3209048.1725899977.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 09 Sep 2024 12:39:37 -0400
Message-ID: <3209049.1725899977@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3209049.1725899977%40sss.pgh.pa.us>
Precedence: bulk

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

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

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

You might consider the implications of this:

regression=3D> do $$ begin
for uid in 1..1000000 loop
  if uid::regrole::text !=3D 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