Required Postgres Service Account Permissions

Help me!
,
1

Hi,

I would like to use a service account when connecting to my database. What are the minimum required permissions I must grant my service account?

The following permissions are sufficient to get the data in,

GRANT SELECT, INSERT, UPDATE, REFERENCES ON ALL TABLES IN SCHEMA public

But, If I don’t use an account with broader access, the auto-generated relations aren’t working.

3

Hey @Huseyin_Nurbaki, and welcome to our community :wave:

By default, the SQL Datasource uses automatic schema discovery.

In order to work, the user that logs in the database must be the owner of the database as well. You can find more infos about this here.

In your case, I would suggest to “cache” the introspection result in a file, like in this example

You can also use a different strategy of using 2 different users, like in this example.

Let me know if that helps :pray:

1 Like
4

Hello @jeffladiray,

Thanks for your precious information.

Do you know by any chance what are the exact privileges / rights needed to make the introspection correctly?

In our case we are using a special role “backoffice” (for our remote environments) that is not the owner of the DB.
The introspection runs well locally as we are using the super role “postgres”.
However, in remote environments, we noticed that it cannot define the constraints of some relationship fields.

In local:

{
            "allowNull": false,
            "autoIncrement": false,
            "constraints": [
                {
                    "column": "id",
                    "table": "advisor_companies"
                }
            ],
            "isLiteralDefaultValue": false,
            "name": "advisor_company_id",
            "primaryKey": false,
            "type": {
                "subType": "UUID",
                "type": "scalar"
            }
        },

In remote env:

     {
            "allowNull": false,
            "autoIncrement": false,
            "constraints": [
            ],
            "isLiteralDefaultValue": false,
            "name": "advisor_company_id",
            "primaryKey": false,
            "type": {
                "subType": "UUID",
                "type": "scalar"
            }
        },

I understood that worse case scenario, I can commit the introspection file to my remote environments.
But I’d be safer if I can only grant the privileges needed to my “backoffice” role if possible.

Let me know!
All the best.

Adrien

1 Like
5

Hey @Adrien_Guyon :wave:

I don’t have much more information to share sadly, especially because the postgres documentation is quite obscure regarding this topic - You can find more about this here, regarding the required privileges to access information_schema, but that’s most likely not the answer you were expecting

As mentionned in our documentation - accessing this table requires the user running the query to be the creator of the database & tables, to make sure our introspection process is able to run smoothly. If you are able to generate a valid introspection on development, versioning it will ensure that you can restrict access on more important (Staging, production) environment.

Using the method shared in the documentation would (to me) be the safest in terms of security/access privileges required to run the agent, as only the introspection process needs way-higher privileges to run (ownership vs basic priv.) (So your backoffice role could be limited to exactly what you expect your agent to be able to do).

Finally, and as a side note, we’re still waiting for the Sequelize v7 official stable release, that should hopefully (:crossed_fingers:) allow us to fully remove this requirement - especially because the information_schema access we need for the introspection to run are quite “small” (Mainly to detect foreign keys - which seems to be what you were able to detect as well) compared to the already accessible amount of information in read-only that don’t require special privileges. Sequelize being our main way to ensure full compatibility with our DBS list.

Let me know if that helps :slight_smile:

1 Like
6

Hey @jeffladiray,

Thanks a lot for your detailed answer.

Just to let you know, our decision is to go for the committed introspection file for now.
And we want in the short-term future to connect a DB replicate for the backoffice (we are not using the native CUD commands on Forest Admin), where the “backoffice” role could be the owner of it and the problem would be gone.

Cheers mate!

1 Like