Amazon RDS Postgres -> Give permission to pg_catalog

Question

I need to grant permission to the master user(MasterUsername) the access of of pg_catalog.

GRANT USAGE ON SCHEMA pg_catalog TO <master-user>;

On running this, I get the below warning: WARNING: no privileges were granted for "pg_catalog".

Essentially, I have an automation script, where I create the database, I set search path and :

SET search_path = <my-schema>, pg_catalog;
CREATE TYPE <type> AS (
id bigint,
name character varying);

I get below error

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for schema pg_catalog

Essentially, it's not allowing me to create type, since the pg_type table exists in pg_catalog. How to create type?

I don't know if granting the USAGE rights will help? If there's another way to work-around this, please do let me know.

Answer 1

The way I worked around this is very simple, I created a role postgres using CREATE ROLE postgres, I assigned it to my user.

Added to search path public and the database by SET search_path=public,<database>

Assigning this to my user actually gave me all the rights I needed to create custom pg_type, use the schema pg_catalog in public schema. How this worked? I don't know, may be postgres guys can answer this? Or the AWS RDS guys can answer this may be.

Secondly, I faced another issue in using the pg_functions and the functions of extension Postgis, even they were resolved by adding the role postgres role to the user who needed to access the function.

I still think this is a workaround and not a direct fix, or may be a fix but there should be some documentation around this(which I did not find).

Answer 2

granting usage rights will only let you be able to access objects of a schema, creating new objects will require create privileges on that schema.