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

Here my assumption is you have a SQL file called "a.sql" and contains the lines of code in the file.

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

Now you are running this script using "psql" command line interface (CLI), so you get the message as below...

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

Now, the thing to be noted that you have connected to database "master" because of "\c master" with user "postgres" because of you passed the credential of "postgres" user. Is I am right here then, If yes, then you have to pass the user and password for different user i.e. "sa" as below.

psql -h localhost -p 5432 -U sa -f a.sql master

Now, it will prompt for the password of "sa" user, finally, your script will run and you will be connected to "sa" user.