Remove Create Privilege on Public Schema In Postgresql12

With each new database in PostgreSQL, public schema is created which by default is used to store all the objects, if not configured otherwise explicitly. Also, with the introduction of schemas in PostgreSQL, multiple objects can be created with same name under different schemas. While searching for an object when schema name is not provided explicitly, PostgreSQL uses ‘search_path’ setting which in certain PostgreSQL versions is by default set to $user,public. This causes PostgreSQl to search the object under the schema with name equivalent to the username and then under the public schema. If the privileges to public schema is not restricted, any user can create malicious function or other objects that can be accidentally/unknowingly called by other users to perform malicious activities such as privilege escalation. Thus, the create privilege over public schema should be revoked and granted only to the users required by business needs and organization’s security policies.

At the time of writing, from CREATE DATABASE PostgreSQL 12 document, it mentions that:

By default, the new database will be created by cloning the standard system database template1.

In template1, CREATE privilege is still enabled by default for public schema in PostgreSQL 12. We can verify that by looking at the following output and see that USAGE (letter U) and CREATE (letter C) privilege are set in =UC/postgres

$ psql
psql (12.13)
Type "help" for help.

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |

If we create a new database using the default template1 without changing the privilege from the template, we will not able to revoke after the database is created. Let’s create a new database and revoke the privilege.

postgres=# CREATE DATABASE 'nexus' WITH OWNER 'postgres' ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE
postgres=# REVOKE CREATE ON SCHEMA public FROM nexus;
REVOKE

As you can see, we successfully revoked the CREATE privilege on public schema for nexus user. However, the privilege is not actually revoked. We can inspect with as the following output

$ psql --user nexus
Password for user nexus:
psql (12.13)
Type "help" for help.

nexus=> \dn+;
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

nexus=> WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE')  AS "usage",
  "name" = pg_catalog.current_schema() AS "current"
    FROM "names";
  name  | create | usage | current
--------+--------+-------+---------
 public | t      | t     | t
(1 row)

The letter C is still there and create is still true.

Solution Link to heading


postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
template1=# \dn+;
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =U/postgres          |
(1 row)

Then we create a new database from the default template1

postgres=# CREATE DATABASE "nexus" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE "nexus" TO "nexus";

Now we can confirm that the user nexus will not have CREATE privilege on public schema

nexus=> \dn+;
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =U/postgres          |
(1 row)

nexus=> WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE')  AS "usage",
  "name" = pg_catalog.current_schema() AS "current"
    FROM "names";
  name  | create | usage | current
--------+--------+-------+---------
 public | f      | t     | t
(1 row)