How to create read-only user in pgAdmin 4?

Question

Creating a read-only user in pgAdmin 4 is a little tricky. Here is a guide on how I did it.

First of all a few words about the process. The whole process is based on editing a schema (very simple and safe) for your DB, so this creates limitations for using the method for all the DBs you have unless you edit schemas for each DB (again, it is easy).

First, we have to open a main dialogue, select the target DB you need the read-only user for -> Schemas -> right mouse click on "public" schema -> Properties.

In the opened window go to "Default privileges" and click the "+" in the right corner.

1. In the "Grantee" column enter: "pg_read_all_data",
2. in "Privileges" column click on the field and you will see options. Enable only "Select".

On the rest tabs (Sequences, Functions, Types) you can do the same (Select or Usage). Hit "Save".

In the left sidebar scroll down and find "Login/Group Roles". Click right button -> Create -> Login/Group Role. OR if you have an existed user role you want to make read-only, click the right button on it and select "Properties".

In the opened window enter the name of the user, on the "Definition" tab enter a password, on the "Previliges" tab select "Can login" and "Inherit rights from the parent roles?"

In the "Membership" tab hit "+" in the "Member of" table and type "pg_read_all_data" into the "User/Role" column.

In the "Parameters" tab hit "+". Select "role" in the "Name" column's dropdown, type "pg_read_all_data" in the "Value" column. In the "Database" column select the desired DB (where you have edited the schema in the previous steps).

Note, you can add more rows with the same settings for different databases (of course, if those DBs have edited schemas as shown above).

Click "Save".

Now you can log into your PhpPgAdmin (or psql or wherever you need) under this user and do only selects. A real read-only user role.

I hope it will help someone.

Answer 1

I don't have enough reputation to comment on your very helpful post, but wanted to add that the public schema by default gives full access to the PUBLIC role (implicit role that all users belong to). So you would first need to revoke this access.

This can be done in pgAdmin in the Security tab of the schema properties dialog, or with the SQL command

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

See also:

• Issue creating read-only user in PostgreSQL that results in user with greater permission with public schema
• PostgreSQL - Who or what is the "PUBLIC" role?