Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfOA6-0024Wj-SP for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 19:00:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfOA5-007EpK-V4 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 19:00:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfOA5-007EpA-KE for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 19:00:41 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfOA2-003AsV-30 for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 19:00:40 +0000 Received: by mail-oi1-x236.google.com with SMTP id 5614622812f47-3eb7ca55c3bso3402885b6e.3 for ; Tue, 04 Feb 2025 11:00:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738695639; x=1739300439; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=IMX5+4rKBidf8AwMEnjL8aZtO+wuJiDvJbFZ1xJ1pvw=; b=bVsCYbjXy+oa/lltOOEHxVKwR/WzbElA0UOuGf9b1AC4nvF3MrZkQKc5wcJqBIPPi3 q541TWZOxsCC66aNCRPRmDp+dxSBpVldLw33CbskM06tuR3Ox0rbH6y33ip975Cp6iXu Letl/pTvDBGq3saWyaMRynAQ0/xZTWdEwDo+hd3vaDJkTKCGUYpK3V3kFfsSgzoZER7R VijRonT3+0wN37IIyC+yTCjMnqNVNicGGmVPdC8RYeS6h/PXmQ2BXRglPH8rZdYwm3Pa gngK7QMYN3iLQgUhNnKiK5/+L1KyuOIbw9Q6olE62L0Ej6NAFbiNFvEBAcUY/BWzW3T8 qZmg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738695639; x=1739300439; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=IMX5+4rKBidf8AwMEnjL8aZtO+wuJiDvJbFZ1xJ1pvw=; b=YfezIUaYGsK+abHmn31gICugPpvpuTG1MC6Bt1KaXizfMw2cuiRMtTKNrSTFhtqF3O NUj1hk+TbhJhFHZUM0VRibfEnIshCOIY0N9oSa8wNAWzKuB/xDnMmFkhLya2ikSS8I2g 5EMlD0OZnHLEc8cp+sb5oyHH+4Yfo2oXOTWkg4RjPK3CdPT6QyX1yPGcZdnJuwz3/OJ5 amL4dQp/NDWW/zEnEsMxImgKVs0k2d8a6Bbl+CsaCKZKnC5h2a6lBYHPBy+q+7ect72i Ok+NmUlQyanI1759oPeLxPYGdbinp1N3oJtnOPDunL/rmJYmbvG4p//Y76l/JZB5Rcki G7Hw== X-Gm-Message-State: AOJu0YwxlKDhtTwRLC6/Q4L/SPIZWpHkHIkYU2o2EfBMAjl+4LwIPfNL 5PWZ8U+uAgF8ZagcZDfPtgaW8mJiWPQncotVZuBvFMHj+bzs/G/sSAD6KoCvJFH1TdOQLQBzLE2 mbm9tpi/Byj+LUZiwQvFSXuHCcgk= X-Gm-Gg: ASbGnctAGhMR47QVSRot53EWG2tKeqmwoAxOQiQEQ52C067bgtVkYFRpIqaT9ZiFaEq h22sKBC9YXIsQagUqOtRf1p5QZAm3ZVciOi1Oe9GUwxblTrAvUlfwG/HiUlbeDo5RX8Wcjb8= X-Google-Smtp-Source: AGHT+IEHaGnQH+PgC/+tTUbGKg9nkn6N332Eg9pN1SAjiGtdrYMmNeu4yxYcYKp0eGl5DIbnCwi3HakyLM1I5Tvy0k8= X-Received: by 2002:a05:6808:3c45:b0:3f1:b0db:3f99 with SMTP id 5614622812f47-3f3238028efmr19203649b6e.0.1738695638779; Tue, 04 Feb 2025 11:00:38 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6802:a0c:b0:577:9519:f64a with HTTP; Tue, 4 Feb 2025 11:00:38 -0800 (PST) In-Reply-To: References: From: "David G. Johnston" Date: Tue, 4 Feb 2025 12:00:38 -0700 X-Gm-Features: AWEUYZmEB2n3wq3GOIeFCHS7m3L-aaB0_aJkBbAwengGr64Fw9n1k3RrvWmYlME Message-ID: Subject: Re: Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL To: Ayush Vatsa Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000ec130a062d559f3c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ec130a062d559f3c Content-Type: text/plain; charset="UTF-8" On Tuesday, February 4, 2025, Ayush Vatsa wrote: > > postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE EXECUTE ON > FUNCTIONS FROM PUBLIC; > ALTER DEFAULT PRIVILEGES > As the documentation explains: Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type. This means you cannot revoke privileges per-schema if they are granted globally (either by default, or according to a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema). Per-schema REVOKE is only useful to reverse the effects of a previous per-schema GRANT. David J. --000000000000ec130a062d559f3c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 4, 2025, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:

postgres=3D# ALTER DEFAULT PRIVILEGES IN SCHE= MA my_schema REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIV= ILEGES


As the documentation expla= ins:

Default privileges t= hat are specified per-schema are added to whatever the global default privi= leges are for the particular object type. This means you cannot revoke priv= ileges per-schema if they are granted globally (either by default, or accor= ding to a previous=C2=A0ALTER DEFAULT PRIVI= LEGES=C2=A0= command that did not specify a schema). Per-schema=C2=A0REVOKE=C2=A0is only useful to reverse the effects of a previous per-schem= a=C2=A0GRANT= .


David J.

--000000000000ec130a062d559f3c--