I wanted to grant a PostgreSQL user (or role) read-only access to a specific list of tables.
I created the role using the Heroku PostgreSQL web console. Having done that, it had the name
I attached it to my Heroku application using the web console, then I ran the following to get myself a default-permission terminal session:
% heroku pg:psql postgresql-metric-59331 --app myapp
As the default user I ran the following:
GRANT USAGE ON SCHEMA PUBLIC TO "read-only-core-tables";
This grants that user the ability to see what tables exist - after running this the
\dt command for that user started showing a full list of tables.
select * from table returned permission denied for every table. To allow select access I ran this:
GRANT SELECT ON TABLE public.availability_tag, public.county, public.django_content_type, public.django_migrations TO "read-only-core-tables";
That's all it took - my read-only user was then able to run
select * from county and see the results - but attempts to select against tables not in that allow-list were denied.
Created 2021-02-26T07:58:19-08:00 · Edit