Thread

1. Accessing referential constraint information with minimal permissions

   Avin Kavish <avin@baseboard.ai> — 2023-06-24T12:35:45Z

   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.
   

2. Re: Accessing referential constraint information with minimal permissions

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2023-06-24T12:56:39Z

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