Thread: [pg_hba.conf] publish own Python application using PostgreSQL

I have my own Python application using a PostgreSQL database over
SQLAlchemy.

Currently I pack the application in a deb-file.
After installation (on a fresh system! Ubuntu 14.04.2) it doesn't run
because of some PostgreSQL-settings.
Of course I understand why and I know (a little bit) which settings I
have to do to make it run.

But the point is I don't want to plague my user to do that.

How could this be solved?
How can I release a application using a local PostgreSQL-database.
I am not sure if the modifications I do are correct or elegant (see
below).

These are the modification I have to do to make my application run with
the connetion string "postgres://puser@localhost/FoobarTest".

The settings are about
the /etc/postgresql/9.3/main/pg_hba.conf file.
There I change this line
host    all             all             127.0.0.1/32            md5
to
host    all             all             127.0.0.1/32            trust

I have to create a user without a password (beside the admin/postgres),
too.

Hi

I am not really an expert, but from your description I guess that you
assume an existing PostgreSQL installation on your customers' server. If
that is the case you probably won't get around giving instructions to
your customer and let them do the change. I would not like to install
applications that change settings in pg_hba.conf on my server. Besides
you may consider limiting the trust access to the specific user and
specific database that your application needs to access.

Bye
Charles

On 7/5/2015 12:15, c.buhtz@posteo.jp wrote:
> I have my own Python application using a PostgreSQL database over
> SQLAlchemy.
>
> Currently I pack the application in a deb-file.
> After installation (on a fresh system! Ubuntu 14.04.2) it doesn't run
> because of some PostgreSQL-settings.
> Of course I understand why and I know (a little bit) which settings I
> have to do to make it run.
>
> But the point is I don't want to plague my user to do that.
>
> How could this be solved?
> How can I release a application using a local PostgreSQL-database.
> I am not sure if the modifications I do are correct or elegant (see
> below).
>
> These are the modification I have to do to make my application run with
> the connetion string "postgres://puser@localhost/FoobarTest".
>
> The settings are about
> the /etc/postgresql/9.3/main/pg_hba.conf file.
> There I change this line
> host    all             all             127.0.0.1/32            md5
> to
> host    all             all             127.0.0.1/32            trust
>
> I have to create a user without a password (beside the admin/postgres),
> too.
>
>

On 2015-07-05 15:11 Charles Clavadetscher <clavadetscher@swisspug.org>
wrote:
> I am not really an expert, but from your description I guess that you
> assume an existing PostgreSQL installation on your customers' server.

The application is a simple open source using a local PostgreSQL
database. The customer is just any user out there.
I guess the PostgreSQL instance itself is in most cases fresh/virgin
installed without any configuration done by the user.

> I would not like to install applications that change settings in
> pg_hba.conf

I know that this is a bad solution. It is just a workaround for my
development environment. I just explained that modifications here to
show how bad my workaround is and how less I know about PostgreSQL.

I read unspecific things about a "configuration file" for the
application that make it possible to get access to PostgreSQL without
having root-access to it. But I don't know details about it.
What could this be?

Is it possible for the user to install a PostgreSQL-using application
(including a fresh install and default-configured PostgreSQL) without
modifying the PostgreSQL-configuration?

On 7/5/2015 3:15 AM, c.buhtz@posteo.jp wrote:
> These are the modification I have to do to make my application run with
> the connetion string "postgres://puser@localhost/FoobarTest".
>
> The settings are about
> the /etc/postgresql/9.3/main/pg_hba.conf file.
> There I change this line
> host    all             all             127.0.0.1/32            md5
> to
> host    all             all             127.0.0.1/32            trust
>
> I have to create a user without a password (beside the admin/postgres),
> too.


why not connect as postgres://puser:somepass@localhost/dbname

and create puser with a password ?   that way mucking with configuration
files is not required.




--
john r pierce, recycling bits in santa cruz

On July 5, 2015 08:58:17 PM c.buhtz@posteo.jp wrote:
> On 2015-07-05 15:11 Charles Clavadetscher <clavadetscher@swisspug.org>
>
> wrote:
> > I am not really an expert, but from your description I guess that you
> > assume an existing PostgreSQL installation on your customers' server.
>
> The application is a simple open source using a local PostgreSQL
> database. The customer is just any user out there.
> I guess the PostgreSQL instance itself is in most cases fresh/virgin
> installed without any configuration done by the user.
>
> > I would not like to install applications that change settings in
> > pg_hba.conf
>
> I know that this is a bad solution. It is just a workaround for my
> development environment. I just explained that modifications here to
> show how bad my workaround is and how less I know about PostgreSQL.
>
> I read unspecific things about a "configuration file" for the
> application that make it possible to get access to PostgreSQL without
> having root-access to it. But I don't know details about it.
> What could this be?
>
> Is it possible for the user to install a PostgreSQL-using application
> (including a fresh install and default-configured PostgreSQL) without
> modifying the PostgreSQL-configuration?

You could set up a whole new server with a different $PGDATA on a different
port.

What I'm wondering though is what made you decide to use pgsql for your
project? It seems to me that something like sqlite would be better suited for
your requirements.

On 2015-07-05 15:13 Jan de Visser <jan@de-visser.net> wrote:
> You could set up a whole new server with a different $PGDATA on a
> different port.

I (and the user) don't want to setup anything - that is the point.

> What I'm wondering though is what made you decide to use pgsql for
> your project? It seems to me that something like sqlite would be
> better suited for your requirements.

When I started I wasn't aware of the difference between PostgreSQL and
sqlite. Maybe this is a solution.

But isn't there a way to use PostgreSQL without that setup and
configuration things?

On 7/5/2015 9:43 PM, c.buhtz@posteo.jp wrote:
> But isn't there a way to use PostgreSQL without that setup and
> configuration things?

no, not really, as its a generic database server meant to be used by
multiple applications across a network, with a wide range of
configuration options, plugins and addons, etc.

at a bare minimum, a database administrator needs to create database
roles (users) and databases for an app like yours.


--
john r pierce, recycling bits in santa cruz

On July 6, 2015 06:43:53 AM c.buhtz@posteo.jp wrote:
> On 2015-07-05 15:13 Jan de Visser <jan@de-visser.net> wrote:
> > You could set up a whole new server with a different $PGDATA on a
> > different port.
>
> I (and the user) don't want to setup anything - that is the point.

Well, you don't have to setup anything. You do an initdb in a different
directory, that will write a .conf file there, which you then massage to
include a different port. You'll use the same binaries as the standard pgsql
install, but in a different environment.

Jan de Visser wrote:
> On July 6, 2015 06:43:53 AM c.buhtz@posteo.jp wrote:
>> On 2015-07-05 15:13 Jan de Visser <jan@de-visser.net> wrote:
>>> You could set up a whole new server with a different $PGDATA on a
>>> different port.
>> I (and the user) don't want to setup anything - that is the point.
>
> Well, you don't have to setup anything. You do an initdb in a different
> directory, that will write a .conf file there, which you then massage to
> include a different port. You'll use the same binaries as the standard pgsql
> install, but in a different environment.

I'm not sure that helps, since I think part of the question is what the
"true Debian way" is to massage the configuration files to include
appropriate entries.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

On 07/05/2015 09:43 PM, c.buhtz@posteo.jp wrote:
> On 2015-07-05 15:13 Jan de Visser <jan@de-visser.net> wrote:
>> You could set up a whole new server with a different $PGDATA on a
>> different port.
>
> I (and the user) don't want to setup anything - that is the point.

Then what you want is an embedded database, in other words a program
that you can include inside your application. As others have suggested
Sqlite is just such program and what is more it is included in the
Python standard library since 2.5. Postgres is not an embedded database
and therefore it will by nature exist outside the app. This means either
you have to create code to anticipate all your users setups and
configure Postgres accordingly or you will need to include the user in
the set up process.

>
>> What I'm wondering though is what made you decide to use pgsql for
>> your project? It seems to me that something like sqlite would be
>> better suited for your requirements.
>
> When I started I wasn't aware of the difference between PostgreSQL and
> sqlite. Maybe this is a solution.
>
> But isn't there a way to use PostgreSQL without that setup and
> configuration things?
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 2015-07-05 22:16 John R Pierce <pierce@hogranch.com> wrote:
> at a bare minimum, a database administrator needs to create database
> roles (users) and databases for an app like yours.

The admin don't need to create the db. It is done by the application
(sqlalchemy-utils on Python3) itself.

But I see. I will go back to sqlite3.

On 7/6/2015 9:55 PM, c.buhtz@posteo.jp wrote:
> On 2015-07-05 22:16 John R Pierce<pierce@hogranch.com>  wrote:
>> >at a bare minimum, a database administrator needs to create database
>> >roles (users) and databases for an app like yours.
> The admin don't need to create the db. It is done by the application
> (sqlalchemy-utils on Python3) itself.

an application should not have the privileges to do that.   you don't
run your apps as 'root', do you?   why would you run them as a database
administrator ?



--
john r pierce, recycling bits in santa cruz

On Tue, Jul 07, 2015 at 06:57:45AM -0500, John McKown wrote:

> >>> >at a bare minimum, a database administrator needs to create database
> >>> >roles (users) and databases for an app like yours.
> >>>
> >> The admin don't need to create the db. It is done by the application
> >> (sqlalchemy-utils on Python3) itself.
> >>
> >
> > an application should not have the privileges to do that.   you don't run
> > your apps as 'root', do you?   why would you run them as a database
> > administrator ?
>
>
> ​Trigger Warning (Thanks, Mallard Fillmore)
>
> I agree with you on this. If I were a customer and some vendor said: "Oh
> yes, to run our product, you must configure your multi-user data base to
> disable passwords and run it as a DBA so that it can make schema changes on
> the fly", then I'd simply say "no sale". Of course, in regards to the
> schema, it would be proper to document what the DBA needs to do to set up
> the data base with the proper tables and other items.

In fact, an app might have an option to emit a script for
the DBA to run. Or even offer to run it for the DBA given
proper credentials are provided on the spot.

Karsten Hilbert
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346