Re: Alternative to psql -c ?

On Wed, 25 Jun 2014 16:42:53 +0200
hubert depesz lubaczewski <depesz@gmail.com> wrote:

> On Wed, Jun 25, 2014 at 4:37 PM, James Le Cuirot
> <chewi@aura-online.co.uk> wrote:
>
> > On Wed, 25 Jun 2014 10:24:53 -0400
> > Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> >
> > > On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote:
> > > > Same problem as stdin, the transactional behaviour is different.
> > > > There is the --single-transaction option but as the man page
> > > > says...
> > > >
> > > > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this
> > > > option will not have the desired effects."
> > >
> > > Hmm.  I've _used_ transactions in such files, I'm pretty sure.
> > > You don't need the --single-transaction setting for this, just do
> > > the BEGIN; and COMMIT; yourself.
> > >
> > > A
> >
> > Sorry, you're missing the point. I'm trying not to alter the
> > existing behaviour of the Chef database cookbook which is used by
> > countless people to execute scripts big and small, with and without
> > transactions. If I just naively wrapped them all in BEGIN/COMMIT
> > then it would override any additional transactions within the
> > scripts.
> >
> > James
>
> Perhaps you can explain what is the functionality you want to
> achieve, as I, for one, don't understand. Do you want transactions?
> Or not?

I want an implicit transaction around the whole script if no explicit
transactions are present in the script. This is what psql's -c option
does and this is also what the pg gem does because both use PQexec.
This behaviour is described near the top of these pages:

http://www.postgresql.org/docs/devel/static/libpq-exec.html
http://www.postgresql.org/docs/devel/static/app-psql.html

> Also - I have no idea what "peer authentication" has to do with Pg
> gem - care to elaborate? The gem is for client, and authentication
> happens in server, so ... ?

Right but peer authentication is all to do with the operating system
user that the client is connecting from. In the case of chef-client,
this is almost always root. Chef cookbooks typically want to create
databases and populate them with tables. Creating databases is
usually done by the postgres user. Since the database cookbook
currently doesn't shell out, the postgres user must be given a
password. Similarly, creating tables is usually done by the user that
will eventually use them, and this user must also be given a password.
Needlessly creating passwords seems like a bad idea, especially since
these passwords get stored in the central Chef database.

Now I did consider that root could shell out to Ruby and use the pg gem
as another user but that would lose the benefit of not having to
install build-essential.

Hope this makes sense now.

Regards,
James