Understanding database schemas

From: Melvin Call <melvincall979(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Understanding database schemas
Date: 2013-08-02 16:33:38
Message-ID: CADGQN56gPexKPVMET9D1KHmoK58Do_cwYs-HLvQwLAgi=bU5aw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good morning all,

I am trying to get a better understanding of how schemas can be used to
limit access to objects, and I seem to be failing miserably. Can anyone
point me to documentation about, or a decent tutorial on, schema usage for
access separation? I have tried to understand through the user guide. It
leads me to believe that ownership of a schema gives the owner all rights
to it, but my experimentation seems to indicate otherwise. Creating a
schema as superuser and assigning ownership of it to another role does not
seem to make it visible to that role:

$ psql -U postgres

DROP SCHEMA IF EXISTS hrschema CASCADE;
DROP DATABASE IF EXISTS personnel;
DROP USER IF EXISTS hr_admin;

CREATE USER hr_admin
WITH CREATEDB
PASSWORD 'md5be394806d6a21c6c52aa2b76063c7d9d';

DROP DATABASE IF EXISTS personnel;
CREATE DATABASE personnel
WITH ENCODING='UTF8'
OWNER=hr_admin
TEMPLATE=template0
LC_COLLATE='C'
LC_CTYPE='C'
CONNECTION LIMIT=-1;

CREATE SCHEMA hrschema
AUTHORIZATION hr_admin;

\dnS+
List of schemas
Name | Owner | Access privileges |
Description
--------------------+----------+----------------------+----------------------------------
hrschema | hr_admin | |
information_schema | postgres | postgres=UC/postgres+|
| | =U/postgres |
pg_catalog | postgres | postgres=UC/postgres+| system catalog
schema
| | =U/postgres |
pg_temp_1 | postgres | |
pg_toast | postgres | | reserved schema for
TOAST tables
pg_toast_temp_1 | postgres | |
public | postgres | postgres=UC/postgres+| standard public
schema
| | =UC/postgres |
(7 rows)

ALTER USER hr_admin
SET search_path
TO hrschema, public;

\q

$ psql -U hr_admin personnel

SHOW search_path;
search_path
------------------
hrschema, public
(1 row)

\dnS+
List of schemas
Name | Owner | Access privileges |
Description
--------------------+----------+----------------------+----------------------------------
information_schema | postgres | postgres=UC/postgres+|
| | =U/postgres |
pg_catalog | postgres | postgres=UC/postgres+| system catalog
schema
| | =U/postgres |
pg_temp_1 | postgres | |
pg_toast | postgres | | reserved schema for
TOAST tables
pg_toast_temp_1 | postgres | |
public | postgres | postgres=UC/postgres+| standard public
schema
| | =UC/postgres |
(6 rows)

The lack of Access privileges seems to be the key, but I am failing to
understand why an object's owner would not be given any access to it by
default.

Thanks,
Melvin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hamann.w 2013-08-02 16:55:03 Re: incremental dumps
Previous Message Kevin Grittner 2013-08-02 15:25:12 Re: Fastest Index/Algorithm to find similar sentences