Обсуждение: how to: psql -U user --password password ?
Hi list, i have a problem with connecting to postgresql on a remote server. basically i want to import some sets of data into thedatabase, using the psql command from within a shell script. i have a local machine where postgres is installed and a remote one where postgres is installed as well. because of security issues the configuration of postgres on the remote server is set in a way that i am not not allowed toconnect to the database from my local machine. and the security thing gets even stricter: i am not even allowed to runa shell script on my remote server. i know already that there is a way to connect to a remote postgres using psql with the options --host 123.123.123.1 --port5432 --username --password unfortunately this only prompts the password screen and i have to input the password for postgres for every single psql commandagain.. this not very convenient in terms of automating imports using shell. i am planning to do some hundreds ofimports (with psql) on a batch and i want to avoid typing the password some hundreds times. i know that in mysql there is way to pass through the password e.g. to mysqldump with mysqldump --user=user --password=password actually i would need similar functionality in postgres. is there a good way to solve my problem? i know that a i could do the import as well using jdbc or odbc connections to postgres (where it is possible to connect asa user with password), but actually i think it is faster (as i have some very large *.sql files to import) and more convenientto do the import directly via psql instead of using jdbc/odbc. i appreciate any help. thanks in advance, stefan _______________________________________________________________________ Jetzt neu! Schuetzen Sie Ihren PC mit McAfee und WEB.DE. 3 Monate kostenlos testen. http://www.pc-sicherheit.web.de/startseite/?mc=022220
Hello Stefan, Recently I Have faced this problem and solved by .pgpass file approch Take alook on http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html With Regards Ashish... ----- Original Message ----- From: "Stefan Zweig" <stefanzweig1881@web.de> To: <pgsql-general@postgresql.org> Sent: Tuesday, July 03, 2007 6:17 PM Subject: [GENERAL] how to: psql -U user --password password ? > Hi list, > > i have a problem with connecting to postgresql on a remote server. > basically i want to import some sets of data into the database, using the > psql command from within a shell script. > i have a local machine where postgres is installed and a remote one where > postgres is installed as well. > because of security issues the configuration of postgres on the remote > server is set in a way that i am not not allowed to connect to the > database from my local machine. and the security thing gets even stricter: > i am not even allowed to run a shell script on my remote server. > > i know already that there is a way to connect to a remote postgres using > psql with the options --host 123.123.123.1 --port > 5432 --username --password > > unfortunately this only prompts the password screen and i have to input > the password for postgres for every single psql command again.. this not > very convenient in terms of automating imports using shell. i am planning > to do some hundreds of imports (with psql) on a batch and i want to avoid > typing the password some hundreds times. > > i know that in mysql there is way to pass through the password e.g. to > mysqldump with mysqldump --user=user --password=password > actually i would need similar functionality in postgres. is there a good > way to solve my problem? > > i know that a i could do the import as well using jdbc or odbc connections > to postgres (where it is possible to connect as a user with password), but > actually i think it is faster (as i have some very large *.sql files to > import) and more convenient to do the import directly via psql instead of > using jdbc/odbc. > > > > i appreciate any help. > > thanks in advance, stefan > _______________________________________________________________________ > Jetzt neu! Schuetzen Sie Ihren PC mit McAfee und WEB.DE. 3 Monate > kostenlos testen. http://www.pc-sicherheit.web.de/startseite/?mc=022220 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
http://www.postgresql.org/docs/8.2/interactive/libpq-pgpass.html or you can set PGPASSWORD environment variable (not recommended though) Stefan Zweig wrote: > Hi list, > > i have a problem with connecting to postgresql on a remote server. basically i want to import some sets of data into thedatabase, using the psql command from within a shell script. > i have a local machine where postgres is installed and a remote one where postgres is installed as well. > because of security issues the configuration of postgres on the remote server is set in a way that i am not not allowedto connect to the database from my local machine. and the security thing gets even stricter: i am not even allowedto run a shell script on my remote server. > > i know already that there is a way to connect to a remote postgres using psql with the options --host 123.123.123.1 --port5432 --username --password > > unfortunately this only prompts the password screen and i have to input the password for postgres for every single psqlcommand again.. this not very convenient in terms of automating imports using shell. i am planning to do some hundredsof imports (with psql) on a batch and i want to avoid typing the password some hundreds times. > > i know that in mysql there is way to pass through the password e.g. to mysqldump with mysqldump --user=user --password=password > actually i would need similar functionality in postgres. is there a good way to solve my problem? > > i know that a i could do the import as well using jdbc or odbc connections to postgres (where it is possible to connectas a user with password), but actually i think it is faster (as i have some very large *.sql files to import) andmore convenient to do the import directly via psql instead of using jdbc/odbc. > > > > i appreciate any help. > > thanks in advance, stefan > _______________________________________________________________________ > Jetzt neu! Schuetzen Sie Ihren PC mit McAfee und WEB.DE. 3 Monate > kostenlos testen. http://www.pc-sicherheit.web.de/startseite/?mc=022220 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On 03/07/2007 13:47, Stefan Zweig wrote: > i know already that there is a way to connect to a remote postgres > using psql with the options --host 123.123.123.1 --port 5432 > --username --password You can specify the username on the command line, but not the password: the --password option only ensures the psql will prompt for the password. The usual thing, as I understand it, is to use a .pgpass file: have a look at the docs, here: http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html > very large *.sql files to import) and more convenient to do the > import directly via psql instead of using jdbc/odbc. Also, if the files you're importing were created by pg_dump, they'll contain various "backslash" commands that are psql-specific, so you *have* to re-import them via psql. HTH, Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
On Tue, Jul 03, 2007 at 02:47:58PM +0200, Stefan Zweig wrote:
>
> i appreciate any help.
man psql:
It is also convenient to have a ~/.pgpass file to avoid
regularly having to type in passwords. See the documentation
for more information.
Peter