node.jspostgresqlpermissionssequelize.js

What should be the minimally required permissions for sequelize user on postgresql?


Creating a simple node.js application I decided to give sequelize a try.
It is required to use sequelize to communicate with db (simple CRUD operations). I also want to track the db changes with sequelize migrations.

What should be the minimally required permissions for sequelize user on postgresql?


Solution

  • Here is the bare minimum grant to be provided for accessing the data thru sequelize. Moreover, you need to turn off the sequelize.sync as it tries to perform "Create if not exists on the table".

    --Create User

    CREATE USER [An User] WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT 100 PASSWORD '[Some secret]';
    

    -- Grant connect to my data base

    GRANT CONNECT ON DATABASE [DB NAME] TO [An User];
    

    -- Grant for Schema usage

    GRANT USAGE ON SCHEMA [Schema Name] TO [An User];
    

    -- Finally, Grant for basic CRUD operation

    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA [Schema Name] TO [An User];
    

    If you want to control through "sequelize.snc",

    GRANT create ON SCHEMA [Schema Name] TO [An User];