PostgreSQL: cross Schema Foreign Keys fails with permission denied for table

Question

Adding a cross-schema foreign key:

ALTER TABLE "editedArticles"
  ADD CONSTRAINT "editedArticles_articles_foreign"
  FOREIGN KEY("originalArticleId") REFERENCES "private".articles("id");

fails with:

ERROR:  42501: permission denied for table articles

even after successfully executing:

GRANT USAGE ON SCHEMA private TO postgres;
GRANT SELECT, REFERENCES ON TABLE private."articles" TO postgres;

I seem to have the same problem as asked about in this unanswered question.

For the record:

• SELECT current_user; returns postgres
• \du+ postgres returns Create role, Create DB, Replication, Bypass RLS

and finally:

postgres=> \dn+ private
                       List of schemas
  Name   |  Owner   |     Access privileges     | Description 
---------+----------+---------------------------+-------------
 private | postgres | postgres=UC/postgres     +| 
         |          | digest=UC/postgres       +| 
         |          | supabase_admin=U/postgres | 
(1 row)

postgres=> \z private.articles
                                     Access privileges
 Schema  |   Name   | Type  |       Access privileges       | Column privileges | Policies 
---------+----------+-------+-------------------------------+-------------------+----------
 private | articles | table | digest=arwdDxt/digest        +|                   | 
         |          |       | supabase_admin=arwdDxt/digest+|                   | 
         |          |       | postgres=r/digest             |                   | 
(1 row)

Note that postgres=r/digest only has the r (read) permission on that table, not the x (references). But why? I executed GRANT REFERENCES (as mentioned above).

Answer 1

Turns out that the command:

GRANT SELECT, REFERENCES ON TABLE private."articles" TO postgres;

returned "Success. No rows returned" when executed with my postgres user, but it actually didn't do anything! But if I executed it with the digest user (that owns the private.articles table), then it returned "GRANT" and now it works.