Thread: Setup postgres with automatic table and user intitiallisation

Hi

I programmed an application which uses postgres 9.0. Currently i am writing an installer for my application and wrote
myown GUI to set up postgres settings and i already install it ofer the original setup.exe with parameters. After that
iwant to create user roles and tables with psql.exe and have written 3 scripts: 
-> one for creating user roles (create role statemenst bey superuser)
-> one for creating data bases (create table statements by superuser)
-> one for initializing data bases (insert statements by new role)

I have the problem that when i run my batch i always have to let the user type in the password, thats nasty... I also
triedPGPASSWORD but it does not work, it always prints that it is the wrong password... 

MyBatchfileOfInstallingAndInitializingPostgres.bat [Superusername] [Superuserpassword] [DataBaseOwnerName]
[DatabaseownerPassword]

@echo off

%~d0
cd %~dp0
if not exist "%~dp09.0\bin\psql.exe" postgresql-9.0.4-1-windows_x64.exe --mode  unattended --install_runtimes 0
--create_shortcuts0 --prefix "%~dp09.0" --datadir "%~dp09.0\data" --serverport 5432 --serviceaccount %1 --superpassword
%2--servicepassword %2 

rem set PGPASSWORD=%2
"%~dp09.0\bin\psql.exe" -U %1 -p 5432 -f CreateUser.sql
"%~dp09.0\bin\psql.exe" -U %1 -p 5432 -f CreateDatabase.sql

echo Enter ReDeformUser password to initialize ReDeformDB

rem set PGPASSWORD=%4
"%~dp09.0\bin\psql.exe" -a -d "ReDeformDB" -U %3 -p 5432 -f Inserts.sql

Thanks for any help

On 06/28/2011 05:34 AM, Lodron, Gerald wrote:
> I have the problem that when i run my batch i always have to let the user type in the password, thats nasty... I also
triedPGPASSWORD but it does not work, it always prints that it is the wrong password... 
>

I'm not sure if setting PGPASSWORD in a Windows batch file is going to
work correctly...have you tried creating a .pgpass file instead?
http://www.postgresql.org/docs/9.0/interactive/libpq-pgpass.html

Another option to consider is swapping out the default pg_hba.conf file
with one that trusts local users fully.  You could put that in place,
signal the server to reload its configuration, execute your setup
script, then put back a properly secured file and reload again.  There
will be a brief window where any local user could gain access to the
database server as a superuser that way.  But if you were that worried
about locking down security, you probably wouldn't be deploying an
auto-installer on Windows in the first place.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/