On 6/12/24 18:56, Tom Lane wrote:
Ron Johnson <ronljohnsonjr@xxxxxxxxx> writes:
On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
david.g.johnston@xxxxxxxxx> wrote:
I think my point is that a paragraph like the following may be a useful
addition:
If one wishes to remove the default privilege granted to public to execute
all newly created procedures it is necessary to revoke that privilege for
every superuser in the system
That seems... excessive.
More to the point, it's wrong. Superusers have every privilege there
is "ex officio"; we don't even bother to look at the catalog entries
when considering a privilege check for a superuser. Revoking their
privileges will accomplish nothing, and it does nothing about the
actual source of the problem (the default grant to PUBLIC) either.
What I'd do if I didn't like this policy is some variant of
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
In a past blog[1] I opined that this cleans up the default security
posture fairly completely:
8<----------------------
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE EXECUTE ON ROUTINES FROM PUBLIC;
-- And/or possibly, more drastic options:
-- REVOKE USAGE ON SCHEMA public FROM PUBLIC;
-- DROP SCHEMA public CASCADE;
REVOKE TEMPORARY ON DATABASE <your_db> FROM PUBLIC;
REVOKE USAGE ON LANGUAGE sql, plpgsql FROM PUBLIC;
8<----------------------
Repeat for each schema that you think might be publicly readable
(which is only public by default).
indeed
BTW, in PG 15 and up, the public schema is not writable by
default, which attacks basically the same problem from a different
direction.
also a good point
[1]
https://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-2
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
