Name: PostgreSQL DB Server. The read-only user is able to create and own tables.
Rating: 2.2 (8184 reviews)
Author: dm8ry

dmitryr@dmitryr-mac my_postgres_and_my_sql % psql -h localhost -p 5434 -U postgres -d postgres
Password for user postgres: 
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

postgres=# 
postgres=# 
postgres=# 
postgres=# create user dima_user with password '1234567';
CREATE ROLE
postgres=# 
postgres=# create database dima_db;
CREATE DATABASE
postgres=# 
postgres=# grant all privileges on database dima_db to dima_user;
GRANT
postgres=# \q


dmitryr@dmitryr-mac my_postgres_and_my_sql % psql -h localhost -p 5434 -U dima_user -d dima_db 
Password for user dima_user: 
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

postgres=# CREATE USER dima_user_ro WITH PASSWORD '7654321';
CREATE ROLE
postgres=# 
postgres=# GRANT CONNECT ON DATABASE dima_db TO dima_user_ro;
GRANT
postgres=# GRANT USAGE ON SCHEMA public TO dima_user_ro;
GRANT
postgres=# 
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
postgres-# GRANT SELECT ON TABLES TO dima_user_ro;
ALTER DEFAULT PRIVILEGES
postgres=# \q


dmitryr@dmitryr-mac my_postgres_and_my_sql % psql -h localhost -p 5434 -U dima_user_ro -d dima_db 
Password for user dima_user_ro: 
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

dima_db=> create table abc(a varchar(100));
CREATE TABLE
dima_db=> \q

Workaround:

dmitryr@dmitryr-mac my_postgres_and_my_sql % psql -h localhost -p 5434 -U postgres -d postgres   
Password for user postgres: 
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

postgres=# \c dima_db
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
You are now connected to database "dima_db" as user "postgres".
dima_db=# REVOKE ALL ON SCHEMA public FROM public;
REVOKE
dima_db=# GRANT USAGE ON SCHEMA public TO public;
GRANT
dima_db=# \q
dmitryr@dmitryr-mac my_postgres_and_my_sql % psql -h localhost -p 5434 -U dima_user_ro -d dima_db
Password for user dima_user_ro: 
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

dima_db=> create table abc(a varchar(100));
ERROR:  permission denied for schema public
LINE 1: create table abc(a varchar(100));
                     ^
dima_db=>