How can I connect to a database as another user?

Question

Im currently creating an API for a school project and everything is working good. My setup is: Node v10, Postgres, Koa and so on...

I currently have this:

CREATE ROLE sa WITH LOGIN PASSWORD 'some-password.';
CREATE DATABASE master WITH OWNER sa;
\c master;

When the init script runs in the docker machine the output I get is this one:

CREATE ROLE
CREATE DATABASE
You are now connected to database "master" as user "postgres".

So I did change the file to something like this:

CREATE ROLE sa WITH LOGIN PASSWORD 'some-password.';
CREATE DATABASE master WITH OWNER sa;
CONNECT TO master AS main USER sa;

And I get a syntax error:

STATEMENT:  CONNECT TO master AS sa USER sa;
psql:/docker-entrypoint-initdb.d/init.sql:4: ERROR:  syntax error at or near "CONNECT"

I can't find anywhere in docs (or haven't look very good) how to connect from a .sql file to a database with an specific user.

How would I connect to 'master' with its owner, which is 'sa' from a .sql file?

Answer 1

You can do this via this command in psql:

\c db_name usr_name