I have an RDS instance with ten databases and aim to create distinct users for each database to monitor my applications better. While I successfully granted read/write privileges to a user, I encountered difficulties in providing ALTER TABLE permissions. I would appreciate any insights. Thus far, I've tried many of the following commands:
CREATE USER new_user WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE your_database TO new_user;
GRANT USAGE ON SCHEMA public TO new_user;
GRANT pg_read_all_data TO new_user;
GRANT pg_write_all_data TO new_user;
However, when attempting to add a column to a table, I encounter the following error:
SQL Error [42501]: ERROR: must be the owner of the table
Given that I'm using master credentials and not a SUPERUSER, is there a specific way to grant ALTER TABLE permissions that I might be missing? Any guidance on resolving this issue would be highly valuable.