Postgresql – getting permission denied on an ownership change

amazon-rdspostgresqlpostgresql-9.4

My current user is pronto

mediapop_staging=> select current_user;
 current_user
--------------
 pronto
(1 row)

This user a standard super user created on AWS RDS.

mediapop_staging=> \du pronto
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 pronto    | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

But I'm getting this:

mediapop_staging=> REASSIGN OWNED BY pronto TO mediapop_staging;
ERROR:  permission denied to reassign objects

Why? How can I resolve it?

Best Answer

There are some unintuitive permissions requirements when using REASSIGN without a superuser. See this other pots where I answered the same question: https://stackoverflow.com/a/62557497/79079

Related Solutions

PostgreSQL Large Objects and Multiple Users

One option is to use SET ROLE command after you open a connection from your application to the databaserole:

SET ROLE databaserole;

In such way any object (including large objects) created within this session will be owned by databaserole instead of token-XXX.

Another option is to use LOCAL and make it work only within the transaction used to create the large object:

BEGIN;
SET LOCAL ROLE databaserole;
-- create and insert the large object
COMMIT;

Alternatively, you could just set the role directly as the user setting:

ALTER ROLE "{{name}}" SET role TO 'databaserole';

I kind of dislike that option as it can become a bit obscure to others how it is working, and if you manage other roles to your user it won't inherit them (although that doesn't seem like a problem to you, as with dynamically created user you should have a single role that it directs inherit from).

Sql-server – Use sp_WhoIsActive in AWS RDS (MSSQL)

I guess I'll answer myself, since it's working...

The required permissions seem to be as follows:

"AlmostAdmin" is a SQL Login in the public server role, GRANTed securables "Connect SQL" and "View Server State".
The login is mapped to "AlmostAdmin" user in MyDB, which is in the db_owner role with default schema dbo.

My problem seems to have been with impersonation, because even though it fails for me when I run the following--

EXECUTE AS USER = 'AlmostAdmin'
EXEC sp_WhoIsActive
REVERT

--It works for the actual user when he's logged in himself (via SSMS). And of course then I logged in as that user and verified that it was working for me too (when using that login, instead of my "TheAdmin" master login!).

Lesson learned: don't rely on impersonation 100% for testing security/permission issues; get the target user involved to test it out as well (or if you're in a position to know the credentials, lower yourself to that level and test it, instead of completely relying on EXECUTE AS).

Best Answer

Related Solutions

PostgreSQL Large Objects and Multiple Users

Sql-server – Use sp_WhoIsActive in AWS RDS (MSSQL)

Related Question