Postgresql ERROR: could not establish connection

Question

New to RDS PostgreSQL and trying to create a new user to take over from a prior user.

1: Because new user is not a Superuser, I have to follow these instructions to reassign prior user objects before dropping, as follows:

CREATE ROLE TempUser WITH PASSWORD 'temppass' LOGIN;

2: Make TempUser part of old and new users

GRANT OldUser TO TempUser;
GRANT NewUser TO TempUser;

3: Reassign OldUser objects and Drop Owned, for each database (loop):

SELECT DBLINK('dbname=some_db user=TempUser password=temppass host=localhost', 'REASSIGN OWNED BY OldUser TO NewUser; DROP OWNED BY OldUser;');

Here at Step 3 is where the error happens.

ERROR: could not establish connection

The error happens when I run above code in a function. But when I perform the steps manually, it works. The function lives in a Db within the current server.

When I run manually and it works, I'm running in a context of the MasterUser (the creator of the server.)

However if I try using the masteruser credentials in dblink above, when running in the function, then I get this error:

permission denied to reassign objects

I also tried granting the following rights to TempUser:

GRANT CONNECT ON DATABASE some_db TO TempUser;     
GRANT ALL PRIVILEGES ON DATABASE some_db TO TempUser;

But did not work.

Any help in this appreciated.

Thanks

Answer 1

There are some very unintuitive permissions requirements when using REASSIGN in the absence of a superuser account (as on RDS on Cloud SQL). I'm surprised it actually worked for you using masteruser, unless masteruser is a superuser somehow?? Basically, in order to reassign as masteruser, I think you would have to GRANT old_user TO masteruser and GRANT new_user TO masteruser (even if masteruser was the one who created both of them - and this might require revoking masteruser from both of them if they happen to be members of masteruser - see related answer, with examples: https://stackoverflow.com/a/62557497/79079)

However, I think you should also be able to do the reassign as new_user if you first GRANT old_user TO new_user - then the only trick is establishing a connection as new_user, which might require GRANT CONNECT ON DATABASE x TO new_user

Not sure if this will be the whole answer, but hopefully it can help guide you in the right direction!