createuser: How to specify a database to connect to

2017-03-13 16:29 GMT+01:00 Schmid Andreas <Andreas.Schmid@bd.so.ch>:

Hi

I'm trying to add a new DB user with the following command from my client
machine:
createuser -h my.host.name -U mysuperusername --pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL: no pg_hba.conf
entry for host "10.0.0.1", user "mysuperusername", database "postgres", SSL
on

Now, it's true that our pg_hba.conf doesn't allow access to the postgres
database. We did this intentionally, as usually no one needs to connect to
this database.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of another
way to achieve what I'm trying?

I whish to do it with createuser rather than with the SQL command CREATE
USER because this way I can avoid the password for the new user to show up
anywhere in the history.

I'm on 9.2 on Ubuntu 14.04.

You can't. The createuser.c code specifically targets the "postgres"
database, which surprises me. Anyway, the only other way to do it is to use
psql, something like: psql -c "CREATE USER..." -h ... your_database

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Schmid Andreas <Andreas.Schmid@bd.so.ch> writes:

I'm trying to add a new DB user with the following command from my client machine:
createuser -h my.host.name -U mysuperusername --pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL: no pg_hba.conf entry for host "10.0.0.1", user "mysuperusername", database "postgres", SSL on

Now, it's true that our pg_hba.conf doesn't allow access to the postgres database. We did this intentionally, as usually no one needs to connect to this database.

That may have been intentional but it was still a bad decision; the entire
point of the postgres database is to have a default landing-place for
connections that don't need to connect to a specific database within
the cluster.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of another way to achieve what I'm trying?

CREATE USER?

I whish to do it with createuser rather than with the SQL command CREATE USER because this way I can avoid the password for the new user to show up anywhere in the history.

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you. You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.

If by "history" you mean ~/.psql_history, you could turn that off (psql -n)
or to protect the password specifically, you could use psql's \password
command.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/13/2017 08:44 AM, Guillaume Lelarge wrote:

2017-03-13 16:29 GMT+01:00 Schmid Andreas <Andreas.Schmid@bd.so.ch
<mailto:Andreas.Schmid@bd.so.ch>>:

Hi

I'm trying to add a new DB user with the following command from my
client machine:
createuser -h my.host.name <http://my.host.name&gt; -U mysuperusername
--pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL: no
pg_hba.conf entry for host "10.0.0.1", user "mysuperusername",
database "postgres", SSL on

Now, it's true that our pg_hba.conf doesn't allow access to the
postgres database. We did this intentionally, as usually no one
needs to connect to this database.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of
another way to achieve what I'm trying?

I whish to do it with createuser rather than with the SQL command
CREATE USER because this way I can avoid the password for the new
user to show up anywhere in the history.

I'm on 9.2 on Ubuntu 14.04.

You can't. The createuser.c code specifically targets the "postgres"
database, which surprises me. Anyway, the only other way to do it is to
use psql, something like: psql -c "CREATE USER..." -h ... your_database

Unfortunately that stills leaves the password in the Postgres log which
is what the OP is trying to avoid. The immediate solution would be to
open the postgres database in pg_hba.conf. A longer term solution would
be to file an issue and see if the code can be changed to allow
specifying a database to createuser.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2017-03-13 16:57 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 03/13/2017 08:44 AM, Guillaume Lelarge wrote:

2017-03-13 16:29 GMT+01:00 Schmid Andreas <Andreas.Schmid@bd.so.ch
<mailto:Andreas.Schmid@bd.so.ch>>:

Hi

I'm trying to add a new DB user with the following command from my
client machine:
createuser -h my.host.name <http://my.host.name&gt; -U mysuperusername
--pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL: no
pg_hba.conf entry for host "10.0.0.1", user "mysuperusername",
database "postgres", SSL on

Now, it's true that our pg_hba.conf doesn't allow access to the
postgres database. We did this intentionally, as usually no one
needs to connect to this database.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of
another way to achieve what I'm trying?

I whish to do it with createuser rather than with the SQL command
CREATE USER because this way I can avoid the password for the new
user to show up anywhere in the history.

I'm on 9.2 on Ubuntu 14.04.

You can't. The createuser.c code specifically targets the "postgres"
database, which surprises me. Anyway, the only other way to do it is to
use psql, something like: psql -c "CREATE USER..." -h ... your_database

Unfortunately that stills leaves the password in the Postgres log which is
what the OP is trying to avoid. The immediate solution would be to open the
postgres database in pg_hba.conf. A longer term solution would be to file
an issue and see if the code can be changed to allow specifying a database
to createuser.

It's not very hard to do. But I really wonder why it's not already done. I
fear there was a good idea, but I fail to see which one :)

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

On 03/13/2017 08:52 AM, Tom Lane wrote:

Schmid Andreas <Andreas.Schmid@bd.so.ch> writes:

I'm trying to add a new DB user with the following command from my client machine:
createuser -h my.host.name -U mysuperusername --pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL: no pg_hba.conf entry for host "10.0.0.1", user "mysuperusername", database "postgres", SSL on

Now, it's true that our pg_hba.conf doesn't allow access to the postgres database. We did this intentionally, as usually no one needs to connect to this database.

That may have been intentional but it was still a bad decision; the entire
point of the postgres database is to have a default landing-place for
connections that don't need to connect to a specific database within
the cluster.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of another way to achieve what I'm trying?

CREATE USER?

I whish to do it with createuser rather than with the SQL command CREATE USER because this way I can avoid the password for the new user to show up anywhere in the history.

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you. You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.

There is a difference though:

createuser:

postgres-2017-03-13 09:02:57.980 PDT-0LOG: statement: CREATE ROLE
dummy_user PASSWORD 'md5beb9541d2dcea94e091cf05f1f526d32' NOSUPERUSER
NOCREATEDB NOCREATEROLE INHERIT LOGIN;

psql> CREATE USER:

postgres-2017-03-13 09:03:27.147 PDT-0LOG: statement: create user
dummy_user with login password '1234';

If by "history" you mean ~/.psql_history, you could turn that off (psql -n)
or to protect the password specifically, you could use psql's \password
command.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Guillaume Lelarge <guillaume@lelarge.info> writes:

It's not very hard to do. But I really wonder why it's not already done. I
fear there was a good idea, but I fail to see which one :)

The core reason why we haven't complicated createuser in that particular
direction is that createuser is only a convenience function for easy
cases. There is not anything it could do for you that you can't do in
psql, and there are multiple cases that it doesn't attempt to handle
at all (some of the less-common options to CREATE USER, for instance).
I don't have any problem with "I decided to get rid of the postgres
database" being one of the unhandled cases.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2017-03-13 17:10 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

Guillaume Lelarge <guillaume@lelarge.info> writes:

It's not very hard to do. But I really wonder why it's not already done.

I

fear there was a good idea, but I fail to see which one :)

The core reason why we haven't complicated createuser in that particular
direction is that createuser is only a convenience function for easy
cases. There is not anything it could do for you that you can't do in
psql, and there are multiple cases that it doesn't attempt to handle
at all (some of the less-common options to CREATE USER, for instance).
I don't have any problem with "I decided to get rid of the postgres
database" being one of the unhandled cases.

Sure, I understand. That's fine with me. Thanks for the explanation.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 03/13/2017 08:52 AM, Tom Lane wrote:

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you. You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.

There is a difference though:

psql> CREATE USER:

postgres-2017-03-13 09:03:27.147 PDT-0LOG: statement: create user
dummy_user with login password '1234';

Well, what you're supposed to do is

postgres=# create user dummy_user;
postgres=# \password dummy_user
Enter new password:
Enter it again:
postgres=#

which will result in sending something like

ALTER USER dummy_user PASSWORD 'md5c5e9567bc40082671d02c654260e0e09'

You can additionally protect that by wrapping it into one transaction
(if you have a setup where the momentary existence of the role without a
password would be problematic) and/or shutting off logging beforehand.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/13/2017 09:19 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 03/13/2017 08:52 AM, Tom Lane wrote:

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you. You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.

There is a difference though:

psql> CREATE USER:

postgres-2017-03-13 09:03:27.147 PDT-0LOG: statement: create user
dummy_user with login password '1234';

Well, what you're supposed to do is

postgres=# create user dummy_user;
postgres=# \password dummy_user
Enter new password:
Enter it again:
postgres=#

which will result in sending something like

ALTER USER dummy_user PASSWORD 'md5c5e9567bc40082671d02c654260e0e09'

You can additionally protect that by wrapping it into one transaction
(if you have a setup where the momentary existence of the role without a
password would be problematic) and/or shutting off logging beforehand.

Got it.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----Ursprüngliche Nachricht-----
Von: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Gesendet: Montag, 13. März 2017 17:28
An: Tom Lane
Cc: Schmid Andreas; 'pgsql-general@postgresql.org'
Betreff: Re: [GENERAL] createuser: How to specify a database to connect to

On 03/13/2017 09:19 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 03/13/2017 08:52 AM, Tom Lane wrote:

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you. You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.

There is a difference though:

psql> CREATE USER:

postgres-2017-03-13 09:03:27.147 PDT-0LOG: statement: create user
dummy_user with login password '1234';

Well, what you're supposed to do is

postgres=# create user dummy_user;
postgres=# \password dummy_user
Enter new password:
Enter it again:
postgres=#

which will result in sending something like

ALTER USER dummy_user PASSWORD 'md5c5e9567bc40082671d02c654260e0e09'

You can additionally protect that by wrapping it into one transaction
(if you have a setup where the momentary existence of the role without a
password would be problematic) and/or shutting off logging beforehand.

Got it.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

Got it, too.
I actually was worried about the .psql_history.
So my command for adding a new user is going to be

echo 'CREATE USER dummy_user; \password dummy_user' | psql -h my.host.name dbname myusername

(I like the one liners.)

Thanks a lot to all of you for pointing out these different solutions. Great to know that even if we apparently configured it in an unreasonable way, PostgreSQL still allows me to achieve what I want...

Best wishes,
Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general