For development purposes, we need to create a read-only user and read-write user for Postgres databases. We can create a separate role for those two types of user and assigned them in the specific role.
Here is step by step command to do that.
Create a new role with read-only permission:
-- Create read-only role CREATE ROLE ro; -- Grant access to existing tables GRANT USAGE ON SCHEMA public TO ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ro; -- Grant access to future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ro; -- Create a final user with password CREATE USER ro_user WITH PASSWORD '*********'; GRANT ro TO ro_user;
If you need to give access other schemas rather then public just replace the schema name with the public:
GRANT USAGE ON schema otherschema TO ro;
GRANT SELECT ON ALL TABLES IN SCHEMA otherschema TO ro_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA otherschema TO ro_user;