Thread: Authentication in batch processing
I have postgres set up to require usernames and passwords for access.
The following script works just fine in 7.1.2 without manual
intervention. Using 7.2.1 psql issues a password prompt and
blocks until a password is manually entered.
#######################################
#!/bin/sh
psql mydb <<\__EOD__
mypassword
create table mytable
(
ssn char(9),
name char(50)
);
__EOD__
#######################################
For several years now I have been using scripts like the above
to drop and create 14 databases, and create and populate ~100
tables on a nightly basis. I need this or something like it
to work.
As always I assume I am doing dumb.
Can someone help?
hal
Password prompting was changed in 7.2.X. You can now pass a script into psql, and you will be prompted for the password on your terminal rather than having the password coming from the script. The best way send the password in 7.2.X is to use 'expect', or use the PGPASSWORD environment variable. (However, on some OS's, environment values like PGPASSWORD can be seen by 'ps'.) Another option is that if /dev/tty can't get opened, the password will be requested from stdin. Unfortunately, I can't think of an easy way to make /dev/tty fail. --------------------------------------------------------------------------- Hal Lynch wrote: > I have postgres set up to require usernames and passwords for access. > > The following script works just fine in 7.1.2 without manual > intervention. Using 7.2.1 psql issues a password prompt and > blocks until a password is manually entered. > > ####################################### > #!/bin/sh > > psql mydb <<\__EOD__ > mypassword > create table mytable > ( > ssn char(9), > name char(50) > ); > __EOD__ > ####################################### > > For several years now I have been using scripts like the above > to drop and create 14 databases, and create and populate ~100 > tables on a nightly basis. I need this or something like it > to work. > > As always I assume I am doing dumb. > > Can someone help? > > hal > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
pgman@candle.pha.pa.us (Bruce Momjian) wrote in message news:<200206022022.g52KM8m18308@candle.pha.pa.us>... > Password prompting was changed in 7.2.X. You can now pass a script into > psql, and you will be prompted for the password on your terminal rather > than having the password coming from the script. > > The best way send the password in 7.2.X is to use 'expect', or use the > PGPASSWORD environment variable. (However, on some OS's, environment > values like PGPASSWORD can be seen by 'ps'.) Another option is that if > /dev/tty can't get opened, the password will be requested from stdin. > Unfortunately, I can't think of an easy way to make /dev/tty fail. I see it as a very radical change in password processing. I know you must have your reasons to have it this way, but there are people like Hal Lynch (or like me), to whom this change introduces many new problems (and does not solve any of security ones). It would be nice if there was an option (in psql) to use stdin instead of /dev/tty to read password(s) (like before 7.2.X). Is this too much to ask? Thanks, Alfred
Alfred Anzlovar wrote: > pgman@candle.pha.pa.us (Bruce Momjian) wrote in message news:<200206022022.g52KM8m18308@candle.pha.pa.us>... > > Password prompting was changed in 7.2.X. You can now pass a script into > > psql, and you will be prompted for the password on your terminal rather > > than having the password coming from the script. > > > > The best way send the password in 7.2.X is to use 'expect', or use the > > PGPASSWORD environment variable. (However, on some OS's, environment > > values like PGPASSWORD can be seen by 'ps'.) Another option is that if > > /dev/tty can't get opened, the password will be requested from stdin. > > Unfortunately, I can't think of an easy way to make /dev/tty fail. > > I see it as a very radical change in password processing. > > I know you must have your reasons to have it this way, but there are > people like Hal Lynch (or like me), to whom this change introduces > many new problems (and does not solve any of security ones). Yes, we had complaints that people were running their script and they wouldn't be prompted for the password on their terminal. Researching, we found no applications that gets passwords from stdin _if_ a controlling terminal (/dev/tty) can be opened. > It would be nice if there was an option (in psql) to use stdin instead of > /dev/tty to read password(s) (like before 7.2.X). > > Is this too much to ask? It will read the password from stdin if there is no controlling terminal. Does that help? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Alfred Anzlovar wrote: > Maybe this could/can be done differently, but as it was, it was so easy > and nice. And if there are more of us, poor souls, with comparable > problems maybe an option called '--pwd-from-stdin-only' would be a > solution. Another option (thanks God for Open Source projects) is adding > an option for prompting into psql by myself. But I see this is a rather > drastic measure to get what I need/want. How about PGUSER/PGPASSWORD? That will work. This is assuming you don't have an OS (BSD?) that displays environment variables for a process. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Alfred Anzlovar wrote: > On Thu, 4 Jul 2002, Bruce Momjian wrote: > > > How about PGUSER/PGPASSWORD? That will work. This is assuming you > > don't have an OS (BSD?) that displays environment variables for a > > process. > > No BSD, Linux. And it works. Thanks. > > Well, I must admit we had some rain today, but after your answer sun came > from behind the clouds :) > > More thanks and greetings from the Sunny side of the Alps. > Alfred Anzlovar 7.3 may remove PGPASSWORD, I think, and instead allow you to specify a file that contains the password. > PS > Is it ok to send e-mail to pgsql-admin@postgresql.org AND TO you? Yes, of course. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
At 12:16 PM 7/4/02 -0400, Bruce Momjian wrote:
> > Well, I must admit we had some rain today, but after your answer sun came
> > from behind the clouds :)
Rain? I've heard about that. Something about moisture falling from the
sky. Haven't seen any here in Phoenix for several months.
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228
On Tue, 2 Jul 2002, Bruce Momjian wrote: > Yes, we had complaints that people were running their script and they > wouldn't be prompted for the password on their terminal. Researching, > we found no applications that gets passwords from stdin _if_ a > controlling terminal (/dev/tty) can be opened. Yes, this would (usually) be the case with scripts that are used by end-users or are run one at a time. But I have two different sets of shell scripts, that are always run interactively (they have a readble/writable /dev/tty) and execute psql several times. First set has sometimes results processed and user (operator) has to provide answers. Since these scripts are 'privileged' in some way, I do not want the passwords to be known to operators, so I keep them in a safe place and provide them to psql scripts via stdin. Second set is related tightly to the development cycle and deals with creation of application environment (database, users, languages, tables, initial data, etc.) where passwords are not sensitive. Because different psql scripts access same database several times I am now forced to type the password again and again and ... so on and so forth (as lord Farquad would put it :). So an option to FORCE reading password from stdin would be nice (without prompting, of course). Maybe this could/can be done differently, but as it was, it was so easy and nice. And if there are more of us, poor souls, with comparable problems maybe an option called '--pwd-from-stdin-only' would be a solution. Another option (thanks God for Open Source projects) is adding an option for prompting into psql by myself. But I see this is a rather drastic measure to get what I need/want. Thanks and greetings from the Sunny side of the Alps! Alfred Anzlovar
On Thu, 4 Jul 2002, Bruce Momjian wrote: > How about PGUSER/PGPASSWORD? That will work. This is assuming you > don't have an OS (BSD?) that displays environment variables for a > process. No BSD, Linux. And it works. Thanks. Well, I must admit we had some rain today, but after your answer sun came from behind the clouds :) More thanks and greetings from the Sunny side of the Alps. Alfred Anzlovar PS Is it ok to send e-mail to pgsql-admin@postgresql.org AND TO you?
Bruce Momjian wrote: ... > > 7.3 may remove PGPASSWORD, I think, and instead allow you to specify a > file that contains the password. But do you know how many hours it took me to find out about PGPASSWORD in the docs and now you want to change that? :-) How about all 4 approaches: on the command-line, from an env-var, from a file, and finally prompting if there's a tty. Pick any order you want on the first 3, but flexibility is important, and circumstances do change over time that may make 1 more desireable then the others. Just a thought... Kevin
Kevin Brannen wrote: > Bruce Momjian wrote: > ... > > > > 7.3 may remove PGPASSWORD, I think, and instead allow you to specify a > > file that contains the password. > > But do you know how many hours it took me to find out about PGPASSWORD > in the docs and now you want to change that? :-) > > How about all 4 approaches: on the command-line, from an env-var, from > a file, and finally prompting if there's a tty. Pick any order you want > on the first 3, but flexibility is important, and circumstances do > change over time that may make 1 more desireable then the others. PGPASSWORD is a security problem on platforms that can show environment variables, mostly *BSD's, and most people don't know it is visible. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote: > Kevin Brannen wrote: > >>Bruce Momjian wrote: >>... >> >>>7.3 may remove PGPASSWORD, I think, and instead allow you to specify a >>>file that contains the password. >> >>But do you know how many hours it took me to find out about PGPASSWORD >>in the docs and now you want to change that? :-) >> >>How about all 4 approaches: on the command-line, from an env-var, from >>a file, and finally prompting if there's a tty. Pick any order you want >>on the first 3, but flexibility is important, and circumstances do >>change over time that may make 1 more desireable then the others. > > > PGPASSWORD is a security problem on platforms that can show environment > variables, mostly *BSD's, and most people don't know it is visible. > Yes, that was part of my point. By having multiple ways to pass the password (coded in 1 library function that can be used everywhere), people can pick what works for them. If their platform has a security problem with env-var, they can ignore that and use one of the other approaches. Or if they have my situation where Postgres only runs on a personal development machine or on the production machine where no one but admins logon to, then we can use one of the more simple approaches (passing it on the command-line or via STDIN). Witness the fact that there's been a patch for 7.2.x recently posted to change the functionality back to reading the password from STDIN. Flexibility is a good thing. :-) I'll even go so far as to say it's important enough that if you, or someone else on the design team, will give me the order of where to check, I'll even write the library function. The order I would propose would be: env-var, command-line, STDIN, interactive prompt if we have a tty. Reorder as you see fit. Kevin p.s. if this goes further, this probably belongs on a hackers/developers list, but I don't subscribe to that.
On Wednesday, July 10, 2002, at 08:54 AM, Kevin Brannen wrote: Stuff deleted > The order I would propose would be: env-var, command-line, STDIN, > interactive prompt if we have a tty. Reorder as you see fit. To the above list I would add a .psql (or something) file in the home dir of the person running psql with 600 perms of course. In this file you could have a password or perhaps an alternate username/password pair. Might also be a good place for those psql environment settings you always use. hal