Adding full READ access for a user to a PostreSQL database

Adding full READ access for a user to a PostgreSQL database is useful if you want to allow someone to query your data without modifying it, whereas it’s an interactive user or an application. Full READ access means that the user can select data from any table, view, or function in the database.

To grant full READ access, you need to do two things:

1. Create the user.
2. Assign the appropriate privileges.

Let’s see how to do that step by step.

First, you need to create the user in the database. You can use the CREATE USER command for that. For example, to create a user named superset with the password secret, you can run this command:

CREATE USER superset WITH PASSWORD 'secret';

Next, you need to grant the user the SELECT privilege on all the tables, views, and functions in the database. You can use the GRANT command for that. For example, to grant alice the SELECT privilege on all the tables in the public schema, you can run this command:

grant usage on schema public to superset;
grant select on all tables in schema public to superset;

If you have tables, views, or functions in other schemas, you need to repeat these commands for each schema.

That’s it! You have successfully granted full READ access for a user to a PostgreSQL database.