Thread: What's the CURRENT schema ?
Hi, I'm playing with the new schema functionality. I login to a database as a user yamada. There I created 2 schemas yamada and inoue. By accident I made 2 tables with the same name vs1 in both public and yamada schemas. I can see the content of yamada.vs1 by the command select * from vs1 but there seems to be no way to see the content of public.vs1. If I drop the table yamada.vs1, I can see the content of public.vs1 by the command select * from vs1. Well there seems to be no concept of the CURRENT schema. Is it intended ? regards, Hiroshi Inoue
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I can see the content of yamada.vs1 by the command
> select * from vs1
> but there seems to be no way to see the content of
> public.vs1.
PUBLIC is a reserved keyword, so you have to do something likeselect * from "public".vs1;
if there is a vs1 hiding it in an earlier namespace in the search
path.
I've been vacillating about whether to choose another name for the
public namespace to avoid the need for quotes here. I can't think
of another good name :-(
regards, tom lane
Tom Lane wrote: > PUBLIC is a reserved keyword, so you have to do something like > select * from "public".vs1; > if there is a vs1 hiding it in an earlier namespace in the search > path. > > I've been vacillating about whether to choose another name for the > public namespace to avoid the need for quotes here. I can't think > of another good name :-( > What about shared.vs1 or common.vs1? Joe
Tom Lane wrote: > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > I can see the content of yamada.vs1 by the command > > select * from vs1 > > but there seems to be no way to see the content of > > public.vs1. > > PUBLIC is a reserved keyword, so you have to do something like > select * from "public".vs1; > if there is a vs1 hiding it in an earlier namespace in the search > path. I see. However my main problem is that the schema of unqualified vs1 is affected by the existence of yamada.vs1. I don't think it's a useful behavior. regards, Hiroshi Inoue
Tom Lane writes:
> PUBLIC is a reserved keyword, so you have to do something like
> select * from "public".vs1;
> if there is a vs1 hiding it in an earlier namespace in the search
> path.
PUBLIC can be made less reserved easily. See patch below.
> I've been vacillating about whether to choose another name for the
> public namespace to avoid the need for quotes here. I can't think
> of another good name :-(
PUBLIC is a good name. Oracle uses it, I think.
diff -u -r2.299 gram.y
--- gram.y 1 Apr 2002 04:35:38 -0000 2.299
+++ gram.y 4 Apr 2002 05:10:23 -0000
@@ -2558,14 +2558,14 @@ n->groupname = NULL; $$ = (Node *)n; }
- | GROUP ColId
+ | GROUP UserId { PrivGrantee *n = makeNode(PrivGrantee);
n->username= NULL; n->groupname = $2; $$ = (Node *)n; }
- | ColId
+ | UserId { PrivGrantee *n = makeNode(PrivGrantee);
n->username= $1;
@@ -5897,7 +5897,6 @@
Iconst: ICONST { $$ = $1; };Sconst: SCONST { $$ = $1; };
-UserId: ColId { $$ = $1; };
/* * Name classification hierarchy.
@@ -5913,6 +5912,13 @@/* Column identifier --- names that can be column, table, etc names. */ColId: IDENT
{ $$ = $1; }
+ | unreserved_keyword { $$ = $1; }
+ | col_name_keyword { $$ = $1; }
+ | PUBLIC { $$ = "public"; }
+ ;
+
+/* User identifier */
+UserId: IDENT { $$ = $1; } | unreserved_keyword { $$ = $1; } |
col_name_keyword { $$ = $1; } ;
--
Peter Eisentraut peter_e@gmx.net
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I see. However my main problem is that the schema of unqualified
> vs1 is affected by the existence of yamada.vs1. I don't think
> it's a useful behavior.
Well, if you don't like it, you could set the search_path to be just
public, or public and then the user's personal namespace. But I think
personal namespace before public should be the default behavior.
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
> PUBLIC can be made less reserved easily. See patch below.
Well, we could do that, but this patch seems an ugly way to do it;
we have too many classifications of keywords already, and I don't
want to introduce another one.
I'd be inclined to make PUBLIC not a keyword at all, and instead have
the production grantee -> ColId do this in its action:
if (strcmp($1, "public") == 0) create PUBLIC PrivGrantee nodeelse create normal PrivGrantee node
An objection to this is that you couldn't make a user named "public"
(with the quotes), since PUBLIC and "public" would look the same to
the action ... but that seems like a good restriction anyway. I'd
be quite willing to tweak CREATE USER to forbid that name.
I suppose it's a judgment call which is uglier. Thoughts?
regards, tom lane
Tom Lane wrote: > > I've been vacillating about whether to choose another name for the > public namespace to avoid the need for quotes here. I can't think > of another good name :-( > For the special schemas, we have pg_catalog, (pg_temp, pg_toast ?), so pg_public could do the trick. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Hiroshi Inoue wrote: > > I see. However my main problem is that the schema of unqualified > vs1 is affected by the existence of yamada.vs1. I don't think > it's a useful behavior. > The unqualified one is there mainly for compatibility, so you can still use your old database set ups without schema names. Once you redo your database to use schemas, or even while you are converting it, there should not be tables with the same name in both places. Anyway, as Tom said, you can change the search order if you prefer. -- 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:
> Tom Lane wrote:
>> I've been vacillating about whether to choose another name for the
>> public namespace to avoid the need for quotes here. I can't think
>> of another good name :-(
> For the special schemas, we have pg_catalog, (pg_temp, pg_toast ?),
> so pg_public could do the trick.
Actually that was my initial choice of name, but I changed my mind
later. The reason is that the dbadmin should be able to restrict or
even delete the public namespace if his usage plans for the database
don't allow any shared objects. If we call it pg_public then the system
will think it is a reserved namespace, and we'd have to put in a special
case to allow it to be deleted (not to mention recreated again, should
the DBA change his mind later).
The public namespace isn't really special and so it should not be named
like a system-reserved namespace. IMHO anyway...
regards, tom lane
Tom Lane wrote: > > I suppose it's a judgment call which is uglier. Thoughts? > Well, PUBLIC is an SQL reserved keyword (pre-92). We are already very liberal with keywords. I would leave PUBLIC alone. I does not _have_ to be "public", so we can just avoid the issue by adding a pg_ prefix to public, common or something else. It is a PostgreSQL concept anyway. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Tom Lane wrote: > > Fernando Nasser <fnasser@redhat.com> writes: > > Tom Lane wrote: > >> I've been vacillating about whether to choose another name for the > >> public namespace to avoid the need for quotes here. I can't think > >> of another good name :-( > > > For the special schemas, we have pg_catalog, (pg_temp, pg_toast ?), > > so pg_public could do the trick. > > Actually that was my initial choice of name, but I changed my mind > later. The reason is that the dbadmin should be able to restrict or > even delete the public namespace if his usage plans for the database > don't allow any shared objects. Can't we prevent creation in there by (un)setting permissions? > If we call it pg_public then the system > will think it is a reserved namespace, and we'd have to put in a special > case to allow it to be deleted (not to mention recreated again, should > the DBA change his mind later). If we can disallow creation with permissions, then we could always keep it. There should be a more practical way of making it empty than having to drop each object individually (DROP will drop the contents but refuse to delete the schema itself as it is a pg_ one?). -- 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:
> Tom Lane wrote:
>> Actually that was my initial choice of name, but I changed my mind
>> later. The reason is that the dbadmin should be able to restrict or
>> even delete the public namespace if his usage plans for the database
>> don't allow any shared objects.
> Can't we prevent creation in there by (un)setting permissions?
That was what I was referring to by "restrict" ... but ISTM we should
allow dropping the namespace too. Why waste cycles searching it if
you don't want to use it?
> There should be a more practical way of making it empty than having to
> drop
> each object individually (DROP will drop the contents but refuse to
> delete
> the schema itself as it is a pg_ one?).
I'd expect DROP on a reserved namespace to error out, and thus do
nothing at all.
regards, tom lane
Tom Lane wrote: > > Fernando Nasser <fnasser@redhat.com> writes: > > Tom Lane wrote: > >> Actually that was my initial choice of name, but I changed my mind > >> later. The reason is that the dbadmin should be able to restrict or > >> even delete the public namespace if his usage plans for the database > >> don't allow any shared objects. > > > Can't we prevent creation in there by (un)setting permissions? > > That was what I was referring to by "restrict" ... but ISTM we should > allow dropping the namespace too. Why waste cycles searching it if > you don't want to use it? > I don't know how the search will be implemented, but it should cost very few instructions (one isnt checks that a list head is zero and another gets the next pointer for the next namespace). And, as we now transform things and keep them as Oids, it will be even cheaper. > > There should be a more practical way of making it empty than having to > > drop > > each object individually (DROP will drop the contents but refuse to > > delete > > the schema itself as it is a pg_ one?). > > I'd expect DROP on a reserved namespace to error out, and thus do > nothing at all. > But we could have: DROP SCHEMA pg_public CONTENTS; or something of a sort (an extension, but a public schema is an extension). And this sintax can come handy for DBAs in general. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > I see. However my main problem is that the schema of unqualified > > vs1 is affected by the existence of yamada.vs1. I don't think > > it's a useful behavior. > > Well, if you don't like it, you could set the search_path to be just > public, Yes I don't like it and probably I would do it for myself but I couldn't force other people to do so. Well for example, how could psqlodbc driver know the CURRENT schema ? > or public and then the user's personal namespace. The order isn't the problem at all. Would the *public* be the CURRENT schema then ? If I recognize correctly, neither is the CURRENT schema in the current spec. regards, Hiroshi Inoue
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Well for example,
> how could psqlodbc driver know the CURRENT schema ?
What "CURRENT" schema? If you have a search path more than one entry
long, there is no unique notion of a CURRENT schema.
regards, tom lane
Tom Lane wrote: > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Well for example, > > how could psqlodbc driver know the CURRENT schema ? > > What "CURRENT" schema? If you have a search path more than one entry > long, there is no unique notion of a CURRENT schema. Oh I see but I think using the search SCHEMA path for table name resolution is harmful. regards, Hiroshi Inoue
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Oh I see but I think using the search SCHEMA path for
> table name resolution is harmful.
Huh? That's more or less the entire *point* of these changes, IMHO.
What's harmful about having a search path?
regards, tom lane
Tom Lane wrote: > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > Oh I see but I think using the search SCHEMA path for > > table name resolution is harmful. > > Huh? That's more or less the entire *point* of these changes, IMHO. > What's harmful about having a search path? I don't object to use a search path to resolve unqualified function, type etc names. But it is very siginificant for users to be able to be sure what tables they are handling. Where's the necessity to use a common search path to resolve table and other objects' name in the first place ? I don't know any OS commands which use the command search path to resolve ordinary file name. We(at least I)'ve been often confused and damaged even when using OS's command search path. Does the flexibilty worth the risk ? The damage would be immeasurable if unexpected tables are chosen. Would PostgreSQL be a dbms unavailable for careless users like me ? regards, Hiroshi Inoue
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I don't object to use a search path to resolve unqualified
> function, type etc names. But it is very siginificant for
> users to be able to be sure what tables they are handling.
I really don't buy this argument; it seems exactly comparable to
arguing that the notion of current directory in Unix is evil, and
that users should be forced to specify absolute paths to every
file that they reference.
There is nothing to stop you from writing qualified names (schema.table)
if you are concerned about being sure that you get the table you intend.
In practice, however, people seem to prefer relative pathnames in most
Unix commands, and I think they'll prefer unqualified names in SQL
commands as well.
> Where's the necessity to use a common search path to resolve
> table and other objects' name in the first place ? I don't
> know any OS commands which use the command search path to
> resolve ordinary file name.
I think that's because of security concerns. I would not object to
having separate search paths for functions/operators and for
tables/datatypes, though, if that would make you happier.
regards, tom lane
> I really don't buy this argument; it seems exactly comparable to > arguing that the notion of current directory in Unix is evil, and > that users should be forced to specify absolute paths to every > file that they reference. You know, I'm kinda surprised that the spec doesn't define a CURRENT_SCHEMA variable you can query??? Chris
Tom Lane wrote: > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > I don't object to use a search path to resolve unqualified > > function, type etc names. But it is very siginificant for > > users to be able to be sure what tables they are handling. > > I really don't buy this argument; it seems exactly comparable to > arguing that the notion of current directory in Unix is evil, and > that users should be forced to specify absolute paths to every > file that they reference. > > There is nothing to stop you from writing qualified names (schema.table) > if you are concerned about being sure that you get the table you intend. Probably I can do it in many cases but I couldn't force others to do it. I don't object if PostgreSQL doesn't allow unqualified table name other than in public/temporary/catalog schema. There's no ambiguity and there's no need for the CURRENT schema. BTW where's the description in SQL standard about the use of SCHEMA path list to resolve unqualified table name ? Is it a PostgreSQL's enhancement(extension) ? As I already mentioned before, SQL-path isn't used to resolve unqalified table name. regards, Hiroshi Inoue
Christopher Kings-Lynne wrote: > > > I really don't buy this argument; it seems exactly comparable to > > arguing that the notion of current directory in Unix is evil, and > > that users should be forced to specify absolute paths to every > > file that they reference. > > You know, I'm kinda surprised that the spec doesn't define a CURRENT_SCHEMA > variable you can query??? > Maybe because it would be the same as CURRENT_USER. For the standard, the schema name used (implied) to qualify objects outside a CREATE SCHEMA statement is a schema name with the SQL-session user id. Except for functions and UDTs where each schema has a SQL-path for searching those (the implied schema must always be in it though). There must be an implementation-defined default for this SQL-path (but the implied schema must also be in it). -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Hiroshi Inoue wrote: > > Tom Lane wrote: > > > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > > I don't object to use a search path to resolve unqualified > > > function, type etc names. But it is very siginificant for > > > users to be able to be sure what tables they are handling. > > > > I really don't buy this argument; it seems exactly comparable to > > arguing that the notion of current directory in Unix is evil, and > > that users should be forced to specify absolute paths to every > > file that they reference. > > > > There is nothing to stop you from writing qualified names (schema.table) > > if you are concerned about being sure that you get the table you intend. > > Probably I can do it in many cases but I couldn't force others > to do it. I don't object if PostgreSQL doesn't allow unqualified > table name other than in public/temporary/catalog schema. > There's no ambiguity and there's no need for the CURRENT schema. > We can't do that. Accordingly to the SQL if you are user HIROSHI and write "SELECT * FROM a;" the table is actually "HIROSHI.a". This must work for people who are using SQL-schemas in their databases or we would have a non-conforming implementation of SCHEMAS (would make the whole exercise pointless IMO). The path proposed by Tom (discussed in the list some time ago) actually does magic: 1) It allows SQL_schema compliant code and database to work as the standard expects; 2) It allows backward compatibility as someone will be able to use the same schema-unaware code and create their databases without schemas as before. 3) If the DBA is careful enough, she/he can convert his/her database to use schemas incrementally. -- 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:
> Christopher Kings-Lynne wrote:
>> You know, I'm kinda surprised that the spec doesn't define a CURRENT_SCHEMA
>> variable you can query???
> Maybe because it would be the same as CURRENT_USER.
It'd probably make sense for us to have one, though, given that I'm
intent on not hardwiring the two concepts together as the spec does ;-).
Although you can interrogate the search path with SHOW, that's not very
accessible at the SQL level, so an SQL function seems useful too.
I'd be inclined to make CURRENT_SCHEMA return the name of the schema
that is the default creation target namespace (viz, the front of the
search path). Thoughts?
regards, tom lane
> -----Original Message----- > From: Fernando Nasser > > Hiroshi Inoue wrote: > > > > Tom Lane wrote: > > > > > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > > > I don't object to use a search path to resolve unqualified > > > > function, type etc names. But it is very siginificant for > > > > users to be able to be sure what tables they are handling. > > > > > > I really don't buy this argument; it seems exactly comparable to > > > arguing that the notion of current directory in Unix is evil, and > > > that users should be forced to specify absolute paths to every > > > file that they reference. > > > > > > There is nothing to stop you from writing qualified names > (schema.table) > > > if you are concerned about being sure that you get the table > you intend. > > > > Probably I can do it in many cases but I couldn't force others > > to do it. I don't object if PostgreSQL doesn't allow unqualified > > table name other than in public/temporary/catalog schema. > > There's no ambiguity and there's no need for the CURRENT schema. > > > > We can't do that. Accordingly to the SQL if you are user HIROSHI > and write "SELECT * FROM a;" the table is actually "HIROSHI.a". > > This must work for people who are using SQL-schemas in their databases > or we would have a non-conforming implementation of SCHEMAS (would make > the whole exercise pointless IMO). Schema name isn't necessarily a user id since SQL-92 though SQL-86 and SQL-89 had and probably Oracle still has the limitation. As far as I see PostgreSQL's schema support wouldn't have the limitation. Probably I wouldn't create the schema HIROSHI using PostgreSQL. When I used Oracle I really disliked the limitation. > The path proposed by Tom (discussed in the list some time ago) actually > does magic: The seems a misuse of SQL-path to me. If I restrict the path to temporary:CURRENT schema:catalog, I would be able to use the CURRENT schema and I can see no other useful way in unqualified table name resolution. Probably I would also be able to use the path as SQL-path. But how can I use the path in both style simultaneously ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > We can't do that. Accordingly to the SQL if you are user HIROSHI > > and write "SELECT * FROM a;" the table is actually "HIROSHI.a". > > > > This must work for people who are using SQL-schemas in their databases > > or we would have a non-conforming implementation of SCHEMAS (would make > > the whole exercise pointless IMO). > > Schema name isn't necessarily a user id since SQL-92 > though SQL-86 and SQL-89 had and probably Oracle still > has the limitation. As far as I see PostgreSQL's schema > support wouldn't have the limitation. Probably I wouldn't > create the schema HIROSHI using PostgreSQL. When > I used Oracle I really disliked the limitation. > You misunderstood what I've said. You may have how many schemas you please. But you will have to refer to their objects specifying the schema name explicitly. The only cases where you can omit the schema name are (accordingly to the SQL'99 standard): 1) The statement is part of a CREATE SCHEMA statement that is creating the object, so the schema being created is assumed (and that is what you want). 2) Your schema has the same name as your user id, your statement is not inside a CREATE SCHEMA and it runs on a session with that authorization id. A schema name equal to the sessuin user id is assumed (which is what you want in this specific case). Otherwise you have to specify the schema explicitly. So, if you name your schema "APPLE", and not HIROSHI, except for inside the CREATE SCHEMA APPLE statement elements, you will have to keep refering to tables with the "APPLE." prefix. PostgreSQL will be smarter and try to relax 2) for you, looking for the table in a public schema as well (if one exists), so old style (non-schema) databases can still be used and people who have schemas with names that are not their user id can save some typing. ;-) -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser writes: > I does not _have_ to be "public", so we can just avoid the issue > by adding a pg_ prefix to public, common or something else. > It is a PostgreSQL concept anyway. No, it's an Oracle concept. -- Peter Eisentraut peter_e@gmx.net
> -----Original Message----- > From: Fernando Nasser > > Hiroshi Inoue wrote: > > > > > We can't do that. Accordingly to the SQL if you are user HIROSHI > > > and write "SELECT * FROM a;" the table is actually "HIROSHI.a". > > > > > > This must work for people who are using SQL-schemas in their databases > > > or we would have a non-conforming implementation of SCHEMAS > (would make > > > the whole exercise pointless IMO). > > > > Schema name isn't necessarily a user id since SQL-92 > > though SQL-86 and SQL-89 had and probably Oracle still > > has the limitation. As far as I see PostgreSQL's schema > > support wouldn't have the limitation. Probably I wouldn't > > create the schema HIROSHI using PostgreSQL. When > > I used Oracle I really disliked the limitation. > > > > You misunderstood what I've said. You may have how many schemas > you please. But you will have to refer to their objects specifying > the schema name explicitly. The only cases where you can omit the > schema name are (accordingly to the SQL'99 standard): Please tell me where's the description in SQL99 ? I wasn't able to find it unfortunately. regards, Hiroshi Inoue
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Fernando Nasser <fnasser@redhat.com> writes: > > Christopher Kings-Lynne wrote: > >> You know, I'm kinda surprised that the spec doesn't define a > CURRENT_SCHEMA > >> variable you can query??? > > > Maybe because it would be the same as CURRENT_USER. > > It'd probably make sense for us to have one, though, given that I'm > intent on not hardwiring the two concepts together as the spec does ;-). > Although you can interrogate the search path with SHOW, that's not very > accessible at the SQL level, so an SQL function seems useful too. > > I'd be inclined to make CURRENT_SCHEMA return the name of the schema > that is the default creation target namespace (viz, the front of the > search path). Thoughts? I think only one schema other than TEMP or catalog is allowed in the search path for the resolution of table name. I can call the schema the CURRENT_SCHEMA. If the restricted search path is inappropriate for the resolution of function, type etc name, you have to provide another path IMHO. BTW every time I examined SQL99, I can find neither the description CURRENT_SCHEMA == CURRENT_USER nor the one that the schema name of an unqualified table name may vary according to the table name. Probably it's because of my poor English. I'm happy if you could tell me where to find it. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > You misunderstood what I've said. You may have how many schemas > > you please. But you will have to refer to their objects specifying > > the schema name explicitly. The only cases where you can omit the > > schema name are (accordingly to the SQL'99 standard): > > Please tell me where's the description in SQL99 ? > I wasn't able to find it unfortunately. > As most things in the SQL standard, you have to collect information from several places and add it together. Look at 4.20, 11.1 and specially at the rules for <schema qualified name>. Then think a little bit about scenarios, trying to apply the rules. It is a pain, but there is no other way. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
> -----Original Message----- > From: Fernando Nasser > > Hiroshi Inoue wrote: > > > > > You misunderstood what I've said. You may have how many schemas > > > you please. But you will have to refer to their objects specifying > > > the schema name explicitly. The only cases where you can omit the > > > schema name are (accordingly to the SQL'99 standard): > > > > Please tell me where's the description in SQL99 ? > > I wasn't able to find it unfortunately. > > > > As most things in the SQL standard, you have to collect information > from several places and add it together. > > Look at 4.20, 11.1 and specially at the rules for > <schema qualified name>. OK I can see at 4.20. If a reference to a <table name> does not explicitly contain a <schema name>, then a specific <schema name> is implied. The particular <schema name> associated with such a <table name> dependson the context in which the <table name> appears and is governed by the rules for <schema qualified name>. Unfortunately I can't find what to see at 11.1. Please tell me where to see. However I can see the following at 5.4 Names and Identifiers 11) If a <schema qualified name> does not contain a <schema name>, then Case: a) If the <schema qualified name>is contained in a <schema definition>, then the <schema name> that is specified or implicit in the <schema definition> is implicit. b) Otherwise, the <schema name> that is specified or implicit for the <SQL-clientmodule definition> is implicit. regards, Hiroshi Inoue
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> However I can see the following at 5.4 Names and Identifiers
> 11) If a <schema qualified name> does not contain a <schema name>, then
> Case:
> a) If the <schema qualified name> is contained in a <schema
> definition>,
> then the <schema name> that is specified or implicit in the <schema
> definition>
> is implicit.
Yes. Fernando, our existing CREATE SCHEMA command does not get this
right for references from views to tables, does it? It seems to me that
to get compliant behavior, we'll need to temporarily push the new schema
onto the front of the namespace search path while parsing view
definitions inside CREATE SCHEMA.
(The relevance to the current discussion is that this is easy to do if
SET variables roll back on error ... but it might be tricky if they do
not.)
regards, tom lane
Tom Lane wrote: > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > However I can see the following at 5.4 Names and Identifiers > > 11) If a <schema qualified name> does not contain a <schema name>, then > > Case: > > a) If the <schema qualified name> is contained in a <schema > > definition>, > > then the <schema name> that is specified or implicit in the <schema > > definition> > > is implicit. > > Yes. Fernando, our existing CREATE SCHEMA command does not get this > right for references from views to tables, does it? It seems to me that > to get compliant behavior, we'll need to temporarily push the new schema > onto the front of the namespace search path while parsing view > definitions inside CREATE SCHEMA. > Correct. It only takes care of proper setting/checking the schema name for the view (as is done for tables) that are being created. Doing as you suggest would be nice (similar to what we do with the authid). BTW, I think have to properly fill/check the schema when the grant objects are tables/views (I am not sure how functions will be handled). I will send a patch in later today or tomorrow, unless you want to do it differently. I prefer to do in in the parser because I can issue and error if a grant is for something that is not an object in the schema being created. Regards, Fernando -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser wrote: > > As most things in the SQL standard, you have to collect information > from several places and add it together. > > Look at 4.20, 11.1 and specially at the rules for > <schema qualified name>. > > Then think a little bit about scenarios, trying to apply the rules. > > It is a pain, but there is no other way. I couldn't find the description CURRENT_SCHEMA == CURRENT_USER. If I recognize SQL99 correctly, the CURRENT schema is the schema defined in a <SQL-client module> not restricted to the CURRENT user. Well here's my proposal. 1) Use the different search path for table name and others. 2) Allow only one schema other than temp or catalog in the table name search path so that we can call it the CURRENT schema. Comments ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > Fernando Nasser wrote: > > > > As most things in the SQL standard, you have to collect information > > from several places and add it together. > > > > Look at 4.20, 11.1 and specially at the rules for > > <schema qualified name>. > > > > Then think a little bit about scenarios, trying to apply the rules. > > > > It is a pain, but there is no other way. > > I couldn't find the description CURRENT_SCHEMA == CURRENT_USER. > If I recognize SQL99 correctly, the CURRENT schema is the schema > defined in a <SQL-client module> not restricted to the CURRENT > user. > Yes, but we don't have a "module" language. You have to look for "session". -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser wrote: > > Hiroshi Inoue wrote: > > > > Fernando Nasser wrote: > > > > > > As most things in the SQL standard, you have to collect information > > > from several places and add it together. > > > > > > Look at 4.20, 11.1 and specially at the rules for > > > <schema qualified name>. > > > > > > Then think a little bit about scenarios, trying to apply the rules. > > > > > > It is a pain, but there is no other way. > > > > I couldn't find the description CURRENT_SCHEMA == CURRENT_USER. > > If I recognize SQL99 correctly, the CURRENT schema is the schema > > defined in a <SQL-client module> not restricted to the CURRENT > > user. > > > > Yes, OK I wasn't wrong at this point. > but we don't have a "module" language. You have to look for > "session". Do you mean PostgreSQL by the *we* ? We have never been and would never be completely in conformity to standard. If we don't have a "module" language, does it mean we couldn't have any subsitute for <SQL-client module> ? regards, Hiroshi Inoue
Hi all, I see there's a TODO item for large object security, it's a feature I'd really like to see. I'm willing to put in the timeto write a patch, but know far to little about postgres internals and history to just dive in. Has there been any discussionon this list about what this feature should be or how it might be implemented? I saw a passing reference to "LOBLOCATORs" in the list archives, but that was all. What's a LOB LOCATOR ? What about giving each large object its own permission flags? ex: GRANT SELECT ON LARGE OBJECT 10291 TO USER webapp; GRANT SELECT, DELETE, UPDATE ON LARGE OBJECT 10291 TO USER admin; Default permission flags (and INSERT permissions) would be set at the table level. All objects without specific permissionswould use the table rules. This allows for backward compatibility and convenience. I think per-object security is important. A user shouldn't be able to get at another user's data just by guessing the rightOID. Ideally, users without permission would not know there were objects in the database they were not allowed to see. I can also imagine a security scheme that uses rule/trigger syntax to give the user a hook to provide her own security functions. I haven't thought that through, though. Any thoughts? -Damon