Sample Header Ad - 728x90

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