For a production server, I wanted to separate the super user, admin and app roles to limit scope of mistakes.
So I've created an account that our app uses to access the DB, and an admin account. The admin account is also used to run migrations to create tables.
I thought I'd ironed out all the issues, the app user can access all existing tables (which were created by the super user), however having now run a migration that creates a table, the app has no access to that table.
The users were granted access with the following commands:
GRANT CREATE, CONNECT ON DATABASE test TO admin; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public TO admin; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin; GRANT CONNECT ON DATABASE test TO api; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO api; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api;
Then the admin user ran
CREATE TABLE new_table; and trying to access that table, the app gets
permission denied for relation new_table
1) What grant am I missing to allow api access to the new table 2) Out of curiousity, if I didn't have a record of the previous grants, how would I examine the grants of the users and discover the privilege that's missing