8

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.

Pavanraotk
  • 1,097
  • 4
  • 15
  • 33

2 Answers2

1

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.

Lohit Gupta
  • 1,045
  • 6
  • 11
  • Yeah I know, I just want to add pg_catalog into search path. The problem is I'm getting that error when I try to grant role. Is there any way I can give that access in AWS RDS – Pavanraotk Jul 15 '17 at 08:20
  • Are you trying to create an object inside pg_catalog schema,or you just need to access some object of pg_catalog to create an object somewhere else. – Lohit Gupta Jul 18 '17 at 06:58
  • I need to create types in pg_type and of course change the permission of postgis functions to my database user. – Pavanraotk Jul 18 '17 at 09:47
  • can't you use any other name besides pg_type otherwise rename the existing pg_type and create your own pg_type in its place. – Lohit Gupta Jul 19 '17 at 09:58
  • I don't think we can create custom types outside of pg_type. Also, pg_type exists in pg_catalog, I can't rename since I don't have righta – Pavanraotk Jul 19 '17 at 10:05
  • you can create a custom type in your schema and use it. Although it is not advisable to change default catalog schemas in postgres unless you know what you are doing and its consequences, if you want to create/alter anything in pg_catalog, you will need to run `GRANT ALL ON SCHEMA pg_catalog TO ;` – Lohit Gupta Jul 19 '17 at 10:33
  • I get warning no privileges were granted, since this is Amazon RDS they don't allow us to get that right – Pavanraotk Jul 19 '17 at 10:42
0

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).

Pavanraotk
  • 1,097
  • 4
  • 15
  • 33