Обсуждение: Automating logins for mundane chores
I am writing some simple batch scripts to login to the DB and do a pg_dump. Also, when I login to do my own SQL tinkering, I'd like not to be asked for a password every time (which, for silly corporate reasons, is quite a convoluted one). So I read up on .pgpass. Where should this file be located. "User's home directory" says the manual, but there's no "home directory" for database users (or is there? if so, where?), only for the postgres user. So I promptly did "su - postgres" and added the requisite info in the .pgpass file therein. But that doesn't seem to automate anything for actual DB users. Next, the manual refers to some PGPASSFILE env variable ( http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html ) but it is unclear where this environment is set up. I don't see any such setting in the postgresql.conf file, which wouldn't make much sense anyway. Where else can I tweak the environment variables? No pointer in the manual. Or was this page only some arcane C libpg stuff? So, my questions: 1. Where do I set up the automated password for (a) psql stuff and (b) for bash scripts or cron jobs -- I suppose both could have the same solution. 2. While we're on psql, I quite like the "\timing" stuff inside psql. I find it very useful to have that on every time I login to psql console, but I don't see any command line option to automate this every time. The psql man page ( http://www.postgresql.org/docs/8.2/interactive/app-psql.html ) talks about a "psqlrc", which on my system is found at "/usr/share/pgsql/psqlrc" so I opened it up, entered the only line: \timing And saved it. Then I started the psql console again, but there's no timing on by default. How can I setup default options for psql? Many TIA!
Phoenix Kiula wrote: > I am writing some simple batch scripts to login to the DB and do a > pg_dump. Also, when I login to do my own SQL tinkering, I'd like not > to be asked for a password every time (which, for silly corporate > reasons, is quite a convoluted one). > > So I read up on .pgpass. Where should this file be located. "User's > home directory" says the manual, but there's no "home directory" for > database users (or is there? if so, where?), only for the postgres > user. So I promptly did "su - postgres" and added the requisite info > in the .pgpass file therein. But that doesn't seem to automate > anything for actual DB users. .pgpass is read by the client. It may not even be located on the same machine as your server, depending on where you run psql. > Next, the manual refers to some PGPASSFILE env variable ( > http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html ) but > it is unclear where this environment is set up. I don't see any such > setting in the postgresql.conf file, which wouldn't make much sense > anyway. Where else can I tweak the environment variables? No pointer > in the manual. Or was this page only some arcane C libpg stuff? It's in the environment on the client machine. If it's for your scripts, you can set it inside the script before you launch psql for example. If you need it. > So, my questions: > > 1. Where do I set up the automated password for (a) psql stuff and (b) > for bash scripts or cron jobs -- I suppose both could have the same > solution. a) In the home directory of the user running psql. b) In the home directory of the user running the cronjob. > 2. While we're on psql, I quite like the "\timing" stuff inside psql. > I find it very useful to have that on every time I login to psql > console, but I don't see any command line option to automate this > every time. The psql man page ( > http://www.postgresql.org/docs/8.2/interactive/app-psql.html ) talks > about a "psqlrc", which on my system is found at > "/usr/share/pgsql/psqlrc" so I opened it up, entered the only line: > > \timing > > And saved it. Then I started the psql console again, but there's no > timing on by default. How can I setup default options for psql? That should be /usr/share/pgsql/psqlrc.sample, which is just a sample file and isn't parsed (unless your distribution did something really strange). On a source install, you need to put the file in /usr/local/pgsql/etc/psqlrc - since you're obviously not using a source install, you'll need to put it wherever your package is configured to have it (should be documented alongside the package, I hope). The easier way is to put it in the file .psqlrc in your home directory (same home directory as you put .pgpass in), assuming you only want this for one user. //Magnus
On 18/08/07, Magnus Hagander <magnus@hagander.net> wrote: > Phoenix Kiula wrote: > > I am writing some simple batch scripts to login to the DB and do a > > pg_dump. Also, when I login to do my own SQL tinkering, I'd like not > > to be asked for a password every time (which, for silly corporate > > reasons, is quite a convoluted one). > > > > It's in the environment on the client machine. If it's for your scripts, > you can set it inside the script before you launch psql for example. If > you need it. Let's say my script was in Perl or PHP. What would the variable name be to set this password? My script is unlikely to call psql, I'm thinking of using only pg_dump and pg_restore. > > 1. Where do I set up the automated password for (a) psql stuff and (b) > > for bash scripts or cron jobs -- I suppose both could have the same > > solution. > > a) In the home directory of the user running psql. > b) In the home directory of the user running the cronjob. Thanks for this. I am logged in as root. Put it there and it works. I also put a ".psqlrc" in the home directory and that works too! Thanks! I'd love to contribute back to the community and mention this in the manual for 8.2/interactive. But the community login and commenting on the site seems to be broken! Even after I am logged in, it does not show it on each page of the site, and when I submit my comment (and login all over again for it) it shows me a "numeric error". Where should I post that error?
Phoenix Kiula wrote: > On 18/08/07, Magnus Hagander <magnus@hagander.net> wrote: >> Phoenix Kiula wrote: >>> I am writing some simple batch scripts to login to the DB and do a >>> pg_dump. Also, when I login to do my own SQL tinkering, I'd like not >>> to be asked for a password every time (which, for silly corporate >>> reasons, is quite a convoluted one). >>> >> It's in the environment on the client machine. If it's for your scripts, >> you can set it inside the script before you launch psql for example. If >> you need it. > > > Let's say my script was in Perl or PHP. What would the variable name > be to set this password? My script is unlikely to call psql, I'm > thinking of using only pg_dump and pg_restore. As long as the interfaced is based off libpq, .pgpass will work. AFAIK, this includes both Perl and PHP, and it certainly includes pg_dump and pg_restore. >>> 1. Where do I set up the automated password for (a) psql stuff and (b) >>> for bash scripts or cron jobs -- I suppose both could have the same >>> solution. >> a) In the home directory of the user running psql. >> b) In the home directory of the user running the cronjob. > > > Thanks for this. I am logged in as root. Put it there and it works. I > also put a ".psqlrc" in the home directory and that works too! Thanks! > > I'd love to contribute back to the community and mention this in the > manual for 8.2/interactive. But the community login and commenting on > the site seems to be broken! Even after I am logged in, it does not > show it on each page of the site, and when I submit my comment (and > login all over again for it) it shows me a "numeric error". Where > should I post that error? It will appear on the site once it's been approved. But if you get an actual error, than that needs to be fixed - please email the complete error you get to the pgsql-www@postgresql.org mailinglist. Thanks! //Magnus
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 08/18/07 06:02, Phoenix Kiula wrote: [snip] > > Thanks for this. I am logged in as root. Put it there and it works. I Well, that's your first problem. And second. And third. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGxwlGS9HxQb37XmcRAvJSAKDP//ElDCzRk2Jcewm1+GVxaeVikACfbk+p 4obghwE8R19ljPRiqRPQQRg= =NYrP -----END PGP SIGNATURE-----
On 18/08/07, Ron Johnson <ron.l.johnson@cox.net> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 08/18/07 06:02, Phoenix Kiula wrote: > [snip] > > > > Thanks for this. I am logged in as root. Put it there and it works. I > > Well, that's your first problem. > > And second. And third. Thanks for the kick in the derierre. Have set it all up to operate as user postgres. Or is that insecure too?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Phoenix Kiula wrote:
> On 18/08/07, Ron Johnson <ron.l.johnson@cox.net> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On 08/18/07 06:02, Phoenix Kiula wrote:
>> [snip]
>>> Thanks for this. I am logged in as root. Put it there and it works. I
>> Well, that's your first problem.
>>
>> And second. And third.
>
>
>
> Thanks for the kick in the derierre. Have set it all up to operate as
> user postgres. Or is that insecure too?
Not as insecure, but consider that postgres == PostgreSQL root.
Joshua D. Drake
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGx6oEATb/zqfZUUQRAulwAJ4+Q/ycbnHC7r4c0hDD064DyX034gCfYM04
x363nOHfRIMbxuCANtzzuJQ=
=+iUG
-----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 08/18/07 21:10, Phoenix Kiula wrote: > On 18/08/07, Ron Johnson <ron.l.johnson@cox.net> wrote: >> >> On 08/18/07 06:02, Phoenix Kiula wrote: >> [snip] >>> Thanks for this. I am logged in as root. Put it there and it works. I >> Well, that's your first problem. >> >> And second. And third. > > > > Thanks for the kick in the derierre. Have set it all up to operate as > user postgres. Or is that insecure too? Whenever thinking about security, the question to ask yourself is: am I doing anything which would make it easier for a Bad Guy to gain access to my data or systems. Then, do the opposite. Examples: Using a powerful account for mundane activities? Use a mundane account instead. (As Joshua pointed out, "postgres" is a powerful account.) Sending important data over the wire (or worse, wireless) in clear text? Encrypt it. Vulnerable to SQL injection attacks by sending fully formed SQL statements across the wire? Use prepared statements instead. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGx6zsS9HxQb37XmcRAndxAJ0YJ1mGQ1+erBsDuq3/iCN3q6ZcsgCgsVpd F0/q8sPWoWs4qgFhbP65NyM= =syP0 -----END PGP SIGNATURE-----
On Aug 18, 2007, at 5:20 AM, Phoenix Kiula wrote: > I am writing some simple batch scripts to login to the DB and do a > pg_dump. Also, when I login to do my own SQL tinkering, I'd like not > to be asked for a password every time (which, for silly corporate > reasons, is quite a convoluted one). > > So I read up on .pgpass. FWIW, *IF* you can trust identd in your environment, I find it to be easier to deal with than .pgpass or the like. -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)