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:
CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
Assign permission to this read only user:
GRANT CONNECT ON DATABASE YourDatabaseName 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;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO 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.
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO Read_Only_User;
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 dbrnd.com, 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.