Postgres Azure: Grants for User Removed

Question

I am the admin of a PostgreSQL 11 DB on Azure. Some of the users only have access to specific views. The users were created by:

CREATE USER M1234 WITH PASSWORD '1234!';

GRANT USAGE ON SCHEMA public TO M1234;

GRANT SELECT ON table v_xxx TO M1234;

GRANT SELECT ON table v_yyy TO M1234;

For some reason the grant for select on one or all the existing views is removed every so often and the users of course cannot access. I would really appreciate it if anyone has any insight as to why and how this could happen and if there is a more long term solution.

Answer 1

You grant SELECT on existing tables, but for future tables, you need additional permissions -- Grant access to future tables ALTER DEFAULT PRIVILEGES IN SCHEMA a_given_schema GRANT SELECT ON TABLES TO read access;