pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christoph Moench-Tegeder <cmt@burggraben.net>
To: Avin Kavish <avin@baseboard.ai>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Accessing referential constraint information with minimal permissions
Date: Sat, 24 Jun 2023 14:56:39 +0200
Message-ID: <ZJboB4qqIyt9PRPu@elch.exwg.net> (raw)
In-Reply-To: <CAN5h1oTXSiRGLWvdQff5V32YGjWLKzaAkW2mm80_iGMJcUMMbw@mail.gmail.com>
References: <CAN5h1oTXSiRGLWvdQff5V32YGjWLKzaAkW2mm80_iGMJcUMMbw@mail.gmail.com>

## 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





view thread (2+ messages)

Message-ID: <ZJboB4qqIyt9PRPu@elch.exwg.net>
Permalink:  ../ZJboB4qqIyt9PRPu@elch.exwg.net/
Also on:    postgresql.org/message-id/ZJboB4qqIyt9PRPu@elch.exwg.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: cmt@burggraben.net, avin@baseboard.ai
  Subject: Re: Accessing referential constraint information with minimal permissions
  In-Reply-To: <ZJboB4qqIyt9PRPu@elch.exwg.net>
  References: <CAN5h1oTXSiRGLWvdQff5V32YGjWLKzaAkW2mm80_iGMJcUMMbw@mail.gmail.com> <ZJboB4qqIyt9PRPu@elch.exwg.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox