New user unable to view CloudSQL Postgres INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

[Udham]

Create some tables with postgres user that has primary and foreign keys. The new user is unable to view  INFORMATION_SCHEMA.TABLE_CONSTRAINTS contents. I tried the following grants: 

GRANT USAGE ON schema schema_name to new_user;

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name to new_user;

// Also tried:

GRANT pg_read_all_data TO new_user; // and tried assigning all other roles as well

// Also we can not directly grant SELECT on any INFORMATION_SCHEMA table as it is restricted.

The other tables in INFORMATION_SCHEMA are listing fine.

[ms4446]

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS table in PostgreSQL contains information about the constraints (like primary keys, foreign keys, unique constraints, etc.) of the tables. Access to this table's information is based on the privileges of the underlying tables. If a user doesn't have the necessary privileges on a table, they won't see the constraints related to that table in INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

To potentially grant the new user the ability to view more entries in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table, you can use the following command:

GRANT pg_read_all_data TO new_user;

This will grant the new user the ability to read all data in the database, including data in system catalogs. However, remember that this role is powerful and should be granted judiciously.

Here's an example of creating tables with primary and foreign keys:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES users(id)
);

After granting the pg_read_all_data role and ensuring the new user has access to the underlying tables, they should be able to see relevant entries in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.

If the new user still faces issues:

• Ensure they are connecting with the correct credentials.
• Verify that the pg_read_all_data role is granted in the correct database.
• Consider checking the roles and privileges of the original user before creating a new one.

Please note that the pg_read_all_data role is a very powerful role, and should only be granted to users who need it. Granting this role to a user will give them the ability to read all data in the database, including sensitive data.