CODEWITHSUNDEEP
windowspostgresql
A Question Asker
A Question Asker

Reputation: 3343

Windows PSQL command line: is there a way to allow for passwordless login?

My goal is to be able to fire off a command without having to be prompted for my password. Is there any way to achieve this from the windows command line? In Linux I feel I could send the password to standard in or something, but I am not sure if I could do this for windows.

Thanks!

Upvotes: 27

Views: 73383

Answers (11)

Alexander Smelyansky
Alexander Smelyansky

Reputation: 1

Save windows command file in your private directory with cmd extension: 
Works with Amazon RDS server since postgres 9 up to current version 16  

@echo postgres
@echo off
cmd.exe /c chcp 1252
set PGCLIENTENCODING=UTF8
set PGHOST=your_host_name or ip
set PGDATABASE=postgres
set PGDATABASE=db_name
set PGPORT=5432
set PGUSER=%1
set PGPASSWORD=%2
psql.exe

Or replace %1 and %2 with hardcoded userole/username and password.

Upvotes: 0

Kern Elvish
Kern Elvish

Reputation: 1

under windows in cmd you can type this:

echo localhost:5432:*:postgres:12345>> %APPDATA%\postgresql\pgpass.conf

(before ">>" there are no any spaces!)

format of pgpass.conf is:

hostname:port:database:username:password

Each of the first four fields can be a literal value, or *, which matches anything

Upvotes: 0

Imtiyaz Shaikh
Imtiyaz Shaikh

Reputation: 625

Another alternative for those who do not want .pgpass file and want to execute in a single command line instance.

set "PGPASSWORD=yourpass" && createdb -h localhost -p 5432 -U postgres new_db_name

I referred almost every solution related to command line and finally ended by wrapping " around PGPASSWORD=yourpass while setting in Windows 11.

Upvotes: 2

Diego Ponciano
Diego Ponciano

Reputation: 1463

If you're able to use Powershell, you can set environment variables inline, similar to bash.

This should work:

$Env:PGPASSWORD='your-pass'; psql -U postgres

Note the semicolon between setting the variable and actual command, this is important since those are inline but two separate commands.

Upvotes: 0

Jonathan
Jonathan

Reputation: 4709

I got it working in Postgres 15 with:

psql -U postgres -h 127.0.0.1 -p 54322 -f some_file password=postgres

J

Upvotes: 0

klmbear
klmbear

Reputation: 103

I know it is an old question but for anyone looking like I was, it is hard to find a solution for windows. stick this in a .bat file and it will work (at least for me it did). change director to postres directory, set environment variable PGPASSWORD execute copy command to a csv file and then clear environment variable, then go back to root directory.

cd C:\Program Files\PostgreSQL\9.5\bin\

set PGPASSWORD=yourpassword


psql -d databasename -U yourusername -w -c "\COPY (select * from yourtable) TO 'c:/Users/yourdirectory/yourcsvfilename.csv' DELIMITER '|' CSV HEADER;"

set PGPASSWORD=

cd c:\

Upvotes: 7

Bill_Stewart
Bill_Stewart

Reputation: 24585

I realize this question is a bit old now, but I believe there is a better means for secure, password-free PostgreSQL logon on Windows - SSPI.

  1. Create a local or domain user account and PostgreSQL login with the same name.

  2. In pg_ident.conf, create a mapping:

    # MAPNAME   SYSTEM-USERNAME      PG-USERNAME
SSPI        username@AUTHORITY   loginname

    Replace username with the Windows user name (this is the sAMAccountName attribute for domain accounts), and replace AUTHORITY with the computer name or short domain name. If you're not sure what to use for AUTHORITY, check the PostgreSQL log file. For a local account, this will be the computer name; for a domain account, it's probably the short domain name. Lastly, replace loginname with the PostgreSQL login name (to reduce confusion, I would recommend using the same name for both username and loginname).

  3. In pg_hba.conf, allow the logon; e.g.:

    # TYPE   DATABASE   USER        ADDRESS        METHOD
host     all        loginname   127.0.0.1/32   sspi map=SSPI
host     all        loginname   ::1/128        sspi map=SSPI

  4. If a domain account, set a SPN for it; e.g.:

    setspn -S POSTGRES/serverfqdn username

Now you can log onto Windows using the specified username and run psql.exe, etc. without needing a password.

Upvotes: 3

Yvens Rebouças Serpa
Yvens Rebouças Serpa

Reputation: 198

I found another useful solution that worked for me on this link. It basically sets the PGPASSWORD at the beginning of your command like this:

PGPASSWORD=PASSWORD psql YOUR_CODE

Upvotes: -3

JAGJ jdfoxito
JAGJ jdfoxito

Reputation: 777

Steps:

First ENviroment Var PGPASSWORD

C:\Windows\system32>set PGPASSWORD=clave

before

psql -d basededatos -U usuario

Ready,

Upvotes: 0

leonbloy
leonbloy

Reputation: 76006

Another handy option (specially if your PG server runs in your own client machine, and if this does not poses any security problem for you) is to allow login without password in the server ("trust" authentication mode).

For example, this line in pg_hba.conf (in your DATA dir, a typical location: C:\Program Files\PostgreSQL\9.0\data\ ) grants access without password from your local machine.

host     all     all     127.0.0.1/32    trust

Then, connect with 

  psql.exe -h 127.0.0.1 -U postgres -w [YOUR_DB_NAME]

Upvotes: 14

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37954

There are two ways:

  • Set environment variable PGPASSWORD e.g. set PGPASSWORD=yoursecretpassword
  • Use password file %APPDATA%\postgresql\pgpass.conf as described in documentation

Within password file (my location is C:\Users\Grzesiek\AppData\Roaming\postgresql\pgpass.conf) use specified in doc format. For example to connect database postgres as role postgres on local 5432 server add:

localhost:5432:postgres:postgres:12345

I checked this and it works well (for me), but don't use 127.0.0.1).

Upvotes: 32

Related Questions