Re: [INTERFACES] [HACKERS] Schemas: status report, call for developers
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [INTERFACES] [HACKERS] Schemas: status report, call for developers
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [INTERFACES] [HACKERS] Schemas: status report, call for developers
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [INTERFACES] [HACKERS] Schemas: status report, call for developers
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
On Wednesday 01 May 2002 06:38, Tom Lane wrote:
Ian Barwick [EMAIL PROTECTED] writes:
How can I restrict the query to the schemas in the
current search path, i.e. the schema names returned
by SELECT current_schemas() ?
Well, this is the issue open for public discussion.
We could define some function along the lines of
is_visible_table(oid) returns bool, and then you could use
that as a WHERE clause in your query. But I'm worried about
the performance implications --- is_visible_table() would have
to do several retail probes of the system tables, and I don't
see any way to optimize that across hundreds of table OIDs.
I have a nagging feeling that this could be answered by defining
a view on pg_class that only shows visible tables ... but I don't
quite see how to define that efficiently, either. Ideas anyone?
(time passes...)
How about a function such as the one attached: select_schemas_setof()
which returns the OIDs of the schemas in the current search path as
a set. (Note: select_schemas_setof() as shown is a userspace C function.)
It works like this:
template1=# CREATE DATABASE schema_test;
CREATE DATABASE
template1=# \c schema_test
You are now connected to database schema_test.
schema_test=# CREATE OR REPLACE FUNCTION current_schemas_setof()
schema_test-# RETURNS setof OID
schema_test-# as '/path/to/current_schemas_setof.so'
schema_test-# LANGUAGE 'C';
CREATE FUNCTION
I can then do this:
schema_test=# CREATE SCHEMA foo;
CREATE SCHEMA
schema_test=# CREATE TABLE foo.mytab(col1 int, col2 text);
CREATE TABLE
schema_test=# CREATE SCHEMA bar;
CREATE SCHEMA
schema_test=# CREATE TABLE bar.mytab(col1 int, col2 text);
CREATE TABLE
schema_test=# SET search_path = public, foo, bar;
SET
schema_test=# SELECT current_schemas();
current_schemas
--
{public,foo,bar}
(1 row)
schema_test=# SELECT current_schemas_setof, n.nspname
schema_test-# FROM public.current_schemas_setof() cs, pg_namespace n
schema_test-# WHERE cs.current_schemas_setof = n.oid;
current_schemas_setof | nspname
--+
16563 | pg_temp_1
11 | pg_catalog
2200 | public
24828 | foo
24835 | bar
(3 rows)
With the function in place I can then create an SQL function like this:
CREATE OR REPLACE FUNCTION public.first_visible_namespace(name)
RETURNS oid
AS
'SELECT n.oid
FROM pg_namespace n, pg_class c, public.current_schemas_setof() cs
WHERE c.relname= $1
AND c.relnamespace=n.oid
AND n.oid= cs.current_schemas_setof
LIMIT 1'
LANGUAGE 'sql';
which can be used like this:
schema_test=# select public.first_visible_namespace('mytab');
first_visible_namespace
-
24828
(1 row)
i.e. finds the first visible schema containing an unqualified relation name.
24828 corresponds to the OID of schema foo.
The following VIEW:
CREATE VIEW public.desc_table_view AS
SELECT n.nspname AS Schema,
c.relname AS Table,
a.attname AS Column,
format_type (a.atttypid, a.atttypmod) AS Type
FROM pg_class c, pg_attribute a, pg_namespace n
WHERE a.attnum 0
AND c.relkind IN ('r', 'v', 'S')
AND a.attrelid = c.oid
AND c.relnamespace=n.oid
AND n.oid IN (SELECT first_visible_namespace(c.relname))
ORDER BY a.attnum;
then provides a simplified simulation of psql's slash command \d [NAME] for
unqualified relation names, e.g.:
schema_test=# SELECT * FROM public.desc_table_view WHERE Table = 'mytab';
Schema | Table | Column | Type
+---++-
foo| mytab | col1 | integer
foo| mytab | col2 | text
(2 rows)
schema_test=# SET search_path= bar, foo, public;
SET
schema_test=# SELECT * FROM public.desc_table_view WHERE Table = 'mytab';
Schema | Table | Column | Type
+---++-
bar| mytab | col1 | integer
bar| mytab | col2 | text
(2 rows)
schema_test=# SET search_path= public;
SET
schema_test=# SELECT * FROM public.desc_table_view WHERE Table = 'mytab';
Schema | Table | Column | Type
+---++--
(0 rows)
which I think is the desired behaviour. Currently \d [NAME] produces this:
schema_test=# SET search_path= bar, foo, public;
SET
schema_test=# \d mytab
Table mytab
Column | Type | Modifiers
+-+---
col1 | integer |
col1 | integer |
col2 | text|
col2 | text|
i.e. finds and describes foo.mytab and bar.mytab.
(Note: SELECT * FROM public.desc_table_view will just dump an unordered
list of all columns for the first visible instance of each table name).
Assuming current_schemas_setof() can be implemented as an internal function,
(I haven't managed it myself yet :-( ), I suspect it is a more efficient
alternative to a putative
Re: [HACKERS] Schemas: status report, call for developers
Ian Barwick [EMAIL PROTECTED] writes:
CREATE OR REPLACE FUNCTION public.first_visible_namespace(name)
RETURNS oid
AS
'SELECT n.oid
FROM pg_namespace n, pg_class c, public.current_schemas_setof() cs
WHERE c.relname=3D $1
AND c.relnamespace=3Dn.oid
AND n.oid=3D cs.current_schemas_setof
LIMIT 1'
LANGUAGE 'sql';
I don't believe this is correct. The LIMIT clause will ensure you
get at most one answer, but it'd be pure luck whether it is the right
answer, when there are multiple tables of the same name in the
namespaces of the search path.
The following VIEW:
CREATE VIEW public.desc_table_view AS
SELECT n.nspname AS Schema,
c.relname AS Table,
a.attname AS Column,
format_type=09(a.atttypid, a.atttypmod) AS Type
FROM pg_class c, pg_attribute a, pg_namespace n
WHERE a.attnum 0
AND c.relkind IN ('r', 'v', 'S')
AND a.attrelid =3D c.oid
AND c.relnamespace=3Dn.oid
AND n.oid IN (SELECT first_visible_namespace(c.relname))
ORDER BY a.attnum;
I was hoping to find something more efficient than that --- quite aside
from the speed or correctness of first_visible_namespace(), a query
depending on an IN is not going to be fast.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schemas: status report, call for developers
On Mon, 6 May 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: For this if we look once again at RelnameGetRelid(relname) in backend/catalog/namespace.c wouldn't this is_visible() function simply be a wrapper around it? Sort of. It's there already, see RelationIsVisible. Doh. Next function down. I see there are routines doing similar things but for functions and others. I'm right in saying that OID isn't unique in a database (necessarily) and so we couldn't have a general object_is_visible(oid) function that did the appropiate from the type of object refered to? It just seems that if we're interested in showing tables according to visibility then shouldn't we be doing the same for these other things? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: I see there are routines doing similar things but for functions and others. I'm right in saying that OID isn't unique in a database (necessarily) and so we couldn't have a general object_is_visible(oid) function that did the appropiate from the type of object refered to? Not in the current structure. Even if OID were guaranteed unique across the database, how would you determine which kind of object a given OID referred to? Seems like it would take expensive probing of a lot of different tables until you found a match --- which is a bit silly when the calling query generally knows darn well where it got the OID from. I suppose we could define an object_is_visible(tableoid, oid) function, but I'm not sure if it has any real usefulness. It just seems that if we're interested in showing tables according to visibility then shouldn't we be doing the same for these other things? Sure; if we go this routine then all five of the FooIsVisible routines will need to be exported as SQL functions. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: Coming back to this subject if I may but only briefly, I hope. How about making a slight change to current_schemas() and including an optional argument such that something like: current_schemas(1) returns the complete list of schemas in the search path including the implicit temporary space and the pg_catalog (if not already listed obviously), while current_schemas() and current_schemas(0) behave as now. I don't really care for that syntax, but certainly we could talk about providing a version of current_schemas that tells the Whole Truth. Having something like this would enable client's like PgAccess to determine the complete list of visible objects. Well, no, it wouldn't. Say there are multiple tables named foo in different namespaces in your search path (eg, a temp table hiding a permanent table of the same name). A test like where current_schemas *= relnamespace won't reflect this correctly. I'm suspecting that what we really need is some kind of is_visible_table() test function, and then you'd do select * from pg_class where is_visible_table(oid); At least I've not been able to think of a better idea than that. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
On Mon, 6 May 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Coming back to this subject if I may but only briefly, I hope. How about making a slight change to current_schemas() and including an optional argument such that something like: current_schemas(1) returns the complete list of schemas in the search path including the implicit temporary space and the pg_catalog (if not already listed obviously), while current_schemas() and current_schemas(0) behave as now. I don't really care for that syntax, but certainly we could talk about providing a version of current_schemas that tells the Whole Truth. Having something like this would enable client's like PgAccess to determine the complete list of visible objects. Well, no, it wouldn't. Say there are multiple tables named foo in different namespaces in your search path (eg, a temp table hiding a permanent table of the same name). A test like where current_schemas *= relnamespace won't reflect this correctly. I'm suspecting that what we really need is some kind of is_visible_table() test function, and then you'd do select * from pg_class where is_visible_table(oid); At least I've not been able to think of a better idea than that. Ok, where I was coming from was the idea of the client, I'm most interested in PgAccess at the moment, retrieving the search path and cross referencing that against the results of the queries for tables etc. I seemed to remember mention of an is_visible() function earlier in the thread but that for some reason this would mean a performance hit across the board, or at least in many places. However, reviewing my emails I see not such comment about performance. Tom originally suggested relation_is_visible(oid) as the function. I also got it wrong about when the temporary space is emptied. I had been thinking it was when the connection terminated. However, I see from the same old message that this happens when the first temporary item is created in a session. Therefore, my way would be invalid anyway; or would it? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: I also got it wrong about when the temporary space is emptied. I had been thinking it was when the connection terminated. However, I see from the same old message that this happens when the first temporary item is created in a session. Therefore, my way would be invalid anyway; or would it? It would work as long as the variant form of current_schemas() truly reflects the effective search path --- because until you create a temporary item, there is no temp schema in the effective path. Still, the issue of hiding seems to be a good reason not to code clients that way. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Schemas: status report, call for developers
On Mon, 6 May 2002, Nigel J. Andrews wrote: On Mon, 6 May 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Coming back to this subject if I may but only briefly, I hope. How about making a slight change to current_schemas() and including an optional argument such that something like: current_schemas(1) returns the complete list of schemas in the search path including the implicit temporary space and the pg_catalog (if not already listed obviously), while current_schemas() and current_schemas(0) behave as now. I don't really care for that syntax, but certainly we could talk about providing a version of current_schemas that tells the Whole Truth. Wouldn't such a function just be based on backend/catalog/namespace.c:RelnameGetRelid(const char *relname) ? I'm suspecting that what we really need is some kind of is_visible_table() test function, and then you'd do select * from pg_class where is_visible_table(oid); At least I've not been able to think of a better idea than that. [snip] For this if we look once again at RelnameGetRelid(relname) in backend/catalog/namespace.c wouldn't this is_visible() function simply be a wrapper around it? Obviously the parameter [probably] wouldn't be an OID but rather a name. If I knew which file would be most appropiate for this (utils/adt/name.c?) I'd have had a go at making a patch. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: For this if we look once again at RelnameGetRelid(relname) in backend/catalog/namespace.c wouldn't this is_visible() function simply be a wrapper around it? Sort of. It's there already, see RelationIsVisible. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2002-05-02 at 16:52, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Is PROC array slot number something internal to postgres ? Yes. If we used PID then we'd eventually have 64K (or whatever the range of PIDs is on your platform) different pg_temp_nnn entries cluttering pg_namespace. Should they not be cleaned up at backend exit even when they are in range 1..MaxBackends ? But we only need MaxBackends different entries at any one time. So the correct nnn value is 1..MaxBackends. BackendId meets the need perfectly. -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schemas: status report, call for developers
Hannu Krosing [EMAIL PROTECTED] writes: On Thu, 2002-05-02 at 16:52, Tom Lane wrote: If we used PID then we'd eventually have 64K (or whatever the range of PIDs is on your platform) different pg_temp_nnn entries cluttering pg_namespace. Should they not be cleaned up at backend exit even when they are in range 1..MaxBackends ? Hm. We currently remove the schema contents (ie the temp tables) but not the pg_namespace entry itself. Seems like deleting that only to have to recreate it would be a waste of cycles. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2002-05-02 at 05:33, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: So, how does one determine the current schema for temporary tables, i.e. what name would be in search_path if it wasn't implicitly included? The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array slot number). AFAIK there isn't any exported way to determine your BackendId from an SQL query. The non-portable way on Linux RH 7.2 : create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C'; CREATE select getpid() getpid1 - 31743 (1 row) I think that useful libc stuff things like this should be put in some special schema, initially available to superusers only. perhaps LIBC.GETPID() -- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schemas: status report, call for developers
On Thursday 02 May 2002 05:33, Tom Lane wrote:
[on establishing whether a relation is in the search path]
This doesn't yield much insight about cases where the match pattern
includes a (partial?) schema-name specification, though. If I'm
allowed to write something like \z s*.t* to find tables beginning
with t in schemas beginning with s, should that include all schemas
beginning with s? Only those in my search path (probably wrong)?
Only those that I have USAGE privilege on? Not sure.
If namespace privileges are based around the Unix directory/file protection
model (as you stated in another thread, see:
http://geocrawler.com/archives/3/10/2002/4/450/8433871/ ), then
a wildcard search on the schema name should logically include
all visible schemas, not just the ones where the user has USAGE privilege.
Or put it another way, is there any reason to exclude information from
say \z which the user can find out by querying pg_class? At the moment
(at least in CVS from 30.4.02) a user can see permissions on tables in schemas
on which he/she has no USAGE privileges:
template1=# create database schema_test;
CREATE DATABASE
template1=# \c schema_test
You are now connected to database schema_test.
schema_test=# create schema foo;
CREATE
schema_test=# create table foo.bar (pk int, txt text);
CREATE
schema_test=# create schema foo2;
CREATE
schema_test=# create table foo2.bar (pk int, txt text);
CREATE
schema_test=# create user joe;
CREATE USER
schema_test=# grant usage on schema foo to joe;
GRANT
schema_test=# \c - joe
You are now connected as new user joe.
schema_test= SELECT nspname AS schema,
schema_test-relname AS object,
schema_test-relkind AS type,
schema_test-relacl AS access
schema_test- FROM pg_class c
schema_test- INNER JOIN pg_namespace n
schema_test- ON c.relnamespace=n.oid
schema_test- WHERE relkind in ('r', 'v', 'S') AND
schema_test-relname NOT LIKE 'pg$_%%' ESCAPE '$' AND
schema_test-nspname || '.' || relname LIKE 'f%.b%';
schema | object | type | access
++--+
foo| bar| r|
foo2 | bar| r|
(2 rows)
i.e. user joe can see which objects exist in schema foo2, even though
he has no USAGE privilege. (Is this behaviour intended?)
Yours
Ian Barwick
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Schemas: status report, call for developers
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schemas: status report, call for developers
Hannu Krosing [EMAIL PROTECTED] writes: On Thu, 2002-05-02 at 05:33, Tom Lane wrote: The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array slot number). AFAIK there isn't any exported way to determine your BackendId from an SQL query. The non-portable way on Linux RH 7.2 : create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C'; But PID is not BackendId. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2002-05-02 at 15:48, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: On Thu, 2002-05-02 at 05:33, Tom Lane wrote: The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array slot number). AFAIK there isn't any exported way to determine your BackendId from an SQL query. The non-portable way on Linux RH 7.2 : create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C'; But PID is not BackendId. Are you sure ? I was assuming that BackendId was the process id of current backend and that's what getpid() returns. What is the Backend ID then ? Is PROC array slot number something internal to postgres ? - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
On Wed, 1 May 2002, Jeffrey W. Baker wrote: These changes may break DBD::Pg. What is the expected time of this release? I will review my code for impact. I think the current plan is to go beta in late summer. So there's no tremendous hurry. I was just sending out a wake-up call ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schemas: status report, call for developers
Hannu Krosing [EMAIL PROTECTED] writes: Is PROC array slot number something internal to postgres ? Yes. If we used PID then we'd eventually have 64K (or whatever the range of PIDs is on your platform) different pg_temp_nnn entries cluttering pg_namespace. But we only need MaxBackends different entries at any one time. So the correct nnn value is 1..MaxBackends. BackendId meets the need perfectly. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
Ian Barwick [EMAIL PROTECTED] writes: i.e. user joe can see which objects exist in schema foo2, even though he has no USAGE privilege. (Is this behaviour intended?) It's open for debate I suppose. Historically we have not worried about preventing people from looking into the system tables, except for cases such as pg_statistic where this might expose actual user data. AFAICS we could only prevent this by making selective views on the system tables and then prohibiting ordinary users from accessing the underlying tables directly. I'm not in a big hurry to do that myself, if only for backward-compatibility reasons. We still do have the option of separate databases, and I'd be inclined to tell people to use those if they want airtight separation between users. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
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/03bindok 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
Tom Lane wrote: 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). (and also in mail to Bill Cunningham) 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 -- General consistency with SELECT behaviour sounds right to me. 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. My digestive organs tell me: an unqualified wildcard pattern should stick to the search path; the search path should only be overridden when the user explicitly provides a wildcard pattern for schema names. This would be consistent with the behaviour of \d etc., i.e. \d mytab should look for 'mytab' in the current search path; \dt my* should look for tables beginning with my in the current search path; \dt f*.my* would look for same in all schemas beginning with f; and \dt *.my* would look in all schemas. Problem: wildcard pattern is a bit of a misnomer, the relevant commands take regular expressions, which means the dot in \z f*.my* won't necessarily be the dot in \z foo.mytab - it would have to be written \z f*\\.my*. Though technically correct this strikes me as counterintuitive, especially with the double escaping (once for psql, once for the regex literal). An alternative solution would be to allow the pattern matching commands to accept either one (\z my*) or two (\z f* my*) regular expressions; in the latter case the first regex is for the schema name, the second for the object name. However, doing away with the dot altogether is also counterintuitive and breaks with the usual schema denotation. Proposal: in wildcard slash commands drop regular expressions and use LIKE for pattern matching. This would enable commands such as \z f%.my%. (Would this mean major breakage? Is there an installed base of scripts which rely on psql slash commands and regular expressions?) I can't personally recall ever having needed to use a regular expression any more complex than the wildcard pattern matching which could be implemented just as well with LIKE. (Anyone requiring regular expression matching could still create appropriate views). Question - which output format is preferable?: schema_test=# \z Access privileges for database schema_test Schema | Object | Access privileges ++--- public | bar| foo| bar| (2 rows) or schema_test=# \z Access privileges for database schema_test Object | Access privileges +--- public.bar | foo.bar| (2 rows) If you've got time to work on fixing frontend code, or even helping to work out definitional questions like these (...) Hmm, time for ask not what your database can do for you but what you can do for your database. I'm willing to put my keyboard where my mouth is and take on psql once any outstanding questions are cleared up, if noone better qualified than me comes forward and provided someone takes a critical look at anything I do. Yours Ian Barwick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2 May 2002, Ian Barwick wrote: Tom Lane wrote: [snipped] 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 -- General consistency with SELECT behaviour sounds right to me. I take it temporary tables are going to be included in such a list, since that would seem sensible from the SELECT behaviour point of view, and may be even also from the user's point of view. So, how does one determine the current schema for temporary tables, i.e. what name would be in search_path if it wasn't implicitly included? (Just throwing ideas around in my head) -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: So, how does one determine the current schema for temporary tables, i.e. what name would be in search_path if it wasn't implicitly included? The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array slot number). AFAIK there isn't any exported way to determine your BackendId from an SQL query. Another problem is that the pg_temp schema is lazily evaluated --- it's not actually attached to and cleaned out until you first try to create a temp table in a particular session. This seems a clear win from a performance point of view, but it makes life even more difficult for queries that are trying to determine which pg_class entries are visible in one's search path. I have already had occasion to write subroutines that answer the question is this relation (resp. type, function, operator) visible in the current search path? --- where visible means not just that its namespace is in the path, but that this object is the frontmost entry of its particular name. Perhaps it'd make sense to export these routines as SQL functions, along the lines of relation_is_visible(oid) returns bool. Then one could use queries similar to select * from pg_class p where p.relname like 'match_pattern' and relation_is_visible(p.oid); to implement a psql command that requires finding tables matching an (unqualified) relation-name pattern. The tables found would be only those that you could reference with unqualified table names. This doesn't yield much insight about cases where the match pattern includes a (partial?) schema-name specification, though. If I'm allowed to write something like \z s*.t* to find tables beginning with t in schemas beginning with s, should that include all schemas beginning with s? Only those in my search path (probably wrong)? Only those that I have USAGE privilege on? Not sure. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
Bill Cunningham [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
Tom Lane wrote: Bill Cunningham [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schemas: status report, call for developers
Bill Cunningham [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
produces a result like this: schema | object + public | abc foo| abc foo| xyz bar| xyz (4 rows) How can I restrict the query to the schemas in the current search path, i.e. the schema names returned by SELECT current_schemas() ? Now, if we had functions-returning-sets, this would all be easy as all you'd need to do would be to join it with the function returning the set of schemas in your search path :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
Christopher Kings-Lynne [EMAIL PROTECTED] writes: What about CREATE USER tgl WITH SCHEMA; ? Uh, what about it? It's not a standard syntax AFAIK. If I were running an installation where I wanted one schema per user as default, I'd rather have an auto_create_schema SET parameter that told CREATE USER to do the dirty work for me automatically. But the sneaky part of this is that users are installation-wide, whereas schemas are only database-wide. To make this really work painlessly, you'd want some kind of mechanism that'd auto-create a schema for the user in every database he's allowed access to. How can we define that cleanly? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
Ian Barwick [EMAIL PROTECTED] writes: How can I restrict the query to the schemas in the current search path, i.e. the schema names returned by SELECT current_schemas() ? Well, this is the issue open for public discussion. We could define some function along the lines of is_visible_table(oid) returns bool, and then you could use that as a WHERE clause in your query. But I'm worried about the performance implications --- is_visible_table() would have to do several retail probes of the system tables, and I don't see any way to optimize that across hundreds of table OIDs. I have a nagging feeling that this could be answered by defining a view on pg_class that only shows visible tables ... but I don't quite see how to define that efficiently, either. Ideas anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
