Why can't my read-only user role see any of my tables' primary key information in psql?
1
vote
1
answer
783
views
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:
SELECT
tc.table_schema,
tc.table_name,
kcu.column_name
FROM
information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
WHERE
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?
Asked by justadampaul
(113 rep)
Apr 30, 2023, 06:38 PM
Last activity: May 1, 2023, 03:19 AM
Last activity: May 1, 2023, 03:19 AM