Thread: Schemas: status report, call for developers
Current CVS tip has most of the needed infrastructure for SQL-spec schema support: you can create schemas, and you can create objects within schemas, and search-path-based lookup for named objects works. There's still a number of things to be done in the backend, but it's time to start working on schema support in the various frontends that have been broken by these changes. I believe that pretty much every frontend library and client application that looks at system catalogs will need revisions. So, this is a call for help --- I don't have the time to fix all the frontends, nor sufficient familiarity with many of them. JDBC and ODBC metadata code is certainly broken; so are the catalog lookups in pgaccess, pgadmin, and so on. psql and pg_dump are broken as well (though I will take responsibility for fixing pg_dump, and will then look at psql if no one else has done it by then). I'm not even sure what else might need to change. Here's an example of what's broken: test=# create schema foo; CREATE test=# create table foo.mytab (f1 int, f2 text); CREATE test=# create schema bar; CREATE test=# create table bar.mytab (f1 text, f3 int); CREATE test=# \d mytab Table "mytab"Column | Type | Modifiers --------+---------+-----------f1 | text |f1 | integer |f2 | text |f3 | integer | psql's \d command hasn't the foggiest idea that there might now be more than one pg_class entry with the same relname. It needs to be taught about that --- but even before that, we need to work out schema-aware definitions of the wildcard expansion rules for psql's backslash commands that accept wildcarded names. In the above example, probably "\d mytab" should have said "no such table" --- because neither foo nor bar were in my search path, so I should not see them unless I give a qualified name (eg, "\d foo.mytab" or "\d bar.mytab"). For commands that accept wildcard patterns, what should happen --- should "\z my*" find these tables, if they're not in my search path? Is "\z f*.my*" sensible to support? I dunno yet. If you've got time to work on fixing frontend code, or even helping to work out definitional questions like these, please check out current CVS tip or a nightly snapshot tarball and give it a try. (But do NOT put any valuable data into current sources --- until pg_dump is fixed, you won't be able to produce a useful backup of a database that uses multiple schemas.) Some documentation can be found at http://developer.postgresql.org/docs/postgres/sql-naming.html http://developer.postgresql.org/docs/postgres/sql-createschema.html http://developer.postgresql.org/docs/postgres/sql-grant.html http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL (see SEARCH_PATH) but more needs to be written. (In particular, I think the Tutorial could stand to have a short section added about schemas; and the Admin Guide ought to be revised to discuss running one database with per-user schemas as a good alternative to per-user databases. Any volunteers to write that stuff?) Some things that don't work yet in the backend: 1. There's no DROP SCHEMA. (If you need to, you can drop the contained objects and then manually delete the pg_namespace row for the schema.) No ALTER SCHEMA RENAME either (though you can just UPDATE the pg_namespace row if you need that). 2. CREATE SCHEMA with sub-statements isn't up to SQL spec requirements yet. Best bet is to create the schema and then create contained objects separately, as in the above example. 3. I'm not sure that the newly-defined GRANT privileges are all checked everywhere they should be. Also, the default privilege settings probably need fine-tuning still. 4. We probably need more helper functions and/or predefined system views to make it possible to fix the frontends in a reasonable way --- for example, it's still quite difficult for something looking at pg_class to determine which tables are visible in the current search path. Thoughts about what should be provided are welcome. regards, tom lane
Bill Cunningham <billc@ballydev.com> writes:
> I would think this should produce the following:
> test=# \d mytab
> Table "bar.mytab"
> Column | Type | Modifiers
> --------+---------+-----------
> f1 | text |
> f1 | integer |
> Table "foo.mytab"
> Column | Type | Modifiers
> --------+---------+-----------
> f2 | text |
> f3 | integer |
Even when schemas bar and foo are not in your search path? (And,
perhaps, not even accessible to you?)
My gut feeling is that "\d mytab" should tell you about the same
table that "select * from mytab" would find. Anything else is
probably noise to you --- if you wanted to know about foo.mytab,
you could say "\d foo.mytab".
However, \d is not a wildcardable operation AFAIR. For the commands
that do take wildcard patterns (like \z), I'm not as sure what should
happen.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 30 April 2002 18:32 > To: pgsql-hackers@postgresql.org; pgsql-interfaces@postgresql.org > Subject: [INTERFACES] Schemas: status report, call for developers > > > Current CVS tip has most of the needed infrastructure for > SQL-spec schema support: you can create schemas, and you can > create objects within schemas, and search-path-based lookup > for named objects works. There's still a number of things to > be done in the backend, but it's time to start working on > schema support in the various frontends that have been broken > by these changes. I believe that pretty much every frontend > library and client application that looks at system catalogs > will need revisions. So, this is a call for help --- I don't > have the time to fix all the frontends, nor sufficient > familiarity with many of them. > > JDBC and ODBC metadata code is certainly broken; so are the > catalog lookups in pgaccess, pgadmin, and so on. psql and > pg_dump are broken as well (though I will take responsibility > for fixing pg_dump, and will then look at psql if no one else > has done it by then). I'm not even sure what else might need > to change. > Thanks Tom, this is just the post I've been waiting for! To anyone thinking of hacking pgAdmin at the moment -> now would probably not be the best time as I will be *seriously* restructuring pgSchema. Regards, Dave.
Bill Cunningham <billc@ballydev.com> writes:
> So we now have a default schema name of the current user?
> ... This is exactly how DB2 operates, implict schemas for each user.
You can operate that way. It's not the default though; the DBA will
have to explicitly do a CREATE SCHEMA for each user. For instance:
test=# CREATE USER tgl;
CREATE USER
test=# CREATE SCHEMA tgl AUTHORIZATION tgl;
CREATE
test=# \c - tgl
You are now connected as new user tgl.
test=> select current_schemas();current_schemas
-----------------{tgl,public} -- my search path is now tgl, public
(1 row)
-- this creates tgl.foo:
test=> create table foo(f1 int);
CREATE
test=> select * from foo;f1
----
(0 rows)
test=> select * from tgl.foo;f1
----
(0 rows)
If you don't create schemas then you get backwards-compatible behavior
(all the users end up sharing the "public" schema as their current
schema).
See the development-docs pages I mentioned before for details.
regards, tom lane
On Wed, May 01, 2002 at 10:05:23AM +0800, Christopher Kings-Lynne wrote: > > phpPgAdmin (WebDB) will be broken as well. I think myself and at least a > few other committers lurk here tho. > > Other things that will break: > > TOra > Various KDE interfaces GNUe will break, as well. Ross
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> GNUe will break, as well.
Do I hear a volunteer to fix it?
regards, tom lane
On Wed, May 01, 2002 at 12:03:00AM -0400, Tom Lane wrote: > "Ross J. Reedstrom" <reedstrm@rice.edu> writes: > > GNUe will break, as well. > > Do I hear a volunteer to fix it? I'm willing to implement whatever clever solution we all come up with. I'll have to coordinate w/ the GNUe IRC folks to get it checked in. Ross
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
>>> GNUe will break, as well.
> I'm willing to implement whatever clever solution we all come up with.
If you need help in inventing a solution, it'd be a good idea to explain
the problem. Personally I'm not familiar with GNUe ...
regards, tom lane
On Wed, May 01, 2002 at 12:56:00AM -0400, Tom Lane wrote: > "Ross J. Reedstrom" <reedstrm@rice.edu> writes: > >>> GNUe will break, as well. > > I'm willing to implement whatever clever solution we all come up with. > > If you need help in inventing a solution, it'd be a good idea to explain > the problem. Personally I'm not familiar with GNUe ... I think all the interfaces are having the same fundemental problem: how to limit the tables 'seen' to a particular list of schema (those in the path). GNUe is GNU Enterprise System - a somewhat grandiose name for a business middleware solutions project. It's highly modular, with a common core to deal with things like DB access. There's a reasonably nice forms designer to handle quickie 2-tier DB apps (client-server, skip the middleware). Right now, it's mostly coded in python. I'm taking off on a business trip for the remainder of the week, starting tomorrow (err today?!) morning. I'll take the GNUe code along and see what it's db schema discovery code is actually doing, and think about what sort of clever things to do. I think for GNUe, we might get away with requiring the end-user (designer) to select a particular schema to work in, and then just qualify everything. Later, Ross
On Tue, Apr 30, 2002 at 09:41:47PM +0300, Oleg Bartunov wrote: > I think DBD::Pg driver very much depends on system tables. > Hope, Jeffrey (current maintainer) is online. These changes may break DBD::Pg. What is the expected time of this release? I will review my code for impact. Thanks for the warning, Jeffrey
<snip>
>
>Here's an example of what's broken:
>
>test=# create schema foo;
>CREATE
>test=# create table foo.mytab (f1 int, f2 text);
>CREATE
>test=# create schema bar;
>CREATE
>test=# create table bar.mytab (f1 text, f3 int);
>CREATE
>test=# \d mytab
> Table "mytab"
> Column | Type | Modifiers
>--------+---------+-----------
> f1 | text |
> f1 | integer |
> f2 | text |
> f3 | integer |
>
I would think this should produce the following:
Table "bar.mytab"Column | Type | Modifiers
--------+---------+-----------f1 | text |f1 | integer |
Table "foo.mytab"Column | Type | Modifiers
--------+---------+-----------f2 | text |f3 | integer |
What do you think?
- Bill Cunningham
I think DBD::Pg driver very much depends on system tables.
Hope, Jeffrey (current maintainer) is online.
regards,
Oleg
On Tue, 30 Apr 2002, Tom Lane wrote:
> Current CVS tip has most of the needed infrastructure for SQL-spec
> schema support: you can create schemas, and you can create objects
> within schemas, and search-path-based lookup for named objects works.
> There's still a number of things to be done in the backend, but it's
> time to start working on schema support in the various frontends that
> have been broken by these changes. I believe that pretty much every
> frontend library and client application that looks at system catalogs
> will need revisions. So, this is a call for help --- I don't have the
> time to fix all the frontends, nor sufficient familiarity with many
> of them.
>
> JDBC and ODBC metadata code is certainly broken; so are the catalog
> lookups in pgaccess, pgadmin, and so on. psql and pg_dump are broken
> as well (though I will take responsibility for fixing pg_dump, and will
> then look at psql if no one else has done it by then). I'm not even
> sure what else might need to change.
>
> Here's an example of what's broken:
>
> test=# create schema foo;
> CREATE
> test=# create table foo.mytab (f1 int, f2 text);
> CREATE
> test=# create schema bar;
> CREATE
> test=# create table bar.mytab (f1 text, f3 int);
> CREATE
> test=# \d mytab
> Table "mytab"
> Column | Type | Modifiers
> --------+---------+-----------
> f1 | text |
> f1 | integer |
> f2 | text |
> f3 | integer |
>
> psql's \d command hasn't the foggiest idea that there might now be more
> than one pg_class entry with the same relname. It needs to be taught
> about that --- but even before that, we need to work out schema-aware
> definitions of the wildcard expansion rules for psql's backslash
> commands that accept wildcarded names. In the above example, probably
> "\d mytab" should have said "no such table" --- because neither foo nor
> bar were in my search path, so I should not see them unless I give a
> qualified name (eg, "\d foo.mytab" or "\d bar.mytab"). For commands
> that accept wildcard patterns, what should happen --- should "\z my*"
> find these tables, if they're not in my search path? Is "\z f*.my*"
> sensible to support? I dunno yet.
>
> If you've got time to work on fixing frontend code, or even helping
> to work out definitional questions like these, please check out current
> CVS tip or a nightly snapshot tarball and give it a try. (But do NOT
> put any valuable data into current sources --- until pg_dump is fixed,
> you won't be able to produce a useful backup of a database that uses
> multiple schemas.)
>
> Some documentation can be found at
> http://developer.postgresql.org/docs/postgres/sql-naming.html
> http://developer.postgresql.org/docs/postgres/sql-createschema.html
> http://developer.postgresql.org/docs/postgres/sql-grant.html
> http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL (see SEARCH_PATH)
> but more needs to be written. (In particular, I think the Tutorial
> could stand to have a short section added about schemas; and the Admin
> Guide ought to be revised to discuss running one database with per-user
> schemas as a good alternative to per-user databases. Any volunteers to
> write that stuff?)
>
> Some things that don't work yet in the backend:
>
> 1. There's no DROP SCHEMA. (If you need to, you can drop the contained
> objects and then manually delete the pg_namespace row for the schema.)
> No ALTER SCHEMA RENAME either (though you can just UPDATE the
> pg_namespace row if you need that).
>
> 2. CREATE SCHEMA with sub-statements isn't up to SQL spec requirements
> yet. Best bet is to create the schema and then create contained objects
> separately, as in the above example.
>
> 3. I'm not sure that the newly-defined GRANT privileges are all checked
> everywhere they should be. Also, the default privilege settings
> probably need fine-tuning still.
>
> 4. We probably need more helper functions and/or predefined system views
> to make it possible to fix the frontends in a reasonable way --- for
> example, it's still quite difficult for something looking at pg_class to
> determine which tables are visible in the current search path. Thoughts
> about what should be provided are welcome.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Tom Lane wrote: >Bill Cunningham <billc@ballydev.com> writes: > >>I would think this should produce the following: >> > >>test=# \d mytab >> Table "bar.mytab" >> Column | Type | Modifiers >>--------+---------+----------- >> f1 | text | >> f1 | integer | >> > >> Table "foo.mytab" >> Column | Type | Modifiers >>--------+---------+----------- >> f2 | text | >> f3 | integer | >> > >Even when schemas bar and foo are not in your search path? (And, >perhaps, not even accessible to you?) > >My gut feeling is that "\d mytab" should tell you about the same >table that "select * from mytab" would find. Anything else is >probably noise to you --- if you wanted to know about foo.mytab, >you could say "\d foo.mytab". > >However, \d is not a wildcardable operation AFAIR. For the commands >that do take wildcard patterns (like \z), I'm not as sure what should >happen. > > regards, tom lane > So we now have a default schema name of the current user? For example: foobar@somewhere> psql testme testme=# select * from mytab Table "foobar.mytab"Column | Type | Modifiers --------+---------+-----------f2 | text |f3 | integer | like that? This is exactly how DB2 operates, implict schemas for each user. - Bill Cunningham
> > JDBC and ODBC metadata code is certainly broken; so are the > > catalog lookups in pgaccess, pgadmin, and so on. psql and > > pg_dump are broken as well (though I will take responsibility > > for fixing pg_dump, and will then look at psql if no one else > > has done it by then). I'm not even sure what else might need > > to change. phpPgAdmin (WebDB) will be broken as well. I think myself and at least a few other committers lurk here tho. Other things that will break: TOra Various KDE interfaces Chris
> test=# CREATE USER tgl; > CREATE USER > test=# CREATE SCHEMA tgl AUTHORIZATION tgl; > CREATE What about "CREATE USER tgl WITH SCHEMA;" ? Which will implicitly do a "CREATE SCHEMA tgl AUTHORIZATION tgl;" Chris
On Thu, May 02, 2002 at 05:28:36PM +0300, Oleg Bartunov wrote: > On Wed, 1 May 2002, Jeffrey W. Baker wrote: > > > On Tue, Apr 30, 2002 at 09:41:47PM +0300, Oleg Bartunov wrote: > > > I think DBD::Pg driver very much depends on system tables. > > > Hope, Jeffrey (current maintainer) is online. > > > > These changes may break DBD::Pg. What is the expected > > time of this release? I will review my code for impact. > > Jeffrey, > > btw, DBD-Pg 1.13 doesn't passed all tests > (Linux 2.4.17, pgsql 7.2.1, DBI-1.21) > > t/02prepare.........ok > t/03bind............ok > t/04execute.........FAILED tests 5-7 > Failed 3/10 tests, 70.00% okay > t/05fetch...........ok > t/06disconnect......ok These tests were failing when I inherited the code. I'll fix them when I rewrite the parser. -jwb
On Wed, 1 May 2002, Jeffrey W. Baker wrote: > On Tue, Apr 30, 2002 at 09:41:47PM +0300, Oleg Bartunov wrote: > > I think DBD::Pg driver very much depends on system tables. > > Hope, Jeffrey (current maintainer) is online. > > These changes may break DBD::Pg. What is the expected > time of this release? I will review my code for impact. Jeffrey, btw, DBD-Pg 1.13 doesn't passed all tests (Linux 2.4.17, pgsql 7.2.1, DBI-1.21) t/02prepare.........ok t/03bind............ok t/04execute.........FAILED tests 5-7 Failed 3/10 tests, 70.00% okay t/05fetch...........ok t/06disconnect......ok > > Thanks for the warning, > Jeffrey > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Tom Lane wrote: > bar were in my search path, so I should not see them unless I give a > qualified name (eg, "\d foo.mytab" or "\d bar.mytab"). For commands > that accept wildcard patterns, what should happen --- should "\z my*" > find these tables, if they're not in my search path? Is "\z f*.my*" > sensible to support? I dunno yet. I am still reading the thread, but I thought \z mytab should show only the first match, like SELECT * from mytab, and \z *.mytab should show all matching tables in the schema search path. This does make '.' a special character in the psql wildcard character set, but as no one uses '.' in a table name, I think it is OK. -- 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, Pennsylvania19026
Tom Lane wrote: > If you don't create schemas then you get backwards-compatible behavior > (all the users end up sharing the "public" schema as their current > schema). I am a little uncomfortable about this. It means that CREATE TABLE will create a table in 'public' if the user doesn't have a schema of their own, and in their private schema if it exists. I seems strange to have such a distinction based on whether a private schema exists. Is this OK? -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am a little uncomfortable about this. It means that CREATE TABLE will
> create a table in 'public' if the user doesn't have a schema of their
> own, and in their private schema if it exists. I seems strange to have
> such a distinction based on whether a private schema exists. Is this OK?
You have a better idea?
Given that we want to support both backwards-compatible and SQL-spec-
compatible behavior, I think some such ugliness is inevitable.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I am a little uncomfortable about this. It means that CREATE TABLE will > > create a table in 'public' if the user doesn't have a schema of their > > own, and in their private schema if it exists. I seems strange to have > > such a distinction based on whether a private schema exists. Is this OK? > > You have a better idea? > > Given that we want to support both backwards-compatible and SQL-spec- > compatible behavior, I think some such ugliness is inevitable. I don't have a better idea, but I am wondering how this will work. If I create a schema with my name, does it get added to the front of my schema schema search path automatically, or do I set it with SET, perhaps in my per-user startup SET column? If I want to prevent some users from creating tables in my database, do I remove CREATE on the schema using REVOKE SCHEMA, then create a schema for every user using the database? -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't have a better idea, but I am wondering how this will work. If I
> create a schema with my name, does it get added to the front of my
> schema schema search path automatically,
Yes (unless you've futzed with the standard value of search_path).
> If I want to prevent some users from creating tables in my database, do
> I remove CREATE on the schema using REVOKE SCHEMA, then create a schema
> for every user using the database?
Well, you revoke world create access on the public schema (or maybe even
delete the public schema, if you don't need it). I don't see why you'd
give people their own schemas if the intent is to keep them from
creating tables.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I don't have a better idea, but I am wondering how this will work. If I > > create a schema with my name, does it get added to the front of my > > schema schema search path automatically, > > Yes (unless you've futzed with the standard value of search_path). > > > If I want to prevent some users from creating tables in my database, do > > I remove CREATE on the schema using REVOKE SCHEMA, then create a schema > > for every user using the database? > > Well, you revoke world create access on the public schema (or maybe even > delete the public schema, if you don't need it). I don't see why you'd > give people their own schemas if the intent is to keep them from > creating tables. No, I was saying you would have to create schemas for the people who you _want_ to be able to create tables. With the old NOCREATE patch, you could just remove create permission from a user. With schemas, you have to remove all permission for table creation, then grant it to those you want by creating schemas for them. This is similar to handling of Unix permissions. If you want to restrict access to a file or directory, you remove public permission, and add group permission, then add the people who you want access to that group. There are no _negative_ permissions, as there are no negative permissions in the unix file system. I just wanted to be clear that restricting access will be multi-step process. If I remove public create access to public, can the super user or db owner still create tables? -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> If I remove public create access to public, can the super user or db
> owner still create tables?
Superusers can always do whatever they want.
The DB owner (assume he's not a superuser) has no special privileges
w.r.t. the public schema at the moment. We could perhaps put in a
kluge to change this, but it would definitely be a kluge --- I don't
see any clean way to make the behavior different.
One possible approach would be for a superuser to change the ownership
of public to be the DB owner.
regards, tom lane