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 1q930z-0005Cd-VB for pgsql-general@arkaria.postgresql.org; Tue, 13 Jun 2023 12:20:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q930y-000706-I4 for pgsql-general@arkaria.postgresql.org; Tue, 13 Jun 2023 12:20:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q930x-0006zx-Qq for pgsql-general@lists.postgresql.org; Tue, 13 Jun 2023 12:20:48 +0000 Received: from mail-yb1-xb30.google.com ([2607:f8b0:4864:20::b30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q930u-0027yL-7E for pgsql-general@lists.postgresql.org; Tue, 13 Jun 2023 12:20:46 +0000 Received: by mail-yb1-xb30.google.com with SMTP id 3f1490d57ef6-bcc29cdcdc9so1954245276.0 for ; Tue, 13 Jun 2023 05:20:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; t=1686658842; x=1689250842; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=s/MiAZvvBPtBuWy3I8j12+gLO3eu1pkAED6jEeFYS5Q=; b=mUGDFUcHgW7xFltGbQtboaA0EYFGMrpuXigOiILp10dxnC+Vl+w4vydpePJJn0iG4Q w6KaKlLQ5oIayJSuUR38kvk5GGW6E7xt4Gt+98P7MxC8HH2lw7QEeDPi50LNlPYLHzVj /W3lw6zLG7zyUcuJCn+rrX+SMhd0Nrj7rVp/RlhK7frM0mpEsDphn8TEt8V2/XEwPUcw 2x1yELi99H/hH7JY/hdnz2UimWUmRopYasXazB7fGfAjHL47Itx3tjQUmMcwTVKBYQnW Muyat3/JTdkKI7jFj6A0tZrXNEGwzpDFwjz0o4p73EModSRd2ZRqZdohynt+U5Hcg0hk A60g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686658842; x=1689250842; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=s/MiAZvvBPtBuWy3I8j12+gLO3eu1pkAED6jEeFYS5Q=; b=Rhb2pvsonbnYa0TXYCmG9zbHmbvYWK3F01pkC6ySBo008UMLNzIuA95OrclFdg3853 2/WHOBQSdjZCB5Ym4VDQfbAc+P0GLorcEwwRsu37mKoc21HURY4Jg39ThFblkiRmh93G ynm96u7EjDF5Nm3pEVNHt5gKYltlTrvfmXglWHbGsPxVZGnVXE1KQii+osHnZIe+NY+K 6R7Ecn2EkkBZRjqrKm7bltxLogy/oO6kdouKZtjkQk3baPreSA+TQS7GtIpyk8Tr6G37 4QprerQZENhy82QZcCO9ergpxWijClJRUrIiF146D/8uHiZ9W1Iw/iBbpiahkiCtSWWP Nk4A== X-Gm-Message-State: AC+VfDxXazmqOtr/N08FtdPS2bjgWjvSNe+ojGiRizMLTBmNUowf5lyM 8zR9+rSKsoP0uVt4vyl+0sx8AA== X-Google-Smtp-Source: ACHHUZ7yLGHFrfLjVak1/CYLo0Oiz5bipydnVGureK4mDZNUGYWsQb1tqWwuD09mNgQmQV1VbuYEqQ== X-Received: by 2002:a25:32d2:0:b0:bcc:4021:3350 with SMTP id y201-20020a2532d2000000b00bcc40213350mr1134826yby.44.1686658842584; Tue, 13 Jun 2023 05:20:42 -0700 (PDT) Received: from [192.168.4.41] (162-239-31-113.lightspeed.dybhfl.sbcglobal.net. [162.239.31.113]) by smtp.gmail.com with ESMTPSA id 126-20020a250f84000000b00ba87bc06fe5sm3075167ybp.52.2023.06.13.05.20.42 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 13 Jun 2023 05:20:42 -0700 (PDT) Message-ID: Date: Tue, 13 Jun 2023 08:20:41 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: "paths" between two ROLEs Content-Language: en-US To: Dominique Devienne , pgsql-general@lists.postgresql.org References: From: Joe Conway In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/13/23 04:17, Dominique Devienne wrote: > Hi. We emulated a legacy security model (enforced in C/C++ code) > into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side. > > To troubleshoot and validate that emulation, I'd like to introspect ROLE > membership to: > > 1) Output the ROLE "path(s)" between any two ROLEs. Typically between > the LOGIN USER and the ROLE that control access to a particular SCHEMA. > In our model, there can be several ways the two end-roles are connected, > involving a variable number of roles. So it has to be a recursive query. > > 2) target-end ROLEs (controlling access to SCHEMAs, again) follow a > naming convention, so they can be identified using a LIKE pattern. > Output all target ROLEs (aggregating each "paths" to the source-ROLE in > an text[]) a given LOGIN USER has access to. > > I'd appreciate either example SQL for the above; or hints to achieve the > above. > My CTE "foo" is not great, thus reaching out to the community to avoid > wasting too much time on this on my own. This shows the path between roles taken which provides a particular privilege for a particular object: https://github.com/CrunchyData/crunchy_check_access It might do for you as-is, or at least you can use it as an example. HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com