Re: [GENERAL] createuser: How to specify a database to connect to

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
usuallyno 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
passwordfor 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