In PostgreSQL, how can I
GRANT USAGE to a certain schema for a certain user in a certain database while I am in another database?
I am connected to a database (let's say database
first) as a user with
CREATEDB privilege. I want to create another database
second for user
normal_user and grant a usage on public schema to this user. I can do
CREATE DATABASE second; from my current connected database but AFAIK I can't do
GRANT USAGE ON SCHEMA public TO normal_user; since that will only grant usage to the
public schema in my current connected database.
I want to do this to automate database creation, user creation, and permission granting in a single SQL script.
I was hoping for a hacky way to do this. I want to do this to automate the db and user creation as well as permission granting. I am aware that some tools out there lime ansible or terraform might help in doing automation. But I am hoping if we can do this solely from Postgres script alone.
I am using AWS RDS as my Postgres and I don't think I have a way to ssh there to run the Postgres command from cmd line. I am trying to do this from dbeaver script page. So I think SQL is all I have to do this. If I can switch user or switch db easily using SQL, that helps.
After a thought, I think using
psql -u user -p -h hostname I might be able to connect to the instance and switch around database in the shell. But let me know if I am mistaken.