schemas in postgresql

introduction

clusters contain the databases. A cluster is created with the initdb command-line tool. I don't know why it's not named initcluster.!!

Databases are the next level; then, it's schemas which are contained inside the databases which are contained inside the clusters.

the $PGDATA variable should point to the root of the cluster's tree. Other than that there is no way to access the cluster. Alternatively, you can always specify psql -d CLUSTERNAME.

under $PGDATA go in the directory called base. These are databases which are visible. Under that, it's the oids which are visible. They can be matched up to the oids with select oid,realname from pg_class;

the cluster shares only with itself the data found in pg_group, pg_database and pg_shadow (list of valid user groups).

Typically, I run each cluster on its own port. Each postmaster can only be connected to one cluster at a time.

Well, this is about schemas, so let's get started!

Schemas group database objects together within a database, like on a per-user basis. If you were looking for everybody to have their own databases, just use schmemas and create all the schemas in the same database. If no schemas are made and the object was not created in a schema, it defaults to the "public" schema, as can be seen issuing \dv from the psql prompt.

What I will show you is how users can be limited to their own schemas. This creates a mysql-like separation of visibility on the basis of the username they connect as.

To learn more type "schemas in postgresql" from other web sites, type that in my search engine at the top of the page.

create schema sql

select nspname from pg_namespace;

shows all schemas (== database namespaces)

create schema joe-schmoe

all schemas must be created by the database owner. It is not possible to grant the create schema right to anybody else.

grant rights on the schema

You mus use the schema keyword in your grant statement. e.g.:

template1=# grant create on apachelog.* to mod_log;
ERROR:  invalid privilege type CREATE for table object
template1=# grant create table on apachelog.* to mod_log;
ERROR:  parser: parse error at or near "table" at character 14

So, the correct options are:

grant usage on schema apachelog to mod_log;
grant create on schema apachelog to mod_log;

you need to issue the grant and create rights to users using the schema. elsewhise, they won't be able to create tables.

As far as GRANTs goes, the manual has the following to say:

Optionally, CREATE SCHEMA can include subcommands to create objects within the new schema. The subcommands are treated essentially the same as separate commands issued after creating the schema, except that if the AUTHORIZATION clause is used, all the created objects will be owned by that user.

searchpath

template1=# show search_path;
search_path
--------------
$user,public
(1 row)

$user is a place holder for the name of the current user, meaning the first schema to be searched will be one with the same name as the current user. As PostgreSQL does not automatically create a schema for each user (unlike databases such as Oracle), in the default setting public will be the schema to which all non-qualified object names refer.

The search_path is "in which schemas will I look for database objects". An sql command is "set search_path to schema_name". or "alter user test set search_path= this_schema."

After creating the namespace and issuing a GRANT USAGE ON SCHEMA XYZ TO USERAXV you can do the set search_path to 'joe-schmoe','joe-schmoe-old';.

Your schema for that user is completely set up now!

issue alter user mod_log set search_path ='mod_log','public'; to make changes permanent

revoking rights from schemas

revoke usage on schema xyz from userabc

finding all schemas on system

select nspname from pg_namespace;

or, if you are in psql, the backslash-command is \dn