In this article we are going to discuss, how to create read only user in PostgreSQL.

Reporting users, or interns, or read only applications need a read only user. A read only user is:

• Able to connect the database
• List objects
• Retrieve data from table

But not able to:

• Delete a row
• Delete a table
• Change table definition
• Delete other roles/users

Read Only User and Role

User and role is same thing in PostgreSQL, user has a login permission in addition to roles.

After PostgreSQL 14

After PostgreSQL 14, developers developed a shortcut. Those are called predefined roles, you can find all predefined roles on this link.

Via one line, only one statement, is enough to grant read only permissions on a database.

GRANT pg_read_all_data TO xxx;

Before PostgreSQL 14

Well, after realizing how easy to grant read only permission after PG 14, this would be painful.

GRANT CONNECT ON DATABASE dbname TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;

Before version 14 as you can see, you need to grant it separately on DB, schema and table level. Therefore running only one statement is not enough.