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?

@Vivek, I'm using PostgreSQL. The first RDBMS I learned is SQL Server. That's why almost everyone of the admins/db owners I create whenever I use other RDBMS than SQL Server are named 'sa'. Hard to kill the habit. — René Vidriales Trujillo, May 04 '18 at 18:35
Check out my answer posted below, if you still facing any issue then let me know. — , May 04 '18 at 18:46
There is no `connect` [SQL statement](https://www.postgresql.org/docs/current/static/sql-commands.html) in Postgres. To switch the connection in `psql` use `\c databasename username` — a_horse_with_no_name, May 04 '18 at 18:49
@a_horse_with_no_name your answer was the one I needed! Super easy haha — René Vidriales Trujillo, May 08 '18 at 15:19

score 36 · Answer 1 · answered Oct 03 '19 at 16:33

36

You can do this via this command in psql:

\c db_name usr_name

answered Oct 03 '19 at 16:33

eng.mrgh

1,143
2
12
28

9

it causes this error: `FATAL: Peer authentication failed for user "user_name" Previous connection kept` ` – Oussama He Feb 05 '20 at 15:31
@OussamaHe You could fix this by modifying the file `pg_hba.conf` located in `/etc/postgresql//main/pg_hba.conf` by changing the part `local all all peer` to `local all all md5`. For more information refer to this [page](https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge). – eng.mrgh Feb 07 '20 at 13:49
3

I wouldn't change the pg_hba the way eng.mrgh proposes it, I'd rather use: psql -h localhost -U -W, for local ipv4 connection. – Mihail Gershkovich Jun 11 '21 at 22:43
Any command to change user session from one user to diff user with password in psql prompt, without modifying pg_hba.conf ? Oracle equivalent: `SQL> conn user/password@db` – Aashutosh Kumar Jun 13 '21 at 19:56

score 16 · Accepted Answer · answered May 04 '18 at 18:45

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.

Any command to change user session from one user to diff user with password in psql prompt? Oracle equivalent: `SQL> conn user/password@db` — Aashutosh Kumar, Jun 13 '21 at 19:55

score 7 · Answer 3 · answered Dec 01 '21 at 09:22

7

You can do this via this command in terminal

psql -d database -U username

answered Dec 01 '21 at 09:22

Mateusz Budzisz

342
4
11

How can I connect to a database as another user?

3 Answers3