I have a PostgreSQL database on Azure, and I've created a readonly_user role that allows for external parties to connect to the db in a read-only capacity using a connection string.
This readonly_user has been granted SELECT privileges, and it also has USAGE access on the public schema.
However, the tables in my database have composite primary keys. When I am logged in as the admin user, I can access these primary keys with the following command:
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
tc.table_name = 'player_data' AND
tc.constraint_type = 'PRIMARY KEY';
For some reason, no matter how I try to play with the usage and access roles for readonly_user, this command (and others like it) to access the primary key information for the db tables always returns 0 rows. There should be at least 3 for every table, which I see clearly when I issue this command connected as the admin role.
Is there something about a read-only user that limits its access to the information_schema? How can I access the primary keys of a table as a read-only user?