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