Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1osF9X-0004th-Ir for pgsql-general@arkaria.postgresql.org; Tue, 08 Nov 2022 03:19:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1osF9W-0001E0-AZ for pgsql-general@arkaria.postgresql.org; Tue, 08 Nov 2022 03:19:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1osF9V-0001Dp-Pl for pgsql-general@lists.postgresql.org; Tue, 08 Nov 2022 03:19:53 +0000 Received: from mail-ej1-x62a.google.com ([2a00:1450:4864:20::62a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1osF9T-0007zq-6Z for pgsql-general@postgresql.org; Tue, 08 Nov 2022 03:19:52 +0000 Received: by mail-ej1-x62a.google.com with SMTP id k2so35261892ejr.2 for ; Mon, 07 Nov 2022 19:19:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=rakreTK5tcMoFP2nTpSRzN2KKyo6FGIVQeJkedmsoLU=; b=E1v5zKkH4EB1UTgRA+etfcA53TrwI5hj6D3x1Pcv9/pjBKIoj+1GoWM9fD/xdmuwcm ILfm4wKh1NVPdrogXYec5nARA56QntnK7+z0TT3tFJnlBDU1Mx1u7BNc0c/t8vxwLY2b PsyzzjKxFzs/c7ZoAhB8UA0LGF3J0vMfhgm4ywKLnL2tkb5fUC6syM9UCmm/38ZZ6gr8 uWkTJ1vdxjghw7CbXJ6dIFbniVjKRulh7lEEKFuwxCoI1aDiurtNgxHo6bEBtCSaVg5e GCSGhahP4/wvkRcxMxDBLxhdnXtYHsw5Ilc08wgpwK46lAeEUOFirg93cucyDjwSlJBb qcHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=rakreTK5tcMoFP2nTpSRzN2KKyo6FGIVQeJkedmsoLU=; b=Rfd5YTpLc28cub2K7NYN2ucWeM2JGtonMrqUM+6rLls7aMjsX6DdGHiIwyULRuQ4YU K04qYmfVA/C6gMNZ3//7xG/WBUZ1MrM6rBFisHfGG7e8we5vsMt+5XWErc8hPEng7N7j Se/3wPNRZZ0YoqMEnKfxYi/vQNnBY9qEUuyGE8q59y7kdiB7775dHESxTH9hfJdck2f8 FiFqFGItDdq0h/TfgwLRSU9scKAPpounlN/k09970SXIWnkvf2iaCfG6vWxV6jjZltog +rjrF1vayuvQDkpWKX5SwMBsyukto/1ZUIZfO3JKzyJN08iINUEjefTTja9kTVejJclf trvw== X-Gm-Message-State: ACrzQf3FaDiPZS0DkEXm//9GqfzZ6aHXYDU0j5fn1kxNNq3IEezz6tDs YE0wakLNSVUcYo3dLwugs+Pqy30HG4jiMA== X-Google-Smtp-Source: AMsMyM6+gbcf8JZpImdYLf/BVvr6WHGI9tkwmh/1I3FdNp0GW9oOIJsSkg+qu5Orqw8wqsb9cyjEXA== X-Received: by 2002:a17:907:e8f:b0:7ad:923a:5a2c with SMTP id ho15-20020a1709070e8f00b007ad923a5a2cmr50110214ejc.736.1667877589509; Mon, 07 Nov 2022 19:19:49 -0800 (PST) Received: from localhost.localdomain (178-190-182-92.adsl.highway.telekom.at. [178.190.182.92]) by smtp.gmail.com with ESMTPSA id ed13-20020a056402294d00b00459e3a3f3ddsm5006171edb.79.2022.11.07.19.19.48 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 07 Nov 2022 19:19:49 -0800 (PST) Message-ID: <5d4b5fcba15b2c871d83e99cae974c01c7bfc1dd.camel@cybertec.at> Subject: Re: PCI:SSF - Safe SQL Query & operators filter From: Laurenz Albe To: Jan Bilek , "pgsql-general@postgresql.org" Date: Tue, 08 Nov 2022 04:19:48 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 2022-11-08 at 01:24 +0000, Jan Bilek wrote: > I am leading PCI:SSF audit on our payment switch platform product and having a issue where our QSA > just came with multiple ways how to escalate privileges and get a shell to the remote host through > our built-in Reporting system which runs on PostgreSQL (12-14). > So these - pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are > inherited from the Superuser and are also super sensitive for us, but our application user apparently > still needs to be superuser for multiple reasons. Would there be any way to go around this? Your application user doesn't need to be a superuser. > 1/ What we need is to create a postgresql user who would be pg_database_owner but not superuser ... > but apparently we still need our user to be superuser to be able to carry on with some operations > (picking one of more then few) .... > CREATE OR REPLACE LANGUAGE plpython3u; > HINT:  Must be superuser to create this extension. Your application user doesn't need to create PL/Python functions. It only has to use them. If your application has to access the operating system, it has to do that in a controlled fashion: 1. through well-written SECURITY DEFINER functions that belong to a highly privileged user 2. through PL/Python functions or other untrusted language functions that are created by a superuser > 2/ Second option is to introduce some sort of limitation of allowed SQL queries and operators > for our Reporting engine, but all that white-listing / blacklisting comes with its own > problems where we are DB agnostic... That shouldn't be necessary. If your application user has restricted privileges as it should, it is automatically limited in the damage it can do. Yours, Laurenz Albe