Re: Modifying pg_shadow? - Mailing list pgsql-novice

Jason Hihn wrote:
 > Wow. I learned a lot just now... You have me interested in these 'schemas' I
 > know they are new for 7.3, so where can I find more info on them? (I read
 > Section 2.8)

I think the docs contain all necessary information.  You
should also have a look at the description of CREATE SCHEMA
in the SQL Commands section of the Reference Manual.

 > It now looks like I can't do what I intended and I'll have to create my own
 > master permission list table, in addition to posture's. But I'll ask it - is
 > there a way to get all pg_class info for all tables in all databases
 > (schemas)?

Not easily.  You'll have to connect to all databases in
turn.  For example, a little shell script like this will
do it (caution, this is from the top of my head, not
tested):

DBSEL="SELECT datname FROM pg_database WHERE datname != 'template0'"
psql -q -t -A -d template1 -c "$DBSEL" \
| while read DATNAME; do
        psql ... -d $DATNAME -c "select * from pg_class"
done

Alternatively, the shell script could build a script for
psql using the \c meta-command to change databases, so
psql doesn't have to be exec'ed a hundred times if you
have a hundred databases ...

 > Reading the docs (2.8), this is what I gather:
 > --Setup:
 > 1) create a database (D), fill with objects
 > 2) create a schema (S)(inherits current database's objects)

No.  The objects already belong to the "public" schema.
When you create a new schema, it won't inherit them.
It will be empty.

You should create the schema first, then create the
objects inside that schema.

Remember that schemas are namespaces.  Think of it like
directories in a filesystem, as an analogy.  The database
would be the filesystem, the schemas are directories (only
one level of them, though, as in MS-DOS 1.0), and the
tables are files in that directory.

 > --Apps:
 > 1) Connect to database D
 > 2) SET search_path TO S, public; (for not having to scope all sql to this
 > schema)
 > 3) use the schema as if it were a separate database  i.e. (select
 > S.tablename -> select tablename, because of my previous step)

Right.  You have to be careful with permissions, though.
You can configure HBA access based on databases, but not
based on schemas.  You need to use GRANT / REVOKE.

 > Now, what are the implications for:
 > backups - If I do a pg_dump, it'll dump all schemas?

Yes.

 > Can I dump just one?

You can dump all tables that are contained in one schema.

 > creating tables in the schema - they stay in that schema only?

Yes.  Remember, a schema is just a namespace.

 > modifying the database from which the schema was created - modifies all
 > schemas descended from that database?

What exactly do you mean?  What modification?

 > create table with my step #2 above - does it go in the schema or the
 > database?

It will always go into a schema, either your self-defined
one, or the the "public" schema, depending on your search
path (if you don't specify the schema explicitely).  The
function current_schema() will tell you in which one a
newly created table will go.

Regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"... there are two ways of constructing a software design:  One way
is to make it so simple that there are _obviously_ no deficiencies and
the other way is to make it so complicated that there are no _obvious_
deficiencies."        -- C.A.R. Hoare, ACM Turing Award Lecture, 1980