Thread: How to change the default database for a user

Hello,

I've created a user and a database both wrongly named "phpbb".
After that I have renamed both to "punbb" using "ALTER DATABASE"
and "ALTER USER". Now everything works fine, except I always
have to specify the database when connecting (both using psql or
the PQconnectdb() from my C-program):

    h754814:afarber {103} psql -U punbb
    psql: FATAL:  database "phpbb" does not exist

    h754814:afarber {104} psql -U punbb punbb
    Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
    ....
    punbb-> \l
              List of databases
       Name    |    Owner    | Encoding
    -----------+-------------+-----------
     postgres  | _postgresql | SQL_ASCII
     punbb     | _postgresql | SQL_ASCII
     template0 | _postgresql | SQL_ASCII
     template1 | _postgresql | SQL_ASCII
    (4 rows)

    punbb-> \du
                                    List of roles
      Role name  | Superuser | Create role | Create DB | Connections |
Member of
    -------------+-----------+-------------+-----------+-------------+-----------
     _postgresql | yes       | yes         | yes       | no limit    |
     punbb       | no        | no          | no        | no limit    |
    (2 rows)

    punbb-> select * from pg_user;
       usename   | usesysid | usecreatedb | usesuper | usecatupd |
passwd  | valuntil | useconfig
    -------------+----------+-------------+----------+-----------+----------+----------+-----------
     _postgresql |       10 | t           | t        | t         |
******** |          |
     punbb       |    16384 | f           | f        | f         |
******** |          |
    (2 rows)

How do I please set the database "punbb" to be the default
database for the user called "punbb"?

Regards
Alex

Alexander Farber <alexander.farber@gmail.com> writes:

> Hello,
>
> I've created a user and a database both wrongly named "phpbb".
> After that I have renamed both to "punbb" using "ALTER DATABASE"
> and "ALTER USER". Now everything works fine, except I always
> have to specify the database when connecting (both using psql or
> the PQconnectdb() from my C-program):
>
>     h754814:afarber {103} psql -U punbb
>     psql: FATAL:  database "phpbb" does not exist

psql uses your Unix username as the default database if you don't
specify one.

-Doug

Hi,

On 1/30/06, Doug McNaught <doug@mcnaught.org> wrote:
> Alexander Farber <alexander.farber@gmail.com> writes:
> >     h754814:afarber {103} psql -U punbb
> >     psql: FATAL:  database "phpbb" does not exist
>
> psql uses your Unix username as the default database if you don't
> specify one.

but I do specify the database username:

    h754814:afarber {109} psql --help | grep -w user
      -U NAME         database user name (default: "phpbb")

    h754814:afarber {110} psql -U punbb
    psql: FATAL:  database "phpbb" does not exist

And it wants to connect to a wrong database
("phpbb" instead of "punbb")

Regards
Alex

Alexander Farber <alexander.farber@gmail.com> writes:

> but I do specify the database username:
>
>     h754814:afarber {109} psql --help | grep -w user
>       -U NAME         database user name (default: "phpbb")
>
>     h754814:afarber {110} psql -U punbb
>     psql: FATAL:  database "phpbb" does not exist
>
> And it wants to connect to a wrong database
> ("phpbb" instead of "punbb")

I was talking about the name of the database, not the user name in the
database.  The former defaults to the same as your Unix username
(e.g. if I log in as 'doug' psql will try to connect me to a database
called 'doug' if I don't supply a database name).  The PGDATABASE
environment variable overrides this.  Are you sure it's not set?

Anyway, you should always supply the database name explicitly when you
connect--it's a lot safer.

-Doug

Argh, I had "phpbb" in my env ( ~/.login) too:

setenv  PGDATA          /var/postgresql/data
setenv  PGDATABASE      phpbb
setenv  PGHOST          /var/www/tmp
setenv  PGUSER          phpbb

Now everything makes sense (I was thinking
about some kind of system table for default databases).

Sorry and thank you for your help

Regards
Alex


On 1/30/06, Doug McNaught <doug@mcnaught.org> wrote:
> I was talking about the name of the database, not the user name in the
> database.  The former defaults to the same as your Unix username
> (e.g. if I log in as 'doug' psql will try to connect me to a database
> called 'doug' if I don't supply a database name).  The PGDATABASE
> environment variable overrides this.  Are you sure it's not set?
>
> Anyway, you should always supply the database name explicitly when you
> connect--it's a lot safer.