Granting a PostgreSQL user read-only access to some tables

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 read-only-core-tables.

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.

But... 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.

Related

• fly Configuring Django SQL Dashboard for Fly PostgreSQL - 2023-08-21
• docker Allowing a container in Docker Desktop for Mac to talk to a PostgreSQL server on the host machine - 2022-03-31
• heroku Using heroku pg:pull to restore a backup to a macOS laptop - 2020-07-10
• heroku Upgrading a Heroku PostgreSQL database with pg:copy - 2020-07-20
• heroku Programatically accessing Heroku PostgreSQL from GitHub Actions - 2020-08-18
• postgresql Show the SQL schema for a PostgreSQL database - 2021-02-23
• datasette Searching all columns of a table in Datasette - 2021-08-23
• github Running a Django and PostgreSQL development environment in GitHub Codespaces - 2023-08-10
• github-actions Running tests against PostgreSQL in a service container - 2021-02-23
• sql Building a Markdown summary of Django group permissions - 2021-06-03

Created 2021-02-26T07:58:19-08:00 · Edit