postgres 15: removing create privileges on public not working

Question

Unfortunately, all our production and stage databases use the public schema.

I am trying to create a read only user which can only read data, specifically not insert or or delete data, or create or drop tables etc. It seems way harder than it should be.

This is what I have currently:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE ROLE my_read_only_role;
GRANT pg_read_all_data TO my_read_only_role;
GRANT pg_read_all_stats to my_read_only_role;
CREATE ROLE my_read_only_user WITH LOGIN PASSWORD 'xxx';
grant my_read_only_role to my_read_only_user;
set role to my_read_only_user;
SELECT * FROM information_schema.role_table_grants 
 WHERE grantee = 'my_read_only_user';
gives:
my_read_only_user my_read_only_user my_db public test INSERT YES NO
my_read_only_user my_read_only_user my_db public test SELECT YES YES
my_read_only_user my_read_only_user my_db public test UPDATE YES NO
my_read_only_user my_read_only_user my_db public test DELETE YES NO
my_read_only_user my_read_only_user my_db public test TRUNCATE YES NO
my_read_only_user my_read_only_user my_db public test REFERENCES YES NO
my_read_only_user my_read_only_user my_db public test TRIGGER YES NO
SELECT current_user; 
gives: 
my_read_only_user

with role set to my_read_only_user, I can still create tables, drop tables, REVOKE permissions etc.
What is going on? How do I create a user which cant run DDL statements?

I also tried logging in as my_read_only_user via dbeaver. It still lets me create and delete tables (on our public schema).

=== update 1

as my read only user:

\du
 Role name | Attributes
-------------------+------------------------------------------------------------
 myadmin | Create role, Create DB +
 | Password valid until infinity
 my_read_only |
 gs
 my_read_only_role | Cannot login
 my_read_only_user |
 rds_ad | Cannot login
 rds_iam | Cannot login
 rds_password | Cannot login
 rds_replication | Cannot login
 rds_superuser | Cannot login
 rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+
 | Password valid until infinity
 rdsrepladmin | No inheritance, Replication +
 | Password valid until infinity
 rdstopmgr | Password valid until infinity

\l mydb

--------------------------------+------------+----------+-----------------+-------------+-------------+------------+-----------+-------------------
 mydb| myadmin | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
(1 row)

 \dn+
 List of schemas
 Name | Owner | Access privileges | Description
-------------+---------------+--------------------------------+------------------------
 aws_commons | rds_superuser | rds_superuser=UC/rds_superuser+|
 | | =U/rds_superuser |
 aws_s3 | rds_superuser | |
 public | myadmin | mydb=UC/myadmin +| standard public schema
 | | =UC/myadmin +|
 | | read_only_access=U/myadmin |
(3 rows)

\dp mytable
 Access privileges
 Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------------------+-------------------+----------
 public | mytable| table | myadmin=arwdDxt/myadmin+| |
 | | | read_only_access=r/myadmin| |

I think read_only_access was a previous attempt to make a read only access user, not related to my_read_only_role or my_read_only_user

I dont know what all the above means, but I dont see anything which shows why my_read_only_user can create and drop tables.

• Vérace: What's your version of PostgreSQL?
• John Little: postgres 15.5 according to RDS control panel
• Laurenz Albe: Please add the result to the following psql commands to the question: 1) \du, 2) \l name_of_your_database 3) \dn+ 4) \dp name_of_the_table_where_you_can_insert 5) \dt name_of_the_table_where_you_can_insert.
• Vérace: Maybe REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM user1; and then you can grant back the SELECT? I know there's been a change recently (can't remember exactly when - 16 I think) on defaults for these things.
• John Little: Unfortunately, the databases in question are AWS RDS, and are not public. We can only access them via DBeaver using its built in SSH tunnels through a gateway EC2 Server. I dont know how to use psql commands in this case, if they cant be run as SQL statements.

Answer 1

You said you revoked CREATE on schema public from PUBLIC, but your \dn+ output says that the permission is still there. That explains why everybody can create tables in that schema.

You probably got confused by the fact that there are several databases in a PostgreSQL cluster, and a command that you run while you are connected to one of the databases only affects that single database. So you must have revoked the CREATE privilege in a database different from the one where you create tables.

Concerning mytable, look at what you get for SELECT current_user. To read from that table, you must be either a superuser (which you are not on a hosted database), or you must be myadmin or read_only_access or a direct or indirect member of these roles.