Create a read-only role & user in PostgreSQL

Create the role for exampledb.... Note, make sure to connect to the db you want to effect privileges for...

\c exampledb;
CREATE ROLE exampledb_read_only_role;
GRANT USAGE ON SCHEMA public TO exampledb_read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO exampledb_read_only_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO exampledb_read_only_role;

exampledb=# \du
                                           List of roles
        Role name         |                         Attributes                         | Member of
--------------------------+------------------------------------------------------------+-----------
 exampledb_read_only_role | Cannot login                                               | {}
 postgres                 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Create user and assign the new role...

CREATE USER read_only_username WITH PASSWORD 'supersecretpassword';
GRANT exampledb_read_only_role TO read_only_username;

\du
                                                  List of roles
       Role name         |                         Attributes                         |         Member of
--------------------------+------------------------------------------------------------+----------------------------
exampledb_read_only_role | Cannot login                                               | {}
postgres                 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
read_only_username       |                                                            | {exampledb_read_only_role} 

Note: if you are using TablePlus or similar GUI, instead of using \du  you can use the following to describe roles

SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

Do you have any question to us?

Contact us and we'll get back to you as soon as possible.