Subject: createuser/dropuser username

From: Grzegorz Szpetkowski <[email-masked]>
Date: Wed, 4 May 2011 18:05:39 +0200

Hi,

I think that createuser/drouser reference documentation could be
enhanced for username param. There is:

"createuser is a wrapper around the SQL command CREATE ROLE. There is
no effective difference between creating users via this utility and
via other methods for accessing the server."

http://www.postgresql.org/docs/9.0/static/app-createuser.html
http://www.postgresql.org/docs/9.0/static/app-dropuser.html

But createuser and dropuser wrappers in some cases are doing implicit
quoting (when CREATE ROLE is not), which I think is not fully known
and is undocumented here. For example:

createuser -SDRe user -- reserved in PostgreSQL
CREATE ROLE "user" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

createuser -SDRe myuser
CREATE ROLE myuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

createuser -SDRe MYuser
CREATE ROLE "MYuser" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

psql postgres
ALTER ROLE MYuser CREATEDB; -- fatal mistake, it works for myuser
delimited identifier
ALTER ROLE

ALTER ROLE "MYuser" CREATEDB; -- ok, quoted identifier
ALTER ROLE

ALTER ROLE user CREATEDB;
ERROR:  syntax error at or near "user"
LINE 1: ALTER ROLE user CREATEDB;
                   ^
postgres=# ALTER ROLE "user" CREATEDB;
ALTER ROLE

Regards,
Grzegorz Szpetkowski

---

From: Robert Haas <[email-masked]>
Date: Thu, 19 May 2011 16:15:23 -0400

> and is undocumented here. For example:

I'm not sure this really needs to be documented, but what exactly do
you have in mind?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Susanne Ebrecht <[email-masked]>
Date: Fri, 20 May 2011 11:09:53 +0200

> I'm not sure this really needs to be documented, but what exactly do

Honestly, I think we should document that the scripts use quotes.
I just made an ad-hoc look into the docs and couldn't find something 
about it.

Lots of years ago - you could see me sitting in front of my computer and
wondering - because I used:
$ createdb test
$ createdb tEst
I wondered that I didn't got an error message.

Thinking deeper about it just let it get logical to me that createdb 
quotes by
automatism.

But this isn't logical on the first view - because we point out that all 
will get
to lower case without quotes.

PostgreSQL even is so intelligent - that when you say:
$ createdb "tEst" - it will ignore the quotes - you will get database 
named tEst and not
named "tEst".

This all isn't really obvious to a typical user.

Best Regards,

Susanne

-- 
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 May 2011 08:08:25 -0400

> $ createdb "tEst" - it will ignore the quotes - you will get database 

Not true.  The reason you don't get quotes there is that the shell
stripped them off.  Try '"tEst"' ...

The current behavior is something that we settled on years ago, after
thinking about the interaction between shell quoting rules and SQL
quoting rules.  If we don't treat SQL names appearing on command lines
as spelled literally, then you have to quote them if you don't want them
downcased --- and doing that in a way that the shell won't mangle is
unpleasant, as in my example above.  So we ruled that all utilities that
take a SQL name from the command line should auto-quote the name.

I'm pretty sure this is documented somewhere, but perhaps not in the
place where Grzegorz looked for it ...

			regards, tom lane

---

From: Susanne Ebrecht <[email-masked]>
Date: Fri, 20 May 2011 14:50:59 +0200

> place where Grzegorz looked for it ...

Hehe - that was my  intention too. I looked through the Tutorial and
through some other chapters - but I couldn't find a hint.

Susanne

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 7 Sep 2011 17:48:36 -0400 (EDT)

> place where Grzegorz looked for it ...

Uh, I did some digging after this email report and found it does need
some cleanup, which is done in the attached patch.

It removes quoting for table references in clusterdb and index/table
references in reindexdb, and adds a general documentation overview about
when case is preserved and suggests quoting, and adds documentation
where special quoting happens.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml
new file mode 100644
index 3e50173..60a54cd
*** a/doc/src/sgml/ref/createdb.sgml
--- b/doc/src/sgml/ref/createdb.sgml
*************** PostgreSQL documentation
*** 90,96 ****
        <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term>
        <listitem>
         <para>
!         Specifies the default tablespace for the database.
         </para>
        </listitem>
       </varlistentry>
--- 90,96 ----
        <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term>
        <listitem>
         <para>
!         Specifies the default tablespace for the database (double-quoted internally).
         </para>
        </listitem>
       </varlistentry>
*************** PostgreSQL documentation
*** 153,159 ****
        <term><option>--owner=<replaceable class="parameter">owner</replaceable></></term>
        <listitem>
         <para>
!         Specifies the database user who will own the new database.
         </para>
        </listitem>
       </varlistentry>
--- 153,160 ----
        <term><option>--owner=<replaceable class="parameter">owner</replaceable></></term>
        <listitem>
         <para>
!         Specifies the database user who will own the new database
!         (double-quoted internally).
         </para>
        </listitem>
       </varlistentry>
*************** PostgreSQL documentation
*** 163,169 ****
        <term><option>--template=<replaceable class="parameter">template</replaceable></></term>
        <listitem>
         <para>
!         Specifies the template database from which to build this database.
         </para>
        </listitem>
       </varlistentry>
--- 164,171 ----
        <term><option>--template=<replaceable class="parameter">template</replaceable></></term>
        <listitem>
         <para>
!         Specifies the template database from which to build this
!         database (double-quoted internally).
         </para>
        </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/ref/createlang.sgml b/doc/src/sgml/ref/createlang.sgml
new file mode 100644
index f01f298..7f9459a
*** a/doc/src/sgml/ref/createlang.sgml
--- b/doc/src/sgml/ref/createlang.sgml
*************** PostgreSQL documentation
*** 70,76 ****
        <term><replaceable class="parameter">langname</replaceable></term>
        <listitem>
         <para>
!         Specifies the name of the procedural language to be installed.
         </para>
        </listitem>
       </varlistentry>
--- 70,77 ----
        <term><replaceable class="parameter">langname</replaceable></term>
        <listitem>
         <para>
!         Specifies the name of the procedural language to be
!         installed.  (automatically lower-cased)
         </para>
        </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/ref/droplang.sgml b/doc/src/sgml/ref/droplang.sgml
new file mode 100644
index 04c3a60..fa7e913
*** a/doc/src/sgml/ref/droplang.sgml
--- b/doc/src/sgml/ref/droplang.sgml
*************** PostgreSQL documentation
*** 73,78 ****
--- 73,79 ----
        <listitem>
         <para>
          Specifies the name of the procedural language to be removed.
+         (automatically lower-cased)
         </para>
        </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
new file mode 100644
index 9ae8000..5fd6410
*** a/doc/src/sgml/reference.sgml
--- b/doc/src/sgml/reference.sgml
***************
*** 198,203 ****
--- 198,211 ----
      applications is that they can be run on any host, independent of
      where the database server resides.
     </para>
+ 
+    <para>
+     When specified on the command line, user and databases names have
+     their case preserved &mdash; the presence of spaces or special
+     characters might require quoting.  Table names and other identifiers
+     do not have their case preserved, except where documented, and
+     might require quoting.
+    </para>
    </partintro>
  
     &clusterdb;
diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c
new file mode 100644
index f4c317a..3742091
*** a/src/bin/scripts/clusterdb.c
--- b/src/bin/scripts/clusterdb.c
*************** cluster_one_database(const char *dbname,
*** 177,183 ****
  	if (verbose)
  		appendPQExpBuffer(&sql, " VERBOSE");
  	if (table)
! 		appendPQExpBuffer(&sql, " %s", fmtId(table));
  	appendPQExpBuffer(&sql, ";\n");
  
  	conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
--- 177,183 ----
  	if (verbose)
  		appendPQExpBuffer(&sql, " VERBOSE");
  	if (table)
! 		appendPQExpBuffer(&sql, " %s", table);
  	appendPQExpBuffer(&sql, ";\n");
  
  	conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
diff --git a/src/bin/scripts/createlang.c b/src/bin/scripts/createlang.c
new file mode 100644
index c2153db..2f667e8
*** a/src/bin/scripts/createlang.c
--- b/src/bin/scripts/createlang.c
*************** main(int argc, char *argv[])
*** 164,169 ****
--- 164,170 ----
  		exit(1);
  	}
  
+ 	/* lower case language name */
  	for (p = langname; *p; p++)
  		if (*p >= 'A' && *p <= 'Z')
  			*p += ('a' - 'A');
diff --git a/src/bin/scripts/droplang.c b/src/bin/scripts/droplang.c
new file mode 100644
index 7fadee0..f136a76
*** a/src/bin/scripts/droplang.c
--- b/src/bin/scripts/droplang.c
*************** main(int argc, char *argv[])
*** 165,170 ****
--- 165,171 ----
  		exit(1);
  	}
  
+ 	/* lower case language name */
  	for (p = langname; *p; p++)
  		if (*p >= 'A' && *p <= 'Z')
  			*p += ('a' - 'A');
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
new file mode 100644
index 53fff01..caeed75
*** a/src/bin/scripts/reindexdb.c
--- b/src/bin/scripts/reindexdb.c
*************** reindex_one_database(const char *name, c
*** 223,231 ****
  
  	appendPQExpBuffer(&sql, "REINDEX");
  	if (strcmp(type, "TABLE") == 0)
! 		appendPQExpBuffer(&sql, " TABLE %s", fmtId(name));
  	else if (strcmp(type, "INDEX") == 0)
! 		appendPQExpBuffer(&sql, " INDEX %s", fmtId(name));
  	else if (strcmp(type, "DATABASE") == 0)
  		appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
  	appendPQExpBuffer(&sql, ";\n");
--- 223,231 ----
  
  	appendPQExpBuffer(&sql, "REINDEX");
  	if (strcmp(type, "TABLE") == 0)
! 		appendPQExpBuffer(&sql, " TABLE %s", name);
  	else if (strcmp(type, "INDEX") == 0)
! 		appendPQExpBuffer(&sql, " INDEX %s", name);
  	else if (strcmp(type, "DATABASE") == 0)
  		appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
  	appendPQExpBuffer(&sql, ";\n");

---

From: Tom Lane <[email-masked]>
Date: Wed, 07 Sep 2011 18:07:03 -0400

> references in reindexdb,

Uh, surely that breaks things.  Or did you miss my statement that the
current behavior is what is intended?

> and adds a general documentation overview about

I don't find the documentation changes to be improvements either.
Possibly instead of

> !         Specifies the default tablespace for the database (double-quoted internally).

you could do something like

         Specifies the default tablespace for the database.  (This name
         is not subject to case-folding.)

> +     do not have their case preserved, except where documented, and

This latter sentence is just plain wrong.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 7 Sep 2011 21:37:28 -0400 (EDT)

> current behavior is what is intended?

I saw that, but how is that consistent with other command-line tools? 
What is the logic that has some tools preserve case and others not?

>          is not subject to case-folding.)

OK.

> This latter sentence is just plain wrong.

Really?  Pg_dump doesn't preserve case for table names:

	pg_dump -t Test test

	$ pg_dump -t Test test
	pg_dump: No matching tables were found
	$ pg_dump -t '"Test"' test
	--
	-- PostgreSQL database dump
	...

and vacuumdb certainly does not:

	vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy

	$ vacuumdb --analyze --verbose --table 'Test (x)' test
	vacuumdb: vacuuming of table "Test (x)" in database "test" failed: ERROR:  relation "test" does not exist
	$ vacuumdb --analyze --verbose --table '"Test" (x)' test
	INFO:  vacuuming "public.Test"
	INFO:  "Test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
	...

Who does?  reindexdb and clusterdb did before my patch, but that hardly
seems consistent.

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 8 Sep 2011 09:14:26 -0400 (EDT)

>          is not subject to case-folding.)

Actually, that is inaccurate.  When we call PQconnectdbParams() with
username/database names, or call fmtId(), we are doing more than
preserving case.  We are also preserving whitespace and special
characters.  I can make the new text:

	(This name is processed as a double-quoted identifier.)

---

From: Tom Lane <[email-masked]>
Date: Thu, 08 Sep 2011 10:40:31 -0400

> What is the logic that has some tools preserve case and others not?

If there are any that fail to preserve case, those are the ones to be
fixed.  Making the working ones match the broken ones is not the correct
direction.

> 	pg_dump -t Test test

pg_dump is a special case because the arguments are patterns to match,
not fixed names.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 8 Sep 2011 10:52:04 -0400 (EDT)

> fixed.  Making the working ones match the broken ones is not the correct

I started going in that direction and stopped because of this example
in our docs:

Ideas?

OK.

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 08 Sep 2011 12:11:50 -0300

>         vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy

Maybe do it unless there are parens.

-- 
Álvaro Herrera <[email-masked]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 8 Sep 2011 12:24:09 -0400 (EDT)

> Maybe do it unless there are parens.

OK, let me give that a try.

---

From: Tom Lane <[email-masked]>
Date: Thu, 08 Sep 2011 14:37:44 -0400

> OK, let me give that a try.

I've been thinking more about this, and realized that there is one
killer argument in favor of not auto-quoting, at least for arguments
that represent table names: if we quote, there is no way to specify
a schema-qualified name.

I'm too lazy to troll the archives to verify this, but I am moderately
sure that this point never came up in the original discussions where
we concluded that auto-quoting command-line arguments was a good policy,
because that was before we'd implemented schemas.

So what I'm thinking right now is that we indeed should not auto-quote
table- or index-name arguments.  However, usernames and database names
taken from the command line are still going to be auto-quoted, because
that's how things work in connection requests.  Is it going to be
confusing that some things are quoted and some not?

(Of course, any such change would be non-backwards-compatible and would
have to be suitably documented and release-noted.)

The backwards-compatible alternative would be to invent additional
schema option arguments so that you could write something like
"reindexdb --schema foo --table bar", but that seems pretty darn ugly.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 9 Sep 2011 19:48:22 -0400 (EDT)

> "reindexdb --schema foo --table bar", but that seems pretty darn ugly.

I have implemented what you suggested above, with improved wording.
Patch attached.

index 3e50173..1516f33
--- 90,97 ----
!         Specifies the default tablespace for the database. (This name
!         is processed as a double-quoted identifier.)
*** 154,159 ****
--- 155,161 ----
          Specifies the database user who will own the new database.
+         (This name is processed as a double-quoted identifier.)
--- 165,172 ----
!         database.  (This name is processed as a double-quoted identifier.)
index f01f298..d28cfb7
!         installed.  (This name is lower-cased.)
index 04c3a60..e5d02aa
+         (This name is lower-cased.)
  
     &clusterdb;

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 10 Sep 2011 16:39:12 -0400 (EDT)

Patch applied.

---------------------------------------------------------------------------

> I have implemented what you suggested above, with improved wording.

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

> 
> -- 
> Sent via pgsql-docs mailing list ([email-masked])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 12 Sep 2011 22:18:59 +0300

> !         is processed as a double-quoted identifier.)

I don't find this wording particularly useful.  What does it mean?  The
imaginary double quotes are stripped off?

The advantage of using the shell tools is that you are not affected by
the oddities of SQL syntax, so don't introduce references to SQL syntax
where it doesn't need to be.  Just say, the names are used as is.  Or
just say nothing, because nothing happens.

> !         installed.  (This name is lower-cased.)

Maybe an explanation why would be in order here.

> +     do not have their case preserved, except where documented, and

What kind of quoting might be required?

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 12 Sep 2011 22:53:15 -0400 (EDT)

> imaginary double quotes are stripped off?

My original wording said that the identifier is processed with
double-quotes internally.  Is that better?

> just say nothing, because nothing happens.

We have to say something because the section description says only
user/db names are double-quoted, execept where documented.

> Maybe an explanation why would be in order here.

I frankly have no idea why.

> What kind of quoting might be required?

Double or single-quoting, and I don't know about Windows, except I know
is supports double-quoting.  Do we really want to get into that?