public inbox for [email protected]help / color / mirror / Atom feed
Removing the default grant of EXECUTE on functions/procedures to PUBLIC 7+ messages / 3 participants [nested] [flat]
* Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 14:45 Tefft, Michael J <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Tefft, Michael J @ 2024-07-05 14:45 UTC (permalink / raw) To: [email protected] <[email protected]> I am trying to remove the default grant of EXECUTE on all functions/procedures to PUBLIC. From my reading, there is no straightforward way to do this. For example, ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; Does not apply this across the entire cluster (or database) but only applies to the role who issued it (and objects yet to be created by that role) . So I am arriving at the conclusion that I need to alter the default privileges for every existing role (which I expected), and ensure that default privileges are altered for every new role that is created going forward. Have I analyzed this correctly? Thanks, Mike Tefft ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 14:49 David G. Johnston <[email protected]> parent: Tefft, Michael J <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: David G. Johnston @ 2024-07-05 14:49 UTC (permalink / raw) To: Tefft, Michael J <[email protected]>; +Cc: [email protected] <[email protected]> On Friday, July 5, 2024, Tefft, Michael J <[email protected]> wrote: > I am trying to remove the default grant of EXECUTE on all > functions/procedures to PUBLIC. > > From my reading, there is no straightforward way to do this. For example, > > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > > Does not apply this across the entire cluster (or database) but only > applies to the role who issued it (and objects yet to be created by that > role) . > > So I am arriving at the conclusion that I need to alter the default > privileges for every existing role (which I expected), and ensure that > default privileges are altered for every new role that is created going > forward. > > > > Have I analyzed this correctly? > > > Only those roles that have create privilege on one or more schemas. That should be a reasonably finite and static set. David J. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 14:51 Tom Lane <[email protected]> parent: Tefft, Michael J <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Tom Lane @ 2024-07-05 14:51 UTC (permalink / raw) To: Tefft, Michael J <[email protected]>; +Cc: [email protected] <[email protected]> "Tefft, Michael J" <[email protected]> writes: > I am trying to remove the default grant of EXECUTE on all functions/procedures to PUBLIC. >> From my reading, there is no straightforward way to do this. For example, > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > Does not apply this across the entire cluster (or database) but only applies to the role who issued it (and objects yet to be created by that role) . > So I am arriving at the conclusion that I need to alter the default privileges for every existing role (which I expected), and ensure that default privileges are altered for every new role that is created going forward. > Have I analyzed this correctly? You'll also need to repeat the ALTERs in each database of your installation. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 17:42 Tefft, Michael J <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tefft, Michael J @ 2024-07-05 17:42 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]> I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT PRIVILEGES had been effective. But I see the same content both before and after the ALTEr. You mention that this needs to be done in each database. Is there a database-level version of pg_roles.acl_default that I should be checking instead? Thanks, Mike Tefft From: Tom Lane <[email protected]> Sent: Friday, July 5, 2024 10:51 AM To: Tefft, Michael J <[email protected]> Cc: [email protected] Subject: Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC "Tefft, Michael J" <Michael. J. Tefft@ snapon. com> writes: > I am trying to remove the default grant of EXECUTE on all functions/procedures to PUBLIC. >> From my reading, there is no straightforward way to do this. For example, "Tefft, Michael J" <[email protected]<mailto:[email protected]>> writes: > I am trying to remove the default grant of EXECUTE on all functions/procedures to PUBLIC. >> From my reading, there is no straightforward way to do this. For example, > ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; > Does not apply this across the entire cluster (or database) but only applies to the role who issued it (and objects yet to be created by that role) . > So I am arriving at the conclusion that I need to alter the default privileges for every existing role (which I expected), and ensure that default privileges are altered for every new role that is created going forward. > Have I analyzed this correctly? You'll also need to repeat the ALTERs in each database of your installation. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 18:22 Tom Lane <[email protected]> parent: Tefft, Michael J <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tom Lane @ 2024-07-05 18:22 UTC (permalink / raw) To: Tefft, Michael J <[email protected]>; +Cc: [email protected] <[email protected]> "Tefft, Michael J" <[email protected]> writes: > I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT PRIVILEGES had been effective. But I see the same content both before and after the ALTEr. Er, what? There's no column named acl_default in pg_roles, nor any other standard PG view. psql's "\ddp" command is the most usual way to examine current defaults: regression=# create user joe; CREATE ROLE regression=# ALTER DEFAULT PRIVILEGES FOR USER joe REVOKE EXECUTE ON FUNCTIONS FROM public; ALTER DEFAULT PRIVILEGES regression=# \ddp Default access privileges Owner | Schema | Type | Access privileges -------+--------+----------+------------------- joe | | function | joe=X/joe (1 row) regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 18:42 Tefft, Michael J <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tefft, Michael J @ 2024-07-05 18:42 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]> I apologize, that was sloppy. I was using the acldefault() function with pg_roles, like this: => select rolname, acldefault('f',oid) from pg_roles where rolname like 'mjt%' order by 1; rolname | acldefault -----------+-------------------------------------- mjt_test1 | {=X/mjt_test1,mjt_test1=X/mjt_test1} mjt_test2 | {=X/mjt_test2,mjt_test2=X/mjt_test2} (2 rows) I had issued alter default privileges for role mjt_test1 revoke execute on functions from public; but had not done a similar ALTER for mjt_test2. And so I was surprised that they both showed a default =X/rolename. Examining \ddp and its underlying quuery, I see that view column pg_default_acl gets a new row with defaclacl populated after the ALTER DEFAULT PRIVILEGES. Thanks very much for your guidance, I am on track now. Mike Tefft From: Tom Lane <[email protected]> Sent: Friday, July 5, 2024 2:22 PM To: Tefft, Michael J <[email protected]> Cc: [email protected] Subject: Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC "Tefft, Michael J" <Michael. J. Tefft@ snapon. com> writes: > I was checking pg_roles. acl_default to see if my role-level ALTER DEFAULT PRIVILEGES had been effective. But I see the same content both before and after the ALTEr. Er, what? "Tefft, Michael J" <[email protected]<mailto:[email protected]>> writes: > I was checking pg_roles.acl_default to see if my role-level ALTER DEFAULT PRIVILEGES had been effective. But I see the same content both before and after the ALTEr. Er, what? There's no column named acl_default in pg_roles, nor any other standard PG view. psql's "\ddp" command is the most usual way to examine current defaults: regression=# create user joe; CREATE ROLE regression=# ALTER DEFAULT PRIVILEGES FOR USER joe REVOKE EXECUTE ON FUNCTIONS FROM public; ALTER DEFAULT PRIVILEGES regression=# \ddp Default access privileges Owner | Schema | Type | Access privileges -------+--------+----------+------------------- joe | | function | joe=X/joe (1 row) regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Removing the default grant of EXECUTE on functions/procedures to PUBLIC @ 2024-07-05 20:19 Tom Lane <[email protected]> parent: Tefft, Michael J <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2024-07-05 20:19 UTC (permalink / raw) To: Tefft, Michael J <[email protected]>; +Cc: [email protected] <[email protected]> "Tefft, Michael J" <[email protected]> writes: > I apologize, that was sloppy. > I was using the acldefault() function with pg_roles, like this: > => select rolname, acldefault('f',oid) from pg_roles where rolname like 'mjt%' order by 1; Ah, yeah, that always shows the *built in* default privileges for a given object kind and owner. If there's a relevant entry in pg_default_acl, it overrides the built-in default during object creation. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-07-05 20:19 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-05 14:45 Removing the default grant of EXECUTE on functions/procedures to PUBLIC Tefft, Michael J <[email protected]> 2024-07-05 14:49 ` David G. Johnston <[email protected]> 2024-07-05 14:51 ` Tom Lane <[email protected]> 2024-07-05 17:42 ` Tefft, Michael J <[email protected]> 2024-07-05 18:22 ` Tom Lane <[email protected]> 2024-07-05 18:42 ` Tefft, Michael J <[email protected]> 2024-07-05 20:19 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox