-1

I try to apply any of these to create read_only_user:

In all cases, the new read_only_user is able to create a new table in my Database (here: MicroReseau) where I want to prevent that.

Why the read_only_user can create new tables and how to prevent that?

Additional info:

enter image description here

1
  • Instead of posting a bunch of links to who knows where, you should include the exact steps you perform to create such a user (role) and also how you create tables (the entire session, not just the CREATE command).
    – mustaccio
    Commented Nov 2, 2023 at 18:57

2 Answers 2

1

You don't provide any relevant information, but let's guess: you are using a PostgreSQL version older than v15, and you didn't revoke the default unsafe permissions on the public schema.

Like the documentation says:

Note that by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. Some usage patterns call for revoking that privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
2
  • There was so many things I try before asking, it would have been a very too long question. For anybody with casual experience with PostgreSQL would know that i'm pretty sure. I also included the image presenting the hierarchy which give a good hint on the solution. In fact, I found my solution before I red your answer. Peoples expect to be able to understand and answer in a minute or 2. I personnally almost never vote down. How would you make somebody to improve himself if you don't tell him what's wrong? Voting down is really bad and would be required only where the person has bad intention. Commented Nov 2, 2023 at 21:33
  • I agree that leaving a comment when you downvote is a good thing. Note that there is such a comment to your question. Commented Nov 3, 2023 at 4:24
0

I just found it.

The problem was because the Schema where my Database was, had in its security privileges, the "PUBLIC" user granted access. This is how it is defined by default when you create a new Database.

I removed the "PUBLIC" access. In my case, there were already the Owner of the database as granted priviledge "UC". Then links in the question used to create read_only_user were working as expected.

By the way, I also changed the name of the Schema from "PUBLIC" to the name of the DatabaseOwner (that change has no impact other than easier to understand when displayed on any tool like pgAdmin).

Just for reference:

BEFORE

BEFORE

AFTER (I also changed its name from "public" to the name of the databaseOwner.

AFTER

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.