BUG #13702: pg_dump interprets “=” in a db name incorrectly

On Thu, Oct 22, 2015 at 8:34 PM, <felipe@felipegasper.com> wrote:

The following bug has been logged on the website:

Bug reference: 13702
Logged by: Felipe Gasper
Email address: felipe@felipegasper.com
PostgreSQL version: Unsupported/Unknown
Operating system: Linux
Description:

For the purposes of an administrative frontend that I help
develop/maintain,
I have a database named:

--------------
abcde_1234567890-=~!@#$%^&*()_+[]\{}|;:?,.<b>&lt;b&gt; spaces
--------------

When I try to back up this DB via pg_dump, the utility appears to split the
DB name on the “=”, thinking that it’s another command-line option.

I’ve tried passing in “--”, but no joy. I tried passing “--dbname” then the
DB name, but pg_dump doesn’t seem to accept that argument.

"--dbname=..." ​Documented 9.3 and above​

The only working solution I have found is to set the DB name in PGDATABASE.

I found this in 9.0.18. Maybe it’s still a problem in supported releases,
too?

It was decided to make the database name in this situation be an abstract
concept as opposed to strictly referring to a named database. Because of
this the equal sign has a special meaning that, apparently, cannot be
overridden.

You may want to consider URI format - the following appears to work (9.3
tested)

psql postgres:///db=123

psql: FATAL: database "db=123" does not exist

David J.

On Thu, Oct 22, 2015 at 8:56 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Oct 22, 2015 at 8:34 PM, <felipe@felipegasper.com> wrote:

The following bug has been logged on the website:

Bug reference: 13702

I’ve tried passing in “--”, but no joy. I tried passing “--dbname” then
the
DB name, but pg_dump doesn’t seem to accept that argument.

"--dbname=..." ​Documented 9.3 and above​

The only working solution I have found is to set the DB name in
PGDATABASE.

I found this in 9.0.18. Maybe it’s still a problem in supported releases,
too?

It was decided to make the database name in this situation be an abstract
concept as opposed to strictly referring to a named database. Because of
this the equal sign has a special meaning that, apparently, cannot be
overridden.

You may want to consider URI format - the following appears to work (9.3
tested)

psql postgres:///db=123
​​

psql: FATAL: database "db=123" does not exist

​Actually, simply placing the actual name into any "conn info" context
works (i.e., not just URI form)

psql dbname=db=123
​
​

psql: FATAL: database "db=123" does not exist

David J.
​

On 22 Oct 2015 7:56 PM, David G. Johnston wrote:

On Thu, Oct 22, 2015 at 8:34 PM, <felipe@felipegasper.com
<mailto:felipe@felipegasper.com>>wrote:

The following bug has been logged on the website:

Bug reference: 13702
Logged by: Felipe Gasper
Email address: felipe@felipegasper.com <mailto:felipe@felipegasper.com>
PostgreSQL version: Unsupported/Unknown
Operating system: Linux
Description:

For the purposes of an administrative frontend that I help
develop/maintain,
I have a database named:

--------------
abcde_1234567890-=~!@#$%^&*()_+[]\{}|;:?,.<b>&lt;b&gt; spaces
--------------

When I try to back up this DB via pg_dump, the utility appears to
split the
DB name on the “=”, thinking that it’s another command-line option.

I’ve tried passing in “--”, but no joy. I tried passing “--dbname”
then the
DB name, but pg_dump doesn’t seem to accept that argument.

"--dbname=..." ​Documented 9.3 and above​

The only working solution I have found is to set the DB name in
PGDATABASE.

I found this in 9.0.18. Maybe it’s still a problem in supported
releases,
too?

It was decided to make the database name in this situation be an
abstract concept as opposed to strictly referring to a named database.
Because of this the equal sign has a special meaning that, apparently,
cannot be overridden.

You may want to consider URI format - the following appears to work (9.3
tested)

psql postgres:///db=123

psql: FATAL: database "db=123" does not exist

Interesting -- also, in 9.0 I *can* do:

dbname='...'

… and it works. Undocumented, apparently (?), but it does the job.

But, I actually have to support all the way back to 8.1, and I’m a bit
leery of relying on undocumented (?) features for our backup
functionality. Hopefully down the road we can just move everything to

=9.3, and peace and love will reign. :)

Thank you!

-Felipe Gasper
Houston, TX

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

On Thu, 22 Oct 2015 20:30:20 -0500
Felipe Gasper <felipe@felipegasper.com> wrote:

Interesting -- also, in 9.0 I *can* do:

dbname='...'

… and it works. Undocumented, apparently (?), but it does the job.

But, I actually have to support all the way back to 8.1, and I’m a bit
leery of relying on undocumented (?) features for our backup
functionality. Hopefully down the road we can just move everything to

=9.3, and peace and love will reign. :)

You can dump and load older version database from newer version clients.
The dbname parsing problem you are having is on the client, not the server,
so if you can arrange to take the backup with a more current client then
one of the suggestions in this thread will work for you.

-dg

--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.

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

Apparently 9.0 can’t pg_restore DBs that have equals signs in them...at
least, such is the case with pg_restore on 9.0.18. It gets confused by
the “=”, even when it’s in the --dbname argument.

To me, that seems like a bug, as there’s no reason that the --dbname
argument should be interpreted as anything other than the name of a
database.

Does anyone here happen to know when pg_restore offered alternative ways
of specifying the database?

-FG

On 23 Oct 2015 7:45 AM, David Gould wrote:

On Thu, 22 Oct 2015 20:30:20 -0500
Felipe Gasper <felipe@felipegasper.com> wrote:

Interesting -- also, in 9.0 I *can* do:

dbname='...'

… and it works. Undocumented, apparently (?), but it does the job.

But, I actually have to support all the way back to 8.1, and I’m a bit
leery of relying on undocumented (?) features for our backup
functionality. Hopefully down the road we can just move everything to

=9.3, and peace and love will reign. :)

You can dump and load older version database from newer version clients.
The dbname parsing problem you are having is on the client, not the server,
so if you can arrange to take the backup with a more current client then
one of the suggestions in this thread will work for you.

-dg

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

On Fri, Oct 23, 2015 at 2:20 PM, Felipe Gasper <felipe@felipegasper.com>
wrote:

Apparently 9.0 can’t pg_restore DBs that have equals signs in them...at
least, such is the case with pg_restore on 9.0.18. It gets confused by the
“=”, even when it’s in the --dbname argument.

To me, that seems like a bug, as there’s no reason that the --dbname
argument should be interpreted as anything other than the name of a
database.

Does anyone here happen to know when pg_restore offered alternative ways
of specifying the database?

-FG

On 23 Oct 2015 7:45 AM, David Gould wrote:

On Thu, 22 Oct 2015 20:30:20 -0500
Felipe Gasper <felipe@felipegasper.com> wrote:

Interesting -- also, in 9.0 I *can* do:

dbname='...'

… and it works. Undocumented, apparently (?), but it does the job.

But, I actually have to support all the way back to 8.1, and I’m a bit
leery of relying on undocumented (?) features for our backup
functionality. Hopefully down the road we can just move everything to

=9.3, and peace and love will reign. :)

You can dump and load older version database from newer version clients.
The dbname parsing problem you are having is on the client, not the
server,
so if you can arrange to take the backup with a more current client then
one of the suggestions in this thread will work for you.

​It would help greatly if you post full command lines and error messages...

Again, the "--dbname" argument can either be an actual database name or a
"connection info" object. In the later case the "=" has a special
meaning. If your database has an "=" in its name then it seems like you
have to use the "connection info" definition form. Note I haven't dug into
this with respect to pg_restore but that is the findings from the brief
psql test I performed.

David J.
​

On 23 Oct 2015 1:33 PM, David G. Johnston wrote:

On Fri, Oct 23, 2015 at 2:20 PM, Felipe Gasper <felipe@felipegasper.com
<mailto:felipe@felipegasper.com>>wrote:

Apparently 9.0 can’t pg_restore DBs that have equals signs in
them...at least, such is the case with pg_restore on 9.0.18. It gets
confused by the “=”, even when it’s in the --dbname argument.

To me, that seems like a bug, as there’s no reason that the --dbname
argument should be interpreted as anything other than the name of a
database.

Does anyone here happen to know when pg_restore offered alternative
ways of specifying the database?

-FG

On 23 Oct 2015 7:45 AM, David Gould wrote:

On Thu, 22 Oct 2015 20:30:20 -0500
Felipe Gasper <felipe@felipegasper.com
<mailto:felipe@felipegasper.com>> wrote:

Interesting -- also, in 9.0 I *can* do:

dbname='...'

… and it works. Undocumented, apparently (?), but it does
the job.

But, I actually have to support all the way back to 8.1, and
I’m a bit
leery of relying on undocumented (?) features for our backup
functionality. Hopefully down the road we can just move
everything to

=9.3, and peace and love will reign. :)

You can dump and load older version database from newer version
clients.
The dbname parsing problem you are having is on the client, not
the server,
so if you can arrange to take the backup with a more current
client then
one of the suggestions in this thread will work for you.

​It would help greatly if you post full command lines and error messages...

Sorry; I hope the following is more helpful.

Again, the "--dbname" argument can either be an actual database name or
a "connection info" object.

But the documentation doesn’t say that; it only says that it’s the name
of the database. Should the documentation be updated, then?

In the later case the "=" has a special
meaning. If your database has an "=" in its name then it seems like you
have to use the "connection info" definition form. Note I haven't dug
into this with respect to pg_restore but that is the findings from the
brief psql test I performed.

Is “connection info” an option with pg_restore, though, which mandates a
--dbname argument?

Here is the execve from strace:

------------
[pid 21172] execve("/usr/local/pgsql/bin/pg_restore",
["/usr/local/pgsql/bin/pg_restore", "--username=postgres",
"--host=/tmp/TMP.work.mVasksDJkt0CEJJh/socket", "--no-privileges",
"--no-owner", "-d", "weird alpha_0123~-_=+[{]}\\\\;:,<.>?",
"--single-transaction", "--role", "weird
alpha_0123~-_=+[{]}\\\\;:,<.>?"], [/* 8 vars */]) = 0
------------

It spits out the following on STDERR:

-------------
[archiver (db)] connection to database "" failed: missing "=" after
"weird" in connection info string
-------------

It makes sense that the “=” would be interpreted as a special character
in pg_dump when there is no --dbname parameter. But the docs say that
--dbname is for a database name; there is no mention of any kind of
parsing otherwise. Should it not work that way? What purpose is served
by passing configuration parameters in the database name?

This does work with 9.4 when I use the “postgres:///…” syntax; however,
as we’re stuck supporting older PgSQLs for the time being (*sigh* even
back to 8.1), and as PGDATABASE doesn’t work with pg_restore, are we
left with having to prohibit “=” in database names?

-FG

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

On Fri, Oct 23, 2015 at 2:54 PM, Felipe Gasper <felipe@felipegasper.com>
wrote:

On 23 Oct 2015 1:33 PM, David G. Johnston wrote:

On Fri, Oct 23, 2015 at 2:20 PM, Felipe Gasper <felipe@felipegasper.com
<mailto:felipe@felipegasper.com>>wrote:

Again, the "--dbname" argument can either be an actual database name or
a "connection info" object.

But the documentation doesn’t say that; it only says that it’s the name of
the database. Should the documentation be updated, then?

In the later case the "=" has a special

meaning. If your database has an "=" in its name then it seems like you
have to use the "connection info" definition form. Note I haven't dug
into this with respect to pg_restore but that is the findings from the
brief psql test I performed.

Is “connection info” an option with pg_restore, though, which mandates a
--dbname argument?

​
http://www.postgresql.org/docs/9.4/interactive/libpq-connect.html#LIBPQ-CONNECT-DBNAME
​
dbname
The database name. Defaults to be the same as the user name. In certain
contexts, the value is checked for extended formats; see Section 31.1.1 for
more details on those.

​31.1.1
http://www.postgresql.org/docs/9.4/interactive/libpq-connect.html#LIBPQ-CONNSTRING
​
​See "dbname" in particular

So it is documented.​ Suggestions and patches welcomed if you think it
should be covered differently.

Here is the execve from strace:

------------
[pid 21172] execve("/usr/local/pgsql/bin/pg_restore",
["/usr/local/pgsql/bin/pg_restore", "--username=postgres",
"--host=/tmp/TMP.work.mVasksDJkt0CEJJh/socket", "--no-privileges",
"--no-owner", "-d", "weird alpha_0123~-_=+[{]}\\\\;:,<.>?",
"--single-transaction", "--role", "weird alpha_0123~-_=+[{]}\\\\;:,<.>?"],
[/* 8 vars */]) = 0
------------

It spits out the following on STDERR:

-------------
[archiver (db)] connection to database "" failed: missing "=" after
"weird" in connection info string
-------------

It makes sense that the “=” would be interpreted as a special character in
pg_dump when there is no --dbname parameter. But the docs say that --dbname
is for a database name; there is no mention of any kind of parsing
otherwise. Should it not work that way? What purpose is served by passing
configuration parameters in the database name?

​At this point "purpose" doesn't really enter into it. That is how it was
decided things would work and we have to live with it.​ As your particular
situation makes perfectly even if we were to design a better API it
wouldn't apply retroactively. So while fixes for found issues in the
current architecture are welcomed there is likely zero interest in
improving an architecture that while probably imperfect is at least
functional.

This does work with 9.4 when I use the “postgres:///…” syntax; however, as
we’re stuck supporting older PgSQLs for the time being (*sigh* even back to
8.1), and as PGDATABASE doesn’t work with pg_restore, are we left with
having to prohibit “=” in database names?

​pg_restore --dbname="dbname=crazy=db=name" [...] likely will work but I
cannot quickly test it ATM.

David J.
​

On 23 Oct 2015 2:14 PM, David G. Johnston wrote:

On Fri, Oct 23, 2015 at 2:54 PM, Felipe Gasper <felipe@felipegasper.com
<mailto:felipe@felipegasper.com>>wrote:

On 23 Oct 2015 1:33 PM, David G. Johnston wrote:

On Fri, Oct 23, 2015 at 2:20 PM, Felipe Gasper
<felipe@felipegasper.com <mailto:felipe@felipegasper.com>
<mailto:felipe@felipegasper.com
<mailto:felipe@felipegasper.com>>>wrote:

Again, the "--dbname" argument can either be an actual database
name or
a "connection info" object.

But the documentation doesn’t say that; it only says that it’s the
name of the database. Should the documentation be updated, then?

In the later case the "=" has a special
meaning. If your database has an "=" in its name then it seems
like you
have to use the "connection info" definition form. Note I
haven't dug
into this with respect to pg_restore but that is the findings
from the
brief psql test I performed.

Is “connection info” an option with pg_restore, though, which
mandates a --dbname argument?

​
http://www.postgresql.org/docs/9.4/interactive/libpq-connect.html#LIBPQ-CONNECT-DBNAME
​
dbname
The database name. Defaults to be the same as the user name. In certain
contexts, the value is checked for extended formats; see Section 31.1.1
for more details on those.

​31.1.1
http://www.postgresql.org/docs/9.4/interactive/libpq-connect.html#LIBPQ-CONNSTRING
​
​See "dbname" in particular

So it is documented.​ Suggestions and patches welcomed if you think it
should be covered differently.

Ah, ok.

The reason why I didn’t find this myself was that there’s nothing that
leads me from the pg_restore docs’ description of the --dbname parameter
to the sections you’ve mentioned above.

Maybe that’s the thing, then--would it work to add a link to describe
how pg_restore parses the argument to “--dbname”? A usage example, too,
perhaps, to show how any valid DB name that the PostgreSQL server
recognizes can be fed into pg_restore, pg_dump, etc.?

​pg_restore --dbname="dbname=crazy=db=name" [...] likely will work but I
cannot quickly test it ATM.

Hooray! At least in 9.0.18, if I quote the DB name as conninfo string, I
can indeed restore to a DB with spaces, equals, etc. in its name.

Now to try it in 8.1. If it doesn’t work there, we’ll probably just
forbid “=” in DB names and call it a day.

Thank you for your responses. I hope I’ve not been untoward in any of this

-FG

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