Search code examples
sqlpostgresqlprivilegespgadmin-4

PostgreSQL - correct db privileges for an API user account

I have a REST service which will be connecting to a PostgreSQL database. The application will only be executing INSERT/UPDATE/DELETE operations. I want to make sure I give the API account the correct permissions to only execute these type of commands and not DROP and other dangerous operations. What are the best practices for this type of accounts and can you give me some SQL examples or instructions how to do this in pgAdmin 4.

Solution

  • When you want to limit access it is often wise to start with clearing out all permissions public might have. Besides being the name of the default schema public is also a special role that includes all roles so if public is allowed to do something everybody is allowed to do it.

    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
REVOKE ALL ON SCHEMA public FROM public;

    Then set the authorizations for your user(s).

    GRANT USAGE ON SCHEMA public TO api_user;
GRANT SELECT INSERT UPDATE DELETE ON ALL TABLES IN SCHEMA public TO api_user;

    If you have other schema's besides public repeat for those.