Thread: Re: [HACKERS] Updated TODO item
On Fri, 4 Jan 2002, Kaare Rasmussen wrote: > > * Make it easier to create a database owned by someone who can't createdb, > > perhaps CREATE DATABASE dbname WITH USER = "user" > > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > Shouldn't that be > > CREATE DATABASE dbname WITH OWNER = "user" > > ? > A much better idea. There is no conflict in using OWNER here. Revised patch attached. Gavin
Attachment
Saved for 7.3. --------------------------------------------------------------------------- Gavin Sherry wrote: > On Fri, 4 Jan 2002, Kaare Rasmussen wrote: > > > > * Make it easier to create a database owned by someone who can't createdb, > > > perhaps CREATE DATABASE dbname WITH USER = "user" > > > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > > > Shouldn't that be > > > > CREATE DATABASE dbname WITH OWNER = "user" > > > > ? > > > > A much better idea. There is no conflict in using OWNER here. > > Revised patch attached. > > Gavin Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> > > * Make it easier to create a database owned by someone who can't createdb,
> > > perhaps CREATE DATABASE dbname WITH USER = "user"
> > CREATE DATABASE dbname WITH OWNER = "user"
> A much better idea. There is no conflict in using OWNER here.
Does this have the multiple "WITH xxx" clauses which were discussed
earlier? That is a nonstarter for syntax. There are other places in the
grammar having "with clauses" and multiple arguments or subclauses, and
having the shift/reduce issues resolved...
- Thomas
Thomas Lockhart wrote:
> > > > * Make it easier to create a database owned by someone who can't createdb,
> > > > perhaps CREATE DATABASE dbname WITH USER = "user"
> > > CREATE DATABASE dbname WITH OWNER = "user"
> > A much better idea. There is no conflict in using OWNER here.
>
> Does this have the multiple "WITH xxx" clauses which were discussed
> earlier? That is a nonstarter for syntax. There are other places in the
> grammar having "with clauses" and multiple arguments or subclauses, and
> having the shift/reduce issues resolved...
Not sure. Patch is at:
http://candle.pha.pa.us/cgi-bin/pgpatches2
Are you asking if it has "WITH ARG val, ARG val" or "WITH ARG val WITH
ARG val?"
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> I might be thicker than a whale sandwich (10 points if you can pick the
> quote :) ), but can someone please tell me what a shift/reduce issue is,
> exactly...
It is what you will come to know and love if you get involved with
grammars written in yacc. yacc (and some related parsers) look ahead one
token to decide what parsing path to take. So if it takes more than one
token to figure that out, you will get a shift/reduce or reduce/reduce
error, and the parser will end up chosing *one* of the possibilities
every time.
You can make these errors go away by restructuring the language or by
restructuring the grammar specification to allow multiple "threads" of
parsing to be carried forward until possible conflicts are resolved. We
use every and all technique to shoehorn SQL and extensions into a
yacc/bison tool ;)
- Thomas
> Does this have the multiple "WITH xxx" clauses which were discussed > earlier? That is a nonstarter for syntax. There are other places in the > grammar having "with clauses" and multiple arguments or subclauses, and > having the shift/reduce issues resolved... I might be thicker than a whale sandwich (10 points if you can pick the quote :) ), but can someone please tell me what a shift/reduce issue is, exactly... Thanks, Chris
On Tue, 8 Jan 2002, Thomas Lockhart wrote:
> > > > * Make it easier to create a database owned by someone who can't createdb,
> > > > perhaps CREATE DATABASE dbname WITH USER = "user"
> > > CREATE DATABASE dbname WITH OWNER = "user"
> > A much better idea. There is no conflict in using OWNER here.
>
> Does this have the multiple "WITH xxx" clauses which were discussed
> earlier? That is a nonstarter for syntax. There are other places in the
When was it discussed so that I can have a read? I cannot recall it.
And yes, it is not pleasant to implement. Luckily, the design of the
CREATE DATABASE rule had already incorporated the possibility of
...
WITH LOCATION = ...
WITH TEMPLAETE = ...
etc.
I'm not sure, however, if this is really what you were asking.
Gavin
On Tue, 8 Jan 2002, Christopher Kings-Lynne wrote:
> > Does this have the multiple "WITH xxx" clauses which were discussed
> > earlier? That is a nonstarter for syntax. There are other places in the
> > grammar having "with clauses" and multiple arguments or subclauses, and
> > having the shift/reduce issues resolved...
>
> I might be thicker than a whale sandwich (10 points if you can pick the
> quote :) ), but can someone please tell me what a shift/reduce issue is,
> exactly...
>
A Yacc parser does two things in order to parse input:
1) Reduce: attempt to reduce the stack by simplifying it to a rule
2) Shift: obtain the next token from input so that a reduction may be able
to take.
Shift/reduce conflicts are pretty ugly. Basically, what happens is that
the parser finds itself in a state where it is valid to reduce OR shift at
some point in the grammar. What I believe Thomas was refering to was this
condition:
Take a rule:
rule a: CREATE DATABASE <name> WITH LOCATION = <name>
rule b: CREATE DATABASE <name> WITH LOCATION = <name> WITH OWNER = <name>
now if the input is:
CREATE DATABASE test WITH LOCATION = '/var/test' WITH OWNER = swm
^
Then the parser can reach the point under-marked by the circumflex and
find it valid to reduce the stack (CREATE DATABASE test WITH LOCATION =
'/var/test') to rule a OR shift and put the WITH (after the circumflex) on
the stack given that this should match rule b.
Naturally, if this conflict is ignored for the grammar above, you could
end up with wild results in your parsed node tree. Realistically, Bison
and other yacc compilers will generate parsers unaffected for this
situation because they always opt to shift when there is a shift/reduce
conflict -- a pretty safe bet. But if it should have been valid to reduce
the input to a once it reached the circumflex, you'd be in trouble.
Gavin
> > > Does this have the multiple "WITH xxx" clauses which were discussed
> > > earlier? That is a nonstarter for syntax. There are other places in the
> > > grammar having "with clauses" and multiple arguments or subclauses, and
> > > having the shift/reduce issues resolved...
...
> CREATE DATABASE <name> WITH LOCATION = <name> WITH OWNER = <name>
It was this syntax I was wondering about. Multiple "WITH"s should not be
necessary. Are they actually required in the patch?
- Thomas
On Tue, 8 Jan 2002, Thomas Lockhart wrote: > > > > Does this have the multiple "WITH xxx" clauses which were discussed > > > > earlier? That is a nonstarter for syntax. There are other places in the > > > > grammar having "with clauses" and multiple arguments or subclauses, and > > > > having the shift/reduce issues resolved... > ... > > CREATE DATABASE <name> WITH LOCATION = <name> WITH OWNER = <name> > > It was this syntax I was wondering about. Multiple "WITH"s should not be > necessary. Are they actually required in the patch? Argh. My bad. The syntax is what you had in mind: CREATE DATABASE <name> [WITH [LOCATION <name>] [OWNER <name>] ...] Gavin
Gavin Sherry <swm@linuxworld.com.au> writes:
> On Fri, 4 Jan 2002, Kaare Rasmussen wrote:
>> CREATE DATABASE dbname WITH OWNER = "user"
> A much better idea. There is no conflict in using OWNER here.
> Revised patch attached.
I have applied this patch, with a couple of editorial tweaks, and one
not-so-minor change: superuser privilege is required to create a
database on behalf of another user. Seems to me that CREATEDB
privilege should not be sufficient to allow such an operation.
Still to do: teach createdb script about it, and revise pg_dumpall
to use the facility instead of assuming that database owners have
CREATEDB privilege. Working on those now ...
regards, tom lane
Thomas Lockhart wrote: > > > > > * Make it easier to create a database owned by someone who can't createdb, > > > > perhaps CREATE DATABASE dbname WITH USER = "user" > > > CREATE DATABASE dbname WITH OWNER = "user" > > A much better idea. There is no conflict in using OWNER here. > > Does this have the multiple "WITH xxx" clauses which were discussed > earlier? That is a nonstarter for syntax. There are other places in the > grammar having "with clauses" and multiple arguments or subclauses, and > having the shift/reduce issues resolved... > The syntax of the CREATE SCHEMA SQL standard command is CREATE SCHEMA AUTHORIZATION userid Shouldn't we be using CREATE DATABASE AUTHORIZATION userid to be consistent? -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser <fnasser@redhat.com> writes:
> The syntax of the CREATE SCHEMA SQL standard command is
> CREATE SCHEMA AUTHORIZATION userid
> Shouldn't we be using
> CREATE DATABASE AUTHORIZATION userid
> to be consistent?
Seems like a very weak analogy; there's no other similarities between
the two command syntaxes, so why argue that this should be the same?
Also, the semantics aren't the same --- for example, there's no a-priori
assumption that a database owner owns everything within the database.
regards, tom lane
Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > On Fri, 4 Jan 2002, Kaare Rasmussen wrote: > >> CREATE DATABASE dbname WITH OWNER = "user" > > > A much better idea. There is no conflict in using OWNER here. > > Revised patch attached. > > I have applied this patch, with a couple of editorial tweaks, and one > not-so-minor change: superuser privilege is required to create a > database on behalf of another user. Seems to me that CREATEDB > privilege should not be sufficient to allow such an operation. > > Still to do: teach createdb script about it, and revise pg_dumpall > to use the facility instead of assuming that database owners have > CREATEDB privilege. Working on those now ... Seems you are already on that too. I will wait for you to finish, when there will be nothing left for me to do. :-( -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote: > Tom Lane wrote: > > Gavin Sherry <swm@linuxworld.com.au> writes: > > > On Fri, 4 Jan 2002, Kaare Rasmussen wrote: > > >> CREATE DATABASE dbname WITH OWNER = "user" > > > > > A much better idea. There is no conflict in using OWNER here. > > > Revised patch attached. > > > > I have applied this patch, with a couple of editorial tweaks, and one > > not-so-minor change: superuser privilege is required to create a > > database on behalf of another user. Seems to me that CREATEDB > > privilege should not be sufficient to allow such an operation. > > > > Still to do: teach createdb script about it, and revise pg_dumpall > > to use the facility instead of assuming that database owners have > > CREATEDB privilege. Working on those now ... > > Seems you are already on that too. I will wait for you to finish, when > there will be nothing left for me to do. :-( I have applied this minor patch. I don't want to document the ability to use equals in this case because some day we may remove it. The equals doesn't fit with any of our other WITH clauses. I cleaned up a paragraph on OWNER, and mentioned we may want to remove the equals backward compatibility hack someday. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 Index: doc/src/sgml/ref/create_database.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v retrieving revision 1.24 diff -c -r1.24 create_database.sgml *** doc/src/sgml/ref/create_database.sgml 24 Feb 2002 20:20:18 -0000 1.24 --- doc/src/sgml/ref/create_database.sgml 25 Feb 2002 02:48:15 -0000 *************** *** 24,33 **** </refsynopsisdivinfo> <synopsis> CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> ! [ WITH [ OWNER [ = ] <replaceable class="parameter">dbowner</replaceable> ] ! [ LOCATION [ = ] '<replaceable class="parameter">dbpath</replaceable>' ] ! [ TEMPLATE [ = ] <replaceable class="parameter">template</replaceable> ] ! [ ENCODING [ = ] <replaceable class="parameter">encoding</replaceable> ] ] </synopsis> <refsect2 id="R2-SQL-CREATEDATABASE-1"> --- 24,33 ---- </refsynopsisdivinfo> <synopsis> CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> ! [ WITH [ OWNER <replaceable class="parameter">dbowner</replaceable> ] ! [ LOCATION '<replaceable class="parameter">dbpath</replaceable>' ] ! [ TEMPLATE <replaceable class="parameter">template</replaceable> ] ! [ ENCODING <replaceable class="parameter">encoding</replaceable> ] ] </synopsis> <refsect2 id="R2-SQL-CREATEDATABASE-1"> *************** *** 186,196 **** <para> Normally, the creator becomes the owner of the new database. ! A different owner may be specified by using the <option>OWNER</> ! clause (but only superusers may create databases on behalf of other users). ! To create a database owned by oneself, either superuser privilege ! or CREATEDB privilege is required. A superuser may create a database ! for another user, even if that user has no special privileges himself. </para> <para> --- 186,195 ---- <para> Normally, the creator becomes the owner of the new database. ! Superusers can create databases owned by other users using the ! <option>OWNER</> clause. They can even create databases owned by ! users with no special privileges. Non-superusers with CREATEDB ! privilege can only create databases owned by themselves. </para> <para> Index: src/backend/parser/gram.y =================================================================== RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v retrieving revision 2.279 diff -c -r2.279 gram.y *** src/backend/parser/gram.y 24 Feb 2002 20:20:20 -0000 2.279 --- src/backend/parser/gram.y 25 Feb 2002 02:48:26 -0000 *************** *** 3155,3160 **** --- 3155,3164 ---- } ; + /* + * Optional equals is here only for backward compatibility. + * Should be removed someday. bjm 2002-02-24 + */ opt_equal: '=' { $$ = TRUE; } | /*EMPTY*/ { $$ = FALSE; } ;
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have applied this minor patch. I don't want to document the ability
> to use equals in this case because some day we may remove it. The
> equals doesn't fit with any of our other WITH clauses.
One could argue at least as plausibly that we should allow optional '='
in all the other WITH clauses. (That thought was why I renamed the
nonterminal to not refer to createdb.)
I'm also quite unimpressed with the notion of trying to suppress
knowledge of a syntax that is in active use by pg_dump, and perhaps
other tools too.
regards, tom lane
Bruce Momjian writes: > I have applied this minor patch. I don't want to document the ability > to use equals in this case because some day we may remove it. Document what the code does, not what you think it may do in the future. Users that are used to the current syntax may get surprised when they see the equal signs gone. > The equals doesn't fit with any of our other WITH clauses. And the WITH keyword doesn't fit any other SQL command. So when we make up new commands in the future, please drop it. -- Peter Eisentraut peter_e@gmx.net
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have applied this minor patch. I don't want to document the ability
> > to use equals in this case because some day we may remove it. The
> > equals doesn't fit with any of our other WITH clauses.
>
> One could argue at least as plausibly that we should allow optional '='
> in all the other WITH clauses. (That thought was why I renamed the
> nonterminal to not refer to createdb.)
Well we have on TODO:
* Make equals sign optional in CREATE DATABASE WITH param = 'val'
> I'm also quite unimpressed with the notion of trying to suppress
> knowledge of a syntax that is in active use by pg_dump, and perhaps
> other tools too.
Well, my assumption is that we don't want to document it because we want
to discourage its use, unless we want to add equals to all the WITH
clauses, which I didn't think we wanted to do.
There are other cases of syntax we don't document because it makes
little sense, and I thought this was one of them.
You have a good point with pg_dump. Can I remove the use of the equals
in there? Seems safe to me. However, it does prevent us from loading
newer pgdumps into older database, which seems like a pain.
Wow, this is tricky. I guess it is not worth fixing this to make it
consistent. I will put back the [=] and remove the comment unless
someone else has a better idea.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Sun, 2002-02-24 at 22:34, Bruce Momjian wrote: > You have a good point with pg_dump. Can I remove the use of the equals > in there? Seems safe to me. However, it does prevent us from loading > newer pgdumps into older database, which seems like a pain. AFAIK, no effort is made to ensure that dump files produced by a current version of pg_dump will restore properly into older databases. From what I can tell, the odds of this happening successfully are slim to none... Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Neil Conway <nconway@klamath.dyndns.org> writes:
> AFAIK, no effort is made to ensure that dump files produced by a current
> version of pg_dump will restore properly into older databases.
No, but gratuitous breakage should be avoided --- especially when it's
in pursuit of a goal that no one except Bruce subscribes to in the
first place ...
regards, tom lane
Tom Lane wrote: > > Fernando Nasser <fnasser@redhat.com> writes: > > The syntax of the CREATE SCHEMA SQL standard command is > > CREATE SCHEMA AUTHORIZATION userid > > Shouldn't we be using > > CREATE DATABASE AUTHORIZATION userid > > to be consistent? > > Seems like a very weak analogy; there's no other similarities between > the two command syntaxes, so why argue that this should be the same? The analogy is not with the command -- it is with with the token 'userid'. The key word prefix tells what that token is supposed to be, and that is an <authorization-id>. THe key word AUTHORIZATION works like a sort of an 'adjective'. > Also, the semantics aren't the same --- for example, there's no a-priori > assumption that a database owner owns everything within the database. > I thought you were arguing that neither would a schema (i.e., you wanted objects in a schema to have different owners). Anyway, that is not the point here. We have two commands that create groups of database objects (our "database" is the SQL catalog) and both specify who will own it. The CREATE DATABASE is implementation defined and we can do whatever we want with it, but as we have a standard command that uses a syntax to specify the owner I think we should follow it. With the additional advantage that the '=' problem goes away and we avoid future shift/reduce problems in the parser as 'WITH' is already too overloaded. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9