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
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 |