Grant usage to a schema from another database

Question

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.

Answer 1

You cannot change an object in one database while you are connected to the other database. You'll have to connect to the database where you want to grant the permission.

You can still do it in a single script if you use psql, because that has the \c command to connect to a different database. So your script could look like that:

CREATE DATABASE second;
\c second
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA newschema;
GRANT USAGE ON SCHEMA newschema TO normal_user;

You should always revoke the CREATE privilege on schema public from PUBLIC for security reasons, and you don't need to grant USAGE on that schema, since that is already granted to PUBLIC.