pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAccessing referential constraint information with minimal permissions
2+ messages / 2 participants
[nested] [flat]
* Accessing referential constraint information with minimal permissions
@ 2023-06-24 12:35 Avin Kavish <avin@baseboard.ai>
0 siblings, 1 reply; 2+ messages in thread
From: Avin Kavish @ 2023-06-24 12:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
I want to programmatically read all the references in a database. Ideally,
I want to do it with read-only permissions to the table. Is it possible?
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 think knowing the relationships between tables is in the same class
of privileges as knowing the columns in the tables. (which you can do by
reading the `columns` view with just SELECT permissions)
Anyway, is there a workaround for this? If not, what is the least
destructive write permission I can give a user who wants this access?
Cheers,
Avin.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Accessing referential constraint information with minimal permissions
@ 2023-06-24 12:56 Christoph Moench-Tegeder <cmt@burggraben.net>
parent: Avin Kavish <avin@baseboard.ai>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Moench-Tegeder @ 2023-06-24 12:56 UTC (permalink / raw)
To: Avin Kavish <avin@baseboard.ai>; +Cc: pgsql-general@lists.postgresql.org
## 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-06-24 12:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-24 12:35 Accessing referential constraint information with minimal permissions Avin Kavish <avin@baseboard.ai>
2023-06-24 12:56 ` Christoph Moench-Tegeder <cmt@burggraben.net>
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