Set up readonly role

[dragarcia]

This role provides the user read only access to the public schema

• Docker
• Packer

Implementation Notes

• For the public schema under the database postgres, all roles have been stripped of access aside from the superuser postgres.
• A role called public_readonly has been created which has readonly access to the public schema.
• This is only a role, not a user. Future users can be granted this role to inherit the readonly privilege:

CREATE USER sample_user WITH ENCRYPTED PASSWORD 'sample_password';
GRANT public_readonly TO sample_user;

[AndersonMamede]

I cannot find the role public_readonly in Supabase, and apparently it has been renamed to supabase_read_only_user

[martinsotirov]

This doesn't work for me, either with the GRANT public_readonly or supabase_read_only_user. How do I make a separate user from the postgres admin user, who can read all tables but not insert, update, delete. This has to work independent of row level security.

[AndersonMamede]

@martinsotirov I created a new user using role supabase_read_only_user. I use the new user, which I named metrics_grafana, in Grafana and it has read-only permissions and also bypasses row level security. This is how I did (remember to replace metrics_grafana with a new username and PASSWORD-FOR-NEW-USER with a new password):

CREATE USER metrics_grafana WITH ENCRYPTED PASSWORD 'PASSWORD-FOR-NEW-USER';
GRANT supabase_read_only_user TO metrics_grafana;
GRANT pg_read_all_data TO metrics_grafana;
ALTER USER metrics_grafana WITH BYPASSRLS;

[martinsotirov]

@AndersonMamede thanks. I guess my problem was due to missing BYPASSRLS.