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 1uMnHW-003u6i-2p for pgsql-admin@arkaria.postgresql.org; Wed, 04 Jun 2025 12:31:46 +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 1uMnHS-00Bt2H-TW for pgsql-admin@arkaria.postgresql.org; Wed, 04 Jun 2025 12:31:43 +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 1uMnHS-00Bt26-GZ for pgsql-admin@lists.postgresql.org; Wed, 04 Jun 2025 12:31:43 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uMnGl-000AqU-0Q for pgsql-admin@postgresql.org; Wed, 04 Jun 2025 12:31:42 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-72c27166ab3so4655156a34.1 for ; Wed, 04 Jun 2025 05:30:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1749040256; x=1749645056; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=bahkoZ9aJlLBjZ60nftV4NhLPhaDpJlaPc6FVWrYZxU=; b=MAdsosri6O/ST1DDLkH+9GYeg5tAY2PzZF7//1ofL9bwklUmItvdpHL9Qv4j2NBTCr qks3gxzh0h3Lgvwjo3FjlAWkwOb1Ld10KCpY7J1mGy5MALrN/S2A1hblOKEHvTGCIOuc xr/OAitrO90QpYoVwHmJzeD0yQaOtnbQYcSkREb/LP+py/qKTPZ6s52xxa16cZhdrJ7j 7NCNEDxjFfDfBU7VdDr9P3wv5fRf9CRWioV2ATegT893c5uJUw4CEh91kgjuzuyLmxOI CNHvpJAA7/ZGp41tVOUModzN19qDg1QVBH7BpF7YEeJpSNdHmsAxF8lIMnSTnDsVHspm +yYA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1749040256; x=1749645056; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=bahkoZ9aJlLBjZ60nftV4NhLPhaDpJlaPc6FVWrYZxU=; b=vAUL32oaHF0vVhbkiNu4u3mqh4TqqLdzj/uooksC8EembssZ2sRPbpudzN4IuU0Shq HE0xAaSw18A/obL4qFrIm59MMgaV+PIdfeERYxLdoyURbwiA4ZYBtkfWy7en/72Bo+XA bedZxSMOaquAAaQoqoNgjY610KuIU8z+c7rAbH4882RwR5LPWZsidHHiQ3S4BNMZvpRd +5z7piXVsiGzcM4ZkcmaTX9quW6BDovgfAunfzyQA0QnF7l21QsS8VqbR/HZwwr634Xs ra2b8wu2G+JUYqJVsiHGaXIWE7LBEhJD1gzUlt5nO5ppySwd0rcTuMfvmJonrgjs5A0U B5bQ== X-Gm-Message-State: AOJu0YxEiYQLb3p14HYlxLk2u6AB5oE7rDrfK5CKc8YoPsf/tchTPw9p I9j3nX0hD2Hje6NYYABm373sxIH7nnNkk4fz9mMeOZcma2k1zB0ewdQPZkIkpixcuUSb/5VAPjz HDy/JYqWn453ryHGcaFplXHusXZrcAnS+qxvU X-Gm-Gg: ASbGnctGi+YrM73tU0B0vZN3g5cQKFyt3lPaMhw2uAT4zUSzGxi5FuQyqcwz+z1YP0q rTZDAmL1+VCdsJIWxmb8LtyeO5BQUARVOGNPaXx5y1ftsIhiKdPjvPLNPCciHeX0nVIZugoFZhf 1X2eOJcgdRuzkbsJr7xSAbALKLOPw1sxqo7Es= X-Google-Smtp-Source: AGHT+IEKp0yzOzoDro85FKU+vlcGhnMeV36G3zL8Ur27sqSsSzJp7zwb1K2/XttmukLHaZQNfT7a1Ne+eYPWZ7scaSE= X-Received: by 2002:a05:6830:498b:b0:72b:a5e0:f76 with SMTP id 46e09a7af769-73869c18e49mr2058267a34.4.1749040256148; Wed, 04 Jun 2025 05:30:56 -0700 (PDT) MIME-Version: 1.0 From: "Radoulov, Dimitre" Date: Wed, 4 Jun 2025 14:30:45 +0200 X-Gm-Features: AX0GCFte4nOFPeYAsOiG4p_EbaDPBCnbAQ87Eb9IrncRaJME2I6RM5_4pLsPjYM Message-ID: Subject: vacuumlo Permission Discrepancy Between Prod and Dev on AWS Aurora PostgreSQL To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="0000000000002a6f840636be2b13" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002a6f840636be2b13 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable We have several AWS Aurora PostgreSQL 11.21 clusters. Our goal is to remove large objects (LOBs) stored in the pg_largeobject table. The application code has already been refactored to stop using LOBs. To test the cleanup process, we cloned the production database and ran vacuumlo. Since the LOBs are associated in a custom way, all of them were treated as orphans and successfully removed =E2=80=94 which is exactly what= we wanted. Here=E2=80=99s where it gets tricky: in the development environment, runnin= g the same vacuumlo command fails with the following error: Removing lo 19481964 vacuumlo: error: failed to remove lo 19481964: ERROR: must be owner of large object 19481964 Removal from database "hecosdb" failed at object 0 of 1000. In both the production and development environments, the LOBs are owned by the same three application users. The user running vacuumlo is not the owner of the LOBs in either environment. Also, there are no superuser privileges available on AWS RDS. Despite this, vacuumlo works fine in production using the RDS master user, but fails in development with the same user due to permission issues. As a workaround, we're currently running an ALTER LARGE OBJECT ... OWNER TO username loop to reassign ownership. However, this process is extremely slow, as we can only execute the changes one by one due to resource constraints. Why does vacuumlo succeed in production but fail in development, even though the setup appears similar? Thank you in advance. Best regards Dimitre --0000000000002a6f840636be2b13 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

We have several AWS Aurora Postg= reSQL 11.21 clusters. Our goal is to remove large objects (LOBs) stored in = the=C2=A0pg_largeobject=C2=A0table. The app= lication code has already been refactored to stop using LOBs.

To test the cleanup process, we cloned the production dat= abase and ran=C2=A0vacuumlo. Since the LOBs= are associated in a custom way, all of them were treated as orphans and su= ccessfully removed =E2=80=94 which is exactly what we wanted.

Here=E2=80=99s where it gets tricky: in the development e= nvironment, running the same=C2=A0vacuumlo= =C2=A0command fails with the following error:

Removing lo 19481964
vacuumlo: error: failed to remove lo 19481964: ERROR: must be owner of larg=
e object 19481964
Removal from database "hecosdb" failed at object 0 of 1000.

In both the production and developme= nt environments, the LOBs are owned by the same three application users. Th= e user running=C2=A0vacuumlo=C2=A0is not th= e owner of the LOBs in either environment. Also, there are no superuser pri= vileges available on AWS RDS.

Despite this,=C2= =A0vacuumlo=C2=A0works fine in production u= sing the RDS master user, but fails in development with the same user due t= o permission issues.

As a workaround, we'r= e currently running an=C2=A0ALTER LARGE OBJECT ...= OWNER TO username=C2=A0loop to reassign ownership. However, this pr= ocess is extremely slow, as we can only execute the changes one by one due = to resource constraints.

Why does=C2= =A0vacuum= lo=C2=A0succeed in= production but fail in development, even though the setup appears similar?=


Thank you in advance.

Best regards
Dimitre
--0000000000002a6f840636be2b13--