Search code examples
postgresqlprivileges

Default privileges for new users on public schema?

I just created a database with an additional application schema.

And for our Java Spring Boot applications I created a new role with the following SQL scripts for setting up the privileges:

CREATE USER app_role WITH ENCRYPTED PASSWORD '#########';

GRANT ALL ON SCHEMA application TO app_role;

Now my expectation was that I could only create and delete tables within the schema application when logging in with this role.

However, I am also able to create and modify tables in the schema public.

Are there any default privileges for the public schema?

Why can I create tables in schemas I did not grant any privileges to?

Solution

  • The public schema has a special role in PostgreSQL, as the documentation describes.

    If you don't want that (and it can be a security problem), you can either REVOKE the CREATE privilege or even drop the schema alogether.