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