0

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?

1 Answer 1

2

This is by design. The SQL standard defines (ISO/IEC 9075-11:2003, 5.58):

CREATE VIEW TABLE_CONSTRAINTS AS
...
    WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
            ( SELECT TP.TABLE_CATALOG, TP.TABLE_SCHEMA, TP.TABLE_NAME
              FROM DEFINITION_SCHEMA.TABLE_PRIVILEGES AS TP
              WHERE TP.PRIVILEGE_TYPE <> 'SELECT'
                AND
                    ( TP.GRANTEE IN
                      ( 'PUBLIC', CURRENT_USER )
                   OR
                      TP.GRANTEE IN
                      ( SELECT ROLE_NAME
                        FROM ENABLED_ROLES ) )

That is, users who only have the SELECT privilege on an object don't see entries in information_schema.table_constraints.

There are several possible solutions:

  • Give the readonly_user the REFERENCES privilege on all tables. That is harmless, as long as it doesn't have CREATE on any schema and TEMP on the database.

  • Query the system catalog tables pg_class and pg_constraint in pg_catalog.

  • Create a SECURITY DEFINER function with SET search_path = information_schema that runs your query and is owned by a privileged user. readonly_user can then call this function to get the information.

2
  • 1
    Thank you! This is very clear. Interestingly, I managed to get around it in the following way: SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'player_data'; was available to the readonly_user role. Still, I am glad to know the table constraints are not available with SELECT alone. I will try the REFERENCES privilege if I need it. May 2, 2023 at 17:06
  • 1
    Interesting! The standard says that The KEY_COLUMN_USAGE table has one or more rows for each row in the TABLE_CONSTRAINTS table that has a CONSTRAINT_TYPE of “UNIQUE”, “PRIMARY KEY”, or “FOREIGN KEY”. So it could be argued that the definition in PostgreSQL is incorrect. But given that pg_constraint is public, I doubt that anybody cares. May 3, 2023 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.