PostgreSQL: Script to Create a Read-Only Database User

In this post, I provide one way to create, a read-only user with limited access to PostgreSQL Database.
As a Database Administrator, you should create different database user as per requirement.
In real time of application, there are a few various types of Database User is required like Application user, Read-only user, Admin user.

Always create a common database role group and assign permission to the user base on this standard Role.

Here I am sharing one script to create only read only with limited access to PostgreSQL Database. This user can only SELECT table data and also restricted by schema access.

Script to Create Read-Only user:

Assign permission to this read only user:

In the above script, you can find that I restricted user by Database, Schema and SELECT permission to tables.

If you require to give any additional permission like EXECUTE permission for functions, use below script.

Anvesh Patel

Leave a Reply

4 Comments on "PostgreSQL: Script to Create a Read-Only Database User"

Notify of
Sort by:   newest | oldest | most voted

I found Your website very useful and recently I got into this particular article.
What I wanted to share is a little addition which I tried many times and which is about forbidding a user from making any transactions on the DB.
Apart from what You’ve showed above, one could add

alter user set default_transaction_read_only = on;

This little piece of code is specially useful when creating a user account to handle backup.

Once again – great job with this website!


Hi! The information provided is very helpful.

I am having trouble forbidding not a user but a group of users from making transaction on DB. Any idea of how to that using what Michael is proposing?

Thanks in advance