Обсуждение: Valid characters for user/role/group names?

In my continuing quest for multi-tenant ways I'm trying to come up with
a method to name roles, users, and groups that will not clash across the
cluster.  The plans are to have one database per tenant and place
applications in different schema in those databases.  This is working
fine so far but I'm now trying to come up with a naming scheme that will
allow a dba  account for tenant_1, tenant_2, etc.
    I found, several months ago, a posting about using the at "@" symbol
in a role name so there could be a dba@tenant_1, dba@tenant_2, etc.  I
can't find the article again but I remember there was a reference to
possible issues with using the '@' and username entries in pg_hba.conf
    If the '@' isn't a problem (other than possibly not being valid
according to the SQL standard I was going to use an '@tenant_1.txt'
entry in pg_hba.conf and place dba@tenant_1, bob@tenant_1,
sally@tenant_1 in it.

But according to

http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html

"SQL identifiers and key words must begin with a letter (a-z, but also
letters with diacritical marks and non-Latin letters) or an underscore
(_). Subsequent characters in an identifier or key word can be letters,
underscores, digits (0-9), or dollar signs ($). Note that dollar signs
are not allowed in identifiers according to the letter of the SQL
standard, so their use might render applications less portable. ... "

I'm wondering what the reference to "non-Latin" letters means.

Anyone have a solution to this?


Thanks,
Rod
--

On Wed, Feb 25, 2009 at 08:50:15AM -0800, Roderick A. Anderson wrote:
> http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html
>
> "SQL identifiers and key words must begin with a letter (a-z, but also
> letters with diacritical marks and non-Latin letters) or an underscore
> (_). Subsequent characters in an identifier or key word can be letters,
> underscores, digits (0-9), or dollar signs ($). Note that dollar signs
> are not allowed in identifiers according to the letter of the SQL
> standard, so their use might render applications less portable. ... "

You'd want to put double quotes around the identifier.  Try searching
for "quoted identifier" in the above page.

--
  Sam  http://samason.me.uk/

Sam Mason <sam@samason.me.uk> writes:
> You'd want to put double quotes around the identifier.  Try searching
> for "quoted identifier" in the above page.

Note that the double quotes would be needed when referencing the role
identifier in SQL commands (eg CREATE ROLE).  In other contexts, such
as pg_hba.conf or psql's command-line -U switch, the rules might well
be different; you might not need quotes, or you might need some other
kind of quoting.  I'd suggest a bit of experimenting before you
settle on a grand plan.

            regards, tom lane

Roderick A. Anderson wrote:
> In my continuing quest for multi-tenant ways I'm trying to come up with
> a method to name roles, users, and groups that will not clash across the
> cluster.

<snip />

While testing and researching I discovered prior work.  I was using
different terminology.

    http://wiki.postgresql.org/wiki/Shared_Database_Hosting

Well my need go a little deeper but they were touched on.

There is also the thread from last August.

    http://archives.postgresql.org/pgsql-admin/2008-08/msg00049.php

My thanks to Sam Mason and Tom Lane for the ideas and suggestions.  I'm
still working through where quoting is needed and not when using pgAdmin
III and psql.


Rod
--