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 1qD2ot-0004yi-M7 for pgsql-general@arkaria.postgresql.org; Sat, 24 Jun 2023 12:56:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qD2os-0002az-54 for pgsql-general@arkaria.postgresql.org; Sat, 24 Jun 2023 12:56:50 +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 1qD2or-0002aq-PF for pgsql-general@lists.postgresql.org; Sat, 24 Jun 2023 12:56:49 +0000 Received: from smtp.burggraben.net ([88.198.69.140]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qD2ok-000BXp-G3 for pgsql-general@lists.postgresql.org; Sat, 24 Jun 2023 12:56:48 +0000 Received: from elch.exwg.net (elch.exwg.net [IPv6:2001:470:7120:1:127b:44ff:fe4f:148d]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "elch.exwg.net", Issuer "R3" (not verified)) by smtp.burggraben.net (Postfix) with ESMTPS id 7F885C0031A; Sat, 24 Jun 2023 14:56:39 +0200 (CEST) Received: by elch.exwg.net (Postfix, from userid 1000) id 187EE3AB77; Sat, 24 Jun 2023 14:56:39 +0200 (CEST) Date: Sat, 24 Jun 2023 14:56:39 +0200 From: Christoph Moench-Tegeder To: Avin Kavish Cc: pgsql-general@lists.postgresql.org Subject: Re: Accessing referential constraint information with minimal permissions Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.2.10 (2023-03-25) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ## Avin Kavish (avin@baseboard.ai): > I know the information is in `information_schema.referential_constraints`, > but apparently reading that information requires having write permissions > to the tables that have references. I don't know why it's designed like > that. I guess because "the standard says so". But then, information_schema.referential_constraints is only a view and the privilege check is coded into the view, so you could just take the query from the view and omit the privilege check and Bob is your uncle. Another way to approach your problem would be via pg_catalog.pg_constraint https://www.postgresql.org/docs/current/catalog-pg-constraint.html and maybe use pg_get_constraintdef() as documented in this table: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE Another way to learn about these internals is to use psql with argument -E (--echo-hidden, or "\set ECHO_HIDDEN on") and watch psql's queries when displaying objects. Regards, Christoph -- Spare Space