403 Forbidden on all tables already present in an Azure SQL DB

[sgiovinetti]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

Environment
WebApp: Docker container in Azure web app (latest directus version)
Database: Azure SQL Standard consumption plan

All the tables already present in an Azure SQL Database are visible in the Settings -> Data Model session of the app but in the Content session a Forbidden error appears.

No other issues if you create tables from scratch within the Directus webapp.

To Reproduce

Install directus in Azure and attach an Azure DB with tables already present in the db.

Errors Shown

Forbidden in the Content session

What version of Directus are you using?

Latest

What version of Node.js are you using?

Embedded in azure

What database are you using?

Azure SQL

What browser are you using?

Chrome

What operating system are you using?

Linux

How are you deploying Directus?

Azure

[azrikahar]

Hi, would you be able to share the application logs instead? It might have more clues on what's happening compared to the one shown in the App, since normally certain errors aren't display to the end users for security purposes. On top of that, perhaps you can also enable set the LOG_LEVEL into trace so that it may be easier to tell what are the offending queries here. You can refer the docs for more info on this LOG_LEVEL environment variable: https://docs.directus.io/configuration/config-options/#general

[sgiovinetti]

Hi @azrikahar,
thank you for pointing me in the right direction.

Here below the log fragment that generates the error.

################################
2022-02-10T08:08:34.728058966Z 08:08:34 🔍 [89.962ms]
2022-02-10T08:08:34.728064466Z SELECT
2022-02-10T08:08:34.728067966Z OBJECT_NAME (fc.parent_object_id) AS "table",
2022-02-10T08:08:34.728071566Z COL_NAME (fc.parent_object_id, fc.parent_column_id) AS "column",
2022-02-10T08:08:34.728075166Z OBJECT_SCHEMA_NAME (f.referenced_object_id) AS foreign_key_schema,
2022-02-10T08:08:34.728093566Z OBJECT_NAME (f.referenced_object_id) AS foreign_key_table,
2022-02-10T08:08:34.728097066Z COL_NAME (fc.referenced_object_id, fc.referenced_column_id) AS foreign_key_column,
2022-02-10T08:08:34.728100466Z f.name AS constraint_name,
2022-02-10T08:08:34.728103566Z REPLACE(f.update_referential_action_desc, '', ' ') AS on_update,
2022-02-10T08:08:34.728106866Z REPLACE(f.delete_referential_action_desc, '', ' ') AS on_delete
2022-02-10T08:08:34.728110066Z FROM
2022-02-10T08:08:34.728113566Z sys.foreign_keys AS f
2022-02-10T08:08:34.728116766Z INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
2022-02-10T08:08:34.728119966Z WHERE
2022-02-10T08:08:34.728123066Z OBJECT_SCHEMA_NAME (f.parent_object_id) = @p0;
2022-02-10T08:08:34.728126266Z [dbo]
2022-02-10T08:08:34.728129366Z 08:08:34 ✨ request completed GET 304 /relations?limit=-1 284ms
2022-02-10T08:08:35.270533080Z 08:08:35 ✨ request completed GET 304 /admin/assets/index.6648089d.css 412ms
2022-02-10T08:08:35.272986181Z 08:08:35 ✨ request completed GET 304 /admin/assets/index.0e9bbd23.js 422ms
2022-02-10T08:08:35.322395119Z 08:08:35 🔍 [422.658ms] select top (@p0) [ip_access] from [directus_roles] where [id] = @p1 [1, 5BFCA5AA-6D03-4221-AB7C-F251006A2D2B]
2022-02-10T08:08:35.381047264Z 08:08:35 🐛 You don't have permission to access this.
2022-02-10T08:08:35.383445066Z 08:08:35 ✨ request completed GET 403 /items/OdsToStarMap?limit=25&page=1&meta[]=filter_count&meta[]=total_count 531ms
##################################

[azrikahar]

All the tables already present in an Azure SQL Database are visible in the Settings -> Data Model session of the app

In your first screenshot, it seems like there are no fields, which does seem odd as it should at the minimum show the primary key field. Can you confirm whether the table do not have any columns at all, or they are not displayed here?

This might not be related at all, but is there a chance where the database user you are using only has limited permission?

[sgiovinetti]

@azrikahar : I confirm you the tables contains many fields. Simply they are not shown in directus. The tables contain a composed primary key (i.e. 2 columns together forms the primary key) and they are system versioned.

The user is db_admin so for sure it is not an issue of permissions.

[Oreilles]

Directus does not support tables with composed primary keys. When starting your server, you should have seen messages along the lines of: OdsToStarMap does not have a primary key and will be ignored.

This is the reason why you have an error message when trying to access them. This is not a bug in itself - but I believe you should not be seeing them at all in the app in order to avoid such confusion.

[rijkvanzanten]

I'll close this for now, as compound primary keys is not something Directus currently supports. We can keep the discussion around compound keys going on #7721 🙂