How can I resolve a warning message that says "no privileges could be revoked for "public"" when trying to revoke access on the public schema of an Amazon RDS PostgreSQL DB instance?

Short Description

By default, users can't access objects in schemas that they don't own. To use objects inside a schema, the USAGE privilege for that schema must be granted to the user. Similarly, for creating objects, the CREATE privilege must be granted on that schema. However, all users have CREATE and USAGE privileges on the public schema, so users who can connect to a database have privileges to create objects in the database's public schema. If you don't want all users to be able to create objects in a public schema, then revoke the privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Note: In this command, public is the schema, and PUBLIC means all users—public is an identifier and PUBLIC is a keyword.

When you revoke the CREATE privilege on the public schema for an Amazon RDS PostgreSQL DB instance, you can receive a warning message that says "no privileges could be revoked for "public."" This warning message generally appears in RDS DB instances when the owner of public schema in the database that you are connected to is rdsadmin and not the database owner. The owner of the public schema, rdsadmin, occurs most often when the database is created using template0 instead of the default, which is template1. For more information, see the PostgreSQL documentation for Template Databases.

In Amazon RDS PostgreSQL, the owner of database template0 is rdsadmin, and the owner of template1 is the master user. If you create a database using template0, then the public schema is owned by rdsadmin and not by your master user or database owner. See the following example database that is created using template0:

testpg=> create database tmp0 template template0;
CREATE DATABASE
tmp0=> SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name  |  Owner   
--------+----------
public | rdsadmin
(1 row)

Because the owner is rdsadmin, a warning message is returned when the REVOKE command is executed:

tmp0=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
WARNING:  no privileges could be revoked for "public"
REVOKE

Resolution

1.    Change the owner of the public schema:

ALTER SCHEMA public OWNER TO user1;

Note: In this example command, user1 is the database owner.

2.    Verify that the ownership is changed:

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name  |  Owner   
--------+----------
public | user1
(1 row)

3.    Execute the revoke command from inside the database using the user name that owns the database (user1).

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Related Information

PostgreSQL documentation for The Public Schema

PostgreSQL on Amazon RDS