CODEWITHSUNDEEP
postgresqlamazon-rdspgadmin-4
Amogh
Amogh

Reputation: 305

Master user on RDS postgres can not grant schema privileges to new user

I have around 5 test postgres databases on a RDS instance. Till date all have been created and run using the Master_user (created at the instance creation).

I am now trying to create 5 new users and Grant ALL privilages for each of the databases respectively. I tried Pgadmin4 Grant wizard, but I get permission denied error for any grant option. Grant ALL doesnt work, and Grant Create, Insert etc also doesn't work. What could be the issue??

Upvotes: 4

Views: 6134

Answers (3)

Vao Tsun
Vao Tsun

Reputation: 51406

let's assume we connected to db as rds_superuser, so then we need do steps:

  1. grant relation owner to rds_superuser (tobe able to "become" it)
  2. become the owner
  3. grant

Eg.: 

rds_superuser@rds_db> grant schema_owner to rds_superuser ;
GRANT ROLE
rds_superuser@rds_db> set role schema_owner;
SET
rds_superuser@rds_db> grant select on all tables in schema schema_name to new_user;
GRANT
rds_superuser@rds_db> reset role;
RESET

Upvotes: 4

Gadelkareem
Gadelkareem

Reputation: 1087

The solution is to use the main database that was created with the instance as a template, so that the main user will have full access to it. I think they are just trying to limit people from creating several DBs on the same RDS..

Upvotes: 1

Amogh
Amogh

Reputation: 305

I just ended up backing up all the databases with --no-owner --no-acl, renaming the databases and creating new ones with new owners. restored each backup with each owner and achieved what I wanted.

Upvotes: 2

Related Questions