PostgreSQL: Script to Create a Read-Only Database User

In this post, I provide one way to create, 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 different 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 common 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 have 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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

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

Notify of

Sort by:   newest | oldest | most voted
8 months 8 hours ago

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!

5 months 8 days ago

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