Why Did I Fail to Create an Object on the postgres Database as a Standard User?

Symptom

• Failed to Create a Schema as a Standard User

  Command: CREATE SCHEMA my_schema;

  Error: ERROR: permission denied for database postgres

  

• Failed to Create a Table as a Standard User

  Command: CREATE TABLE my_table(id int PRIMARY KEY,name VARCHAR(30));

  ERROR: permission denied for schema public

  

Solution

Standard users cannot create objects in the postgres database, but you can use the root user to do so.

Switch to user root and ensure that your database kernel allows for root privilege escalation. For details, see Privileges of the Root User.

Privileges of the Root User

RDS for PostgreSQL grants privileges to the root user. To facilitate the use of RDS for PostgreSQL while ensuring no operational risks, privileges of the root user are escalated in specific scenarios.

The following table describes root privilege escalation details in different versions.

Root privilege escalation applies to the following scenarios:

• Creating an event trigger
• Creating a wrapper
• Creating a logical replication publication
• Creating a logical replication subscription
• Querying and maintaining replication sources
• Creating a replication user
• Creating a full-text index template and parser
• Running the vacuum command on a system table
• Running the analyze command on a system table
• Creating an extension
• Granting permissions on an object to a user