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 1u9mIl-006DEO-SX for pgsql-general@arkaria.postgresql.org; Tue, 29 Apr 2025 14:51:16 +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 1u9mIj-007mvg-1O for pgsql-general@arkaria.postgresql.org; Tue, 29 Apr 2025 14:51:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9mIi-007mvY-MY for pgsql-general@lists.postgresql.org; Tue, 29 Apr 2025 14:51:13 +0000 Received: from mail-oi1-x229.google.com ([2607:f8b0:4864:20::229]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u9mIf-000CRP-2q for pgsql-general@lists.postgresql.org; Tue, 29 Apr 2025 14:51:13 +0000 Received: by mail-oi1-x229.google.com with SMTP id 5614622812f47-3f8ae3ed8f4so4233407b6e.3 for ; Tue, 29 Apr 2025 07:51:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745938268; x=1746543068; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=C2vBufo912/L45tloAb5ktk3okwyISWOPi2WvPcu5Kw=; b=h2sDB0hbdw8+47gr0636HaHf/STbY/MSv2ei2Y7w+744aR1hR2FlGyZPPIf5L3DHEG s3s9zmo/cLgJZeZ4RYd9REVD9wjZyLnT0jKp9x53+Z03AuQNkpRuBxxt0uE8BFawq+dY uTtjNVB0rAoRhqUfs/dxhffmHNHZUOWuyxbidjs/7cpO/vj9/uOAu67Hdp8ka3/4viiI 2EwbyPDsYG+kwTeBtkzxEn+6BFmLLqS2QrBAjH+YD7nRA4mf3/GX44/8KSH0Wcng6ClD 26sCrm71xTKwPOwEDaA+BjEg4vmJvEa7rl0yTCV6AiYoXzEPkeDpFBNLBEIfTcB2QXZf er1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745938268; x=1746543068; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=C2vBufo912/L45tloAb5ktk3okwyISWOPi2WvPcu5Kw=; b=TgsFXen/cJ2YW6E/e6J3DO9x2kyTWdICnkCgKr3buOhAjePoZ8BI3Zk58lpdIwsR3i F2a+ulyRSuYKRzxpl+PJV9G+Lc7H3BcQmfhkK+v/nQIrXCB2rAGSj5bY7Lgform53x3A L30bS84FWQigof6yzpNIOKXZ8PW+Ne0OZJxx8Do0JvgH5D9URHhs1AjLajuDxp9dOudh 8ncJF8X5RFU+8TBTzc2VnitOfUF3vEawP/OhUwnWYMuJjN6U5A74CkE9lU5WJ/vU37xQ VbCW+Uj6/PXzavM+AoGKHjI+jpiJqfrvIwygK8VgqSaFcSRp2d8BMcKGyB8kAqB+9hp/ y8zg== X-Forwarded-Encrypted: i=1; AJvYcCW5QuAwdw2NtB5s86Q1ipkmuxNr6k/t+3ZseM1fq5yReQTnVsPWwPej1T9h1/YJ1+aTcB5hCPe/ciCPdX6U@lists.postgresql.org X-Gm-Message-State: AOJu0Yxc6YIwBN6fi03z7i0Ritge6vkjVIWXBvqMKk0xMvY85K7p7A77 GM8QYzInoNK8MdTS+FgjusDxGmp4Phd/NZYwRUSqT5427348oDSoQhjPZlnbJkhpPQvZISsSuqt XFKjegbdU7jqt3iEKWX1c01dfTAI= X-Gm-Gg: ASbGncsW2+xtyYj88pD7MR+zNLG/l5R4Vmzipl3LeKlurZQazq8r2DlJ8UdVkD6I6Km BQXc8ACOxFhmQWUKNGA2xYNLggw6pF1Yj2FJF4vfN0EYG/GcX7/xN+GDuIvir9FG+1pW7nKgU3o 7D4h9OAGl/MVcjO3iYUTuG4VQv X-Google-Smtp-Source: AGHT+IHUVFcoGQQgYA6bo1gyf/3Zw9GvgdVhdpQ5AhChpY1lvIWurrP0r86r+0H3jrgeqyY66W9QP+fSrD5dH6CroJ0= X-Received: by 2002:a05:6808:3998:b0:3f9:17f9:3099 with SMTP id 5614622812f47-402115ebe5emr1920107b6e.27.1745938267783; Tue, 29 Apr 2025 07:51:07 -0700 (PDT) MIME-Version: 1.0 References: <2705563.1740067550@sss.pgh.pa.us> <2765124.1740070338@sss.pgh.pa.us> <3072990.1740148436@sss.pgh.pa.us> <3073944.1740149062@sss.pgh.pa.us> In-Reply-To: <3073944.1740149062@sss.pgh.pa.us> From: Dominique Devienne Date: Tue, 29 Apr 2025 16:50:40 +0200 X-Gm-Features: ATxdqUGvMAgTnOC9HKXB1VYF67yPw4LjK7HuekfWjA6LiRZLUp-E6OUJt0zOVpw Message-ID: Subject: Re: DROP ROLE as SUPERUSER To: Tom Lane Cc: "David G. Johnston" , "pgsql-general@lists.postgresql.org" Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, Feb 21, 2025 at 3:44=E2=80=AFPM Tom Lane wrote: > Dominique Devienne writes: > > On Fri, Feb 21, 2025 at 3:33=E2=80=AFPM Tom Lane wr= ote: > >> REASSIGN OWNED then DROP OWNED is the recommended path. > > > Hi. Am I missing something? foobar does not OWN anything in this case. > > So I don't see how these recommendations are relevant to this particula= r > > case. --DD Hi. Resurrecting this older thread, as I'm running into an issue with this. > DROP OWNED also removes relevant permission entries (which can be > thought of as things owned by the role, if you hold your head at > the right angle). See its man page. Except when it doesn't... I'm doing the DROP OWNED, and I'm getting several "WARNING: no privileges could be revoked for ..." for SCHEMAs and ROUTINEs. (which BTW break my unit tests, which monitor outputs). So the "removes relevant permission entries" part is kinda broken IMHO. Or at least, not at all as convenient as one might hope it to be. FWIW. --D= D