Thread: Re: Bug#21681: postgresql: Doesn't allow granting to www-data

John Goerzen wrote:

  >>                 Were you able to create a user `www-data'?
  >> I get:
  >
  >I believe that the createuser script did it for me.

Yes, it does.  Very inconsistent!

  >> I agree that there is a bug, but it is that the error message is wrong!
  >
  >If grant would permit the username to be quoted in ' characters, then
  >the problem ought to go away, I think.

Currently, it appears that the authors don't want to change, so it is
necessary to specify a postgres user-id when connecting.

However there is, as you say in another mail, no convenient way of
doing that automatically.  We need an environment variable or a
command-line option to specify the user and (optionally) password.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver

PGP key from public servers; key ID 32B8FAA1

                 ========================================
    Come to me, all you who labour and are heavily laden, and I will
    give you rest. Take my yoke upon you, and learn from me; for I am
    meek and lowly in heart, and you shall find rest for your souls.
    For my yoke is easy and my burden is light.    (Matthew 11: 28-30)

> >> Were you able to create a user `www-data'?
> >I believe that the createuser script did it for me.
> Yes, it does.  Very inconsistent!
> >> I agree that there is a bug, but it is that the error message is
> >> wrong!
> >If grant would permit the username to be quoted in ' characters, then
> >the problem ought to go away, I think.
>
> Currently, it appears that the authors don't want to change, so it is
> necessary to specify a postgres user-id when connecting.

"Don't want to change"? Probably not. We're trying to figure out how to
cope with an ever-increasing number of interested users _and_
developers, and don't always react quickly to good suggestions.

The topic just came up recently, as I recall, and your suggestions are
good. Do you really want the patch applied which disables the more
general user names, or do you want to move more slowly and try to get
full user names in v6.4 (we have several months to get this right; in
fact we may already have them; see below :)

> However there is, as you say in another mail, no convenient way of
> doing that automatically.  We need an environment variable or a
> command-line option to specify the user and (optionally) password.

Sorry, I didn't follow the whole discussion. Is the problem only with
explicit CREATE USER and GRANT commands in SQL, or are there other
interfaces which would show problems too (you mention command-line
options above, but I don't know to what).

Oh, I just tried something:

tgl=> create user "hi-there";
CREATE USER
tgl=> select usename from pg_user;
usename
--------
postgres
tgl
hi-there
(3 rows)

Isn't this what you want?? I haven't figured out how to get GRANT to
work, but it seems to swallow the double-quoted user name as it
should...

                        - Tom

btw, how is it going with the docs conversion, Oliver? I'd expect that
it would keep you out of trouble for a little while at least :)

"Thomas G. Lockhart" wrote:
  >"Don't want to change"? Probably not. We're trying to figure out how to
  >cope with an ever-increasing number of interested users _and_
  >developers, and don't always react quickly to good suggestions.

Don't take it personally!  I'm happy to accept the developers' decisions,
since you know the code much better.

  >The topic just came up recently, as I recall, and your suggestions are
  >good. Do you really want the patch applied which disables the more
  >general user names,

I would prefer to have Unix user names allowed throughout.  However, if
the developers decide not to do this, the patch to createuser is
required to maintain consistency.  From Bruce's original reply,  I had
thought that was the position.

  >                   or do you want to move more slowly and try to get
  >full user names in v6.4 (we have several months to get this right; in
  >fact we may already have them; see below :)

By all means, lets have them!

  >> However there is, as you say in another mail, no convenient way of
  >> doing that automatically.  We need an environment variable or a
  >> command-line option to specify the user and (optionally) password.
  >
  >Sorry, I didn't follow the whole discussion. Is the problem only with
  >explicit CREATE USER and GRANT commands in SQL, or are there other
  >interfaces which would show problems too (you mention command-line
  >options above, but I don't know to what).

Sorry; that's what comes of running a three-way discussion.  The problem is
that you can't (I think) start a connection while supplying another
user-name than your login-name, except by the -u option to psql.  This
leads to an interactive prompt for name and password.  This is not
convenient for CGI scripts on web-servers (which is how the original
problem manifested itself.)  It seems to be desirable to be able to
specify the postgres user name while starting the connection.

  >Oh, I just tried something:
  >
  >tgl=> create user "hi-there";
  >CREATE USER

  >Isn't this what you want?? I haven't figured out how to get GRANT to
  >work, but it seems to swallow the double-quoted user name as it
  >should...

Yes it is; I hadn't tried double-quotes, because single-quotes are used
for strings - it didn't occur to me! (Incidentally, WHY double-quotes here
instead of single-quotes? Surely that's against SQL practice?) It doesn't
work for GRANT, though, with either kind of quote:

  bray->  grant all on address to www-data;
  ERROR:  aclparse: non-existent user "www"
  bray=> grant all on address to "www-data";
  ERROR:  aclparse: mode flags must use "arwR"
  bray=> grant all on address to 'www-data';
  ERROR:  parser: parse error at or near "'"


Overall, it seems to me that a user-name is just a string, that is used
as a key into pg_shadow.  The SQL92 definition allows it to be a
character string literal.  So there ought to be no problem in specifying
a string rather than an identifier in all the relevant places.
(I speak in happy ignorance of whatever the real problems may be!)


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver

PGP key from public servers; key ID 32B8FAA1

                 ========================================
    Come to me, all you who labour and are heavily laden, and I will
    give you rest. Take my yoke upon you, and learn from me; for I am
    meek and lowly in heart, and you shall find rest for your souls.
    For my yoke is easy and my burden is light.    (Matthew 11: 28-30)

> Don't take it personally!  I'm happy to accept the developers'
> decisions, since you know the code much better.

No offense taken. It's just that you got an opinion, not a "decision".
When you are bouncing ideas off of the list, you might get an opinion
back, but if you really think it should be pursued then it's OK to push
the topic to see if it resonates with someone. For something big, it
does usually take a consensus among the core developers to get it
incorporated, but on smaller issues which fit in with how things already
are it isn't that complicated.

btw, we try to not be too thin-skinned; sometimes even polite people
need to be blunt to get their point across when we're being dense :)

>   >Do you really want the patch applied which disables the more
>   >general user names,
> I would prefer to have Unix user names allowed throughout.

Not exactly possible with SQL92 grammar; see below...

> (What started this thread?) The problem is
> that you can't (I think) start a connection while supplying another
> user-name than your login-name, except by the -u option to psql.  This
> leads to an interactive prompt for name and password.

This sounds like a feature to add to libpq and/or psql...

>   >tgl=> create user "hi-there";
>   >CREATE USER
> I hadn't tried double-quotes, because single-quotes are
> used for strings - it didn't occur to me! (Incidentally, WHY
> double-quotes here instead of single-quotes? Surely that's against SQL
> practice?)

I don't think so; syntactically an authorization ID seems to resemble
other identifiers, not literal strings. Identifiers are allowed to be
"delimited", when means surrounded by double-quotes, to allow mixed case
and weird characters into an identifier.

> It doesn't work for GRANT, though, with either kind of quote:

Yeah, well, that is probably a smaller problem. Perhaps there is a query
buried in the backend which needs to have some double-quoting applied.
We can look into it, eh?

> Overall, it seems to me that a user-name is just a string, that is
> used as a key into pg_shadow.  The SQL92 definition allows it to be a
> character string literal.

I don't see this in my reference books. A character string literal is
delimited by single-quotes; never run across it for a user name
(actually an "authorization ID" in SQL92).

> So there ought to be no problem in specifying
> a string rather than an identifier in all the relevant places.
> (I speak in happy ignorance of whatever the real problems may be!)

Yup :)

The SQL92 character set specifically allows only a few characters other
than [A-Za-z0-9] in non-delimited identifiers. And, it specifically
defines most other interesting characters (including "-") as explicit
delimiters.

I think we should concentrate on making the features work as well as
possible within the SQL92 framework, and within the limitations of our
lex/yacc grammar.

Delimited identifiers are afaik the way to do this...

                         - Tom

More info on the GRANT problem with user names containing a minus sign:

It turns out that "+-=" are used inside an ACL string constructed
internally in the backend. So, putting one of those characters into the
user name causes what follows to be misinterpreted.

 * aclparse
 *      Consumes and parses an ACL specification of the form:
 *              [group|user] [A-Za-z0-9]*[+-=][rwaR]*

I think that we would need to restructure this internal information to
make the user name field unambiguous no matter its contents.

Bruce, can you put this on the ToDo list? In the meantime I would
suggest _not_ restricting the allowable user names elsewhere, since this
is a bug fix kind of thing...

                     - Tom

>
> More info on the GRANT problem with user names containing a minus sign:
>
> It turns out that "+-=" are used inside an ACL string constructed
> internally in the backend. So, putting one of those characters into the
> user name causes what follows to be misinterpreted.
>
>  * aclparse
>  *      Consumes and parses an ACL specification of the form:
>  *              [group|user] [A-Za-z0-9]*[+-=][rwaR]*
>
> I think that we would need to restructure this internal information to
> make the user name field unambiguous no matter its contents.
>
> Bruce, can you put this on the ToDo list? In the meantime I would
> suggest _not_ restricting the allowable user names elsewhere, since this
> is a bug fix kind of thing...

Added to TODO:

* Restructure storing of GRANT permission information to allow +-=


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

To bring everything back to base here...In one of the posts CC'd to
me, somebody wondered how this mess got started, so let me recap...

On Debian, a webserver runs under the user www-data.  I had created a
www-data user for PostgreSQL using createuser, so that CGI scripts
would be able to access the database (using ident authentication).
However, the newer Postgres releases default to not granting select to
public (wise), so it did not work on the first try.  I looked into it,
and remembered -- Ah ha! -- that I needed to grant all (in this case)
to www-data.  However, psql wouldn't parse my grant command due to the
hyphen in the www-data username.  I had no problem creating the user
(although it appears now that the SQL way of creating the user may be
broken/strange).  I also had no problem getting the CGI to connect to
the database once www-data was able to access the tables.  (I was able
to use a temporary workaround of granting all to public on the
specific tables, since nobody else happens to have a user account with
this postgres installation).

The problem, then, was the GRANT command for me.  I reported this
using Debian's bug-tracking system as per usual procedure, and Oliver
sent it on up to y'all as per usual procedure.

"Oliver Elphick" <olly@lfix.co.uk> writes:

> However there is, as you say in another mail, no convenient way of
> doing that automatically.  We need an environment variable or a
> command-line option to specify the user and (optionally) password.

ident will work fine for the CGI.  It is just that I need to be able
to grant access to the ident'd user :-)

And finally, I hope you excuse this paragraph in the Debian logs
Oliver... :-)

FYI, while the -hackers are getting CCd anyway, I ought to mention
here how amazingly cool I think Postgres is.  At work, I am currently
writing a multiplatform Perl/Tk application using DBI::Pg as the
database driver, and Postgres running on BSDi.  (One of my jobs is at
an ISP, where BSDi is in heavy use.)  Besides being free (yay!  it's a
smallish ISP), it also is the only one that runs natively on BSDi
(this is the BIG win), excepting the mSQLs of the world that we would
rather ignore :-)  Another person there is investigating moving all of
the billing information over from flatfile ASCII databases to a
Postgres database, and I will be working with moving usage accounting
software from flatfile databases to Postgres, as well as my current
call tracking project.  Nobody has officially said that we will stick
with Postgres, but I think it is likely.  Good job guys!  (At home, I
do the relatively simple task of indexing my videotape collection with
a couple of tables with no more than 700 rows each, compared with
15000/table with 10 tables at work <g>)

--
John Goerzen        Linux, Unix programming           jgoerzen@complete.org |
Developer, Debian GNU/Linux (Free powerful OS upgrade)       www.debian.org |
----------------------------------------------------------------------------+
``You'll notice that this scanner, Bill [Gates]...''  <Blue Screen of Death>
``Whoa!''  <Applause>   ``Moving right along....''  -- Microsoft (Comdex
      video at: http://cnn.com/TECH/computing/9804/20/gates.comdex/index.html