Authorization in psql

#1

Hi All,

I need to run several .sql files using psql. To do that I have prepared
following batch file:

psql -h 10.x.x.x -U postgres -f create_database.sql
psql -h 10.x.x.x -f create_schema.sql dbname postgres
etc.

The problem is, that psql is asking me about dbuser's password for each
row. I would rather to specify password in batch file, but I cannot find
such psql option in the documentation. Any idea how to solve this problem?

Thanks in advance,
Krzysiek

#2

Krzysztof Adamski <akamski (AT) op (DOT) pl> wrote:

Quote:

I need to run several .sql files using psql. To do that I have prepared
following batch file:

psql -h 10.x.x.x -U postgres -f create_database.sql
psql -h 10.x.x.x -f create_schema.sql dbname postgres
etc.

The problem is, that psql is asking me about dbuser's password for each
row. I would rather to specify password in batch file, but I cannot find
such psql option in the documentation. Any idea how to solve this problem?

You can use a password file.

The information is somewhat hidden in the documentation, see
http://www.postgresql.org/docs/8.1/s...pq-pgpass.html

Yours,
Laurenz Albe

#3

In article <ehsoen$lhg$1 (AT) news (DOT) onet.pl>,
Krzysztof Adamski <akamski (AT) op (DOT) pl> wrote:

Quote:

I need to run several .sql files using psql. To do that I have prepared
following batch file:

psql -h 10.x.x.x -U postgres -f create_database.sql
psql -h 10.x.x.x -f create_schema.sql dbname postgres
etc.

The problem is, that psql is asking me about dbuser's password for each
row. I would rather to specify password in batch file, but I cannot find
such psql option in the documentation. Any idea how to solve this problem?

You should also be able to eliminate the need to actually use a password
if you are on a UNIX-like system. Since psql will use the username of
the user who ran the program, you don't have to specify and user if you
run the program as the postgres user. Also, you generally don't have to
enter a password for the database if the database is configured to
authenticate based on the userid of the user who ran the program.

Thus, something like this would work:

$ sudo su postgres -e "/bin/sh"
Switches to postgres user shell
$ ./psql_script.sh

- Aaron Hsu

--
aaron.hsu (AT) sacrificumdeo (DOT) net <http://www.aaronhsu.com>

#4

Aaron Hsu <aaron.hsu (AT) sacrificumdeo (DOT) net> wrote:

Quote:

In article <ehsoen$lhg$1 (AT) news (DOT) onet.pl>,
Krzysztof Adamski <akamski (AT) op (DOT) pl> wrote:

I need to run several .sql files using psql. To do that I have prepared
following batch file:

psql -h 10.x.x.x -U postgres -f create_database.sql
psql -h 10.x.x.x -f create_schema.sql dbname postgres
etc.

The problem is, that psql is asking me about dbuser's password for each
row. I would rather to specify password in batch file, but I cannot find
such psql option in the documentation. Any idea how to solve this problem?

You should also be able to eliminate the need to actually use a password
if you are on a UNIX-like system. Since psql will use the username of
the user who ran the program, you don't have to specify and user if you
run the program as the postgres user. Also, you generally don't have to
enter a password for the database if the database is configured to
authenticate based on the userid of the user who ran the program.

If, and only if, the client is on the same machine as the server.

Else authentication 'trust' would be suicide.

Yours,
Laurenz Albe

#5

In article <1162199154.795987 (AT) proxy (DOT) dienste.wien.at>,
Laurenz Albe <invite (AT) spam (DOT) to.invalid> wrote:

Quote:

Aaron Hsu <aaron.hsu (AT) sacrificumdeo (DOT) net> wrote:
In article <ehsoen$lhg$1 (AT) news (DOT) onet.pl>,
Krzysztof Adamski <akamski (AT) op (DOT) pl> wrote:

I need to run several .sql files using psql. To do that I have prepared
following batch file:

psql -h 10.x.x.x -U postgres -f create_database.sql
psql -h 10.x.x.x -f create_schema.sql dbname postgres
etc.

The problem is, that psql is asking me about dbuser's password for each
row. I would rather to specify password in batch file, but I cannot find
such psql option in the documentation. Any idea how to solve this problem?

You should also be able to eliminate the need to actually use a password
if you are on a UNIX-like system. Since psql will use the username of
the user who ran the program, you don't have to specify and user if you
run the program as the postgres user. Also, you generally don't have to
enter a password for the database if the database is configured to
authenticate based on the userid of the user who ran the program.

If, and only if, the client is on the same machine as the server.

Else authentication 'trust' would be suicide.

True, I was assuming that the natural thing to do for these types of
things would be an ssh login to the machine and running the scripts
locally for convenience. That probably was a bit to general an
assumption. :-)

- Aaron Hsu

--
aaron.hsu (AT) sacrificumdeo (DOT) net <http://www.aaronhsu.com>

#6

Krzysztof Adamski wrote:

Quote:

Hi All,

I need to run several .sql files using psql. To do that I have prepared
following batch file:

psql -h 10.x.x.x -U postgres -f create_database.sql
psql -h 10.x.x.x -f create_schema.sql dbname postgres
etc.

The problem is, that psql is asking me about dbuser's password for each
row. I would rather to specify password in batch file, but I cannot find
such psql option in the documentation. Any idea how to solve this problem?

Thanks in advance,
Krzysiek
From - Sun
you can test:

PGUSER=XXXX PGPASSWORD=XXXXX <command>

your example:
PGUSER=XXXX PGPASSWORD=XXXXX psql -h 10.x.x.x -U postgres -f

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Authorization in psql

comp.databases.postgresql comp.databases.postgresql

Authorization in psql - 10-27-2006 , 05:54 AM

Re: Authorization in psql - 10-27-2006 , 08:49 AM

Re: Authorization in psql - 10-28-2006 , 02:10 PM

Re: Authorization in psql - 10-30-2006 , 03:05 AM

Re: Authorization in psql - 10-30-2006 , 04:17 PM

Re: Authorization in psql - 04-22-2007 , 03:39 AM