1

Is it possible to alter schema of a database I am not connected to? More specifically I need to change an owner of a schema (but it doesn't matter for the questions' sake).

As documentation says schemata can be altered using a clause like:

ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

and it sure works, but only on a database I am currently connected in.

Sure I can reconnect to the other database and do it manually, but I am interested whether it is possible to do it from a connection to another (typically postgres) database. It would be quite helpful for automation processes.

I have tried something like:

ALTER DATABASE ALTER SCHEMA name OWNER TO ...
ALTER SCHEMA "db_name".name OWNER TO ...

But without success - so I am interested whether it is possible at all.

I tried to search for this information using one popular search engine and StackOverflow search feature as well. Unsuccessfully - hence the question.

helvete
  • 2,455
  • 13
  • 33
  • 37
  • Looks like this thread may have a workaround for you: https://stackoverflow.com/questions/10335561/use-database-name-command-in-postgresql – Jacob H Jan 31 '18 at 15:02
  • @JacobH : Thanks for pointing me there. I have actually utilized a workaround of `psql $PG_DATABASE -c "ALTER SCHEMA \"public\" OWNER TO $PG_USER";` for now. I am more interested about whether it's possible so I can use it in future:) – helvete Jan 31 '18 at 15:04
  • 1
    Well in SQL Server you would do a `USE [Database]` but since Postgres doesn't have an equivalent I think the workaround may be the only option. Perhaps another user has a different approach. – Jacob H Jan 31 '18 at 15:06
  • No, that's not possible. You can only access objects in the database you are connected to. If you want to do something in a different database, you need to connect to that database. –  Jan 31 '18 at 15:10
  • Thanks for a response @a_horse_with_no_name . What you said would actually make an answer of its own. – helvete Jan 31 '18 at 15:26

1 Answers1

0

As @a_horse_with_no_name and @JacobH pointed out in comments it is not possible to alter schema of a database you are not currently connected to.

So I ended up using a command like this in order to achieve the schema alteration:

psql $PG_DATABASE -c "ALTER SCHEMA \"<schema-name>\" OWNER TO $PG_USER";
helvete
  • 2,455
  • 13
  • 33
  • 37