Postgres Pro
Downloads
Home   >   mailing lists

Thread: Understanding database schemas

Understanding database schemas

From
Melvin Call
Date:
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

Re: Understanding database schemas

From
Bosco Rama
Date:
On 08/02/13 09:33, Melvin Call wrote:
>
> $ 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;

You've created 'hrschema' schema in the 'postgres' database at this
point.

You'll need to connect to the 'personnel' database before issuing this
DDL command.  And since you are reconnecting, you may as well do it as
the 'hr_admin' user and skip the whole 'authorization' clause.

HTH,
Bosco.

Re: Understanding database schemas

From
Melvin Call
Date:
On Fri, Aug 2, 2013 at 11:56 AM, Bosco Rama <postgres@boscorama.com> wrote:
On 08/02/13 09:33, Melvin Call wrote:
>
> $ 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;

You've created 'hrschema' schema in the 'postgres' database at this
point.

You'll need to connect to the 'personnel' database before issuing this
DDL command.  And since you are reconnecting, you may as well do it as
the 'hr_admin' user and skip the whole 'authorization' clause.

Thanks Bosco, that was it. The DDL is in a script, and I even had the connection command there, but I had commented it out and sadly I just never caught that. And I've even slept since then... I now have a department table in personnel.hrschema that was created under the hr_admin role.
 

HTH,
Bosco.

If I may pigtail another related question, what is the procedure for allowing another user access to that schema?

As you may have surmised, I am trying to create an HR database, and I want certain users to only have access to certain entities. So hr_admin will own the database and have access to everything. hr_user only needs access to public information, such as department names, people names, phone numbers, etc., and I am trying to limit that access through hrschema (which I meant to name hr_public_schema, but let's stick with my incorrect name for the moment for the sake of clarity). So hrschema will contain the public tables that I want hr_user to have access to. I tried (as hr_admin):

GRANT SELECT
ON ALL TABLES IN SCHEMA hrschema
TO hr_user;

$ psql -U hr_user personnel
\c personnel

\dt
No relations found.

SELECT has_table_privilege('hr_user', 'hrschema.department', 'select');
ERROR:  permission denied for schema hrschema


Obviously I am still missing something

I appreciate your time and help.

Re: Understanding database schemas

From
Melvin Call
Date:
On Fri, Aug 2, 2013 at 12:56 PM, Melvin Call <melvincall979@gmail.com> wrote:
On Fri, Aug 2, 2013 at 11:56 AM, Bosco Rama <postgres@boscorama.com> wrote:
On 08/02/13 09:33, Melvin Call wrote:
>
> $ 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;

You've created 'hrschema' schema in the 'postgres' database at this
point.

You'll need to connect to the 'personnel' database before issuing this
DDL command.  And since you are reconnecting, you may as well do it as
the 'hr_admin' user and skip the whole 'authorization' clause.

Thanks Bosco, that was it. The DDL is in a script, and I even had the connection command there, but I had commented it out and sadly I just never caught that. And I've even slept since then... I now have a department table in personnel.hrschema that was created under the hr_admin role.
 

HTH,
Bosco.

If I may pigtail another related question, what is the procedure for allowing another user access to that schema?

As you may have surmised, I am trying to create an HR database, and I want certain users to only have access to certain entities. So hr_admin will own the database and have access to everything. hr_user only needs access to public information, such as department names, people names, phone numbers, etc., and I am trying to limit that access through hrschema (which I meant to name hr_public_schema, but let's stick with my incorrect name for the moment for the sake of clarity). So hrschema will contain the public tables that I want hr_user to have access to. I tried (as hr_admin):

GRANT SELECT
ON ALL TABLES IN SCHEMA hrschema
TO hr_user;

To clarify, I logged out as hr_admin after the above statement, and logged in as hr_user at this point.
 

$ psql -U hr_user personnel
\c personnel

\dt
No relations found.

SELECT has_table_privilege('hr_user', 'hrschema.department', 'select');
ERROR:  permission denied for schema hrschema


Obviously I am still missing something

I appreciate your time and help.

Re: Understanding database schemas

From
Bosco Rama
Date:
On 08/02/13 10:56, Melvin Call wrote:
>
> If I may pigtail another related question, what is the procedure for
> allowing another user access to that schema?

Heh.  You almost have the words already:
   grant usage on schema hrschema to hr_user;

This will allow them to see the objects in the schema.  It does nothing
to allow them to access the objects themselves, so you'll still need to
grant the appropriate privileges on the objects within the schema.  You
can use default privileges if you don't wish to grant them at creation
time.  See the docs for default privs:

<http://www.postgresql.org/docs/9.2/static/sql-alterdefaultprivileges.html>

HTH,
Bosco.

Re: Understanding database schemas

From
Melvin Call
Date:
On Fri, Aug 2, 2013 at 1:36 PM, Bosco Rama <postgres@boscorama.com> wrote:
On 08/02/13 10:56, Melvin Call wrote:
>
> If I may pigtail another related question, what is the procedure for
> allowing another user access to that schema?

Heh.  You almost have the words already:
   grant usage on schema hrschema to hr_user;

Too easy! Thanks for the help. That'll teach me to try to learn something new in the middle of development crunch time! And many thanks for the link as well. This will help a great deal.
 

This will allow them to see the objects in the schema.  It does nothing
to allow them to access the objects themselves, so you'll still need to
grant the appropriate privileges on the objects within the schema.  You
can use default privileges if you don't wish to grant them at creation
time.  See the docs for default privs:

<http://www.postgresql.org/docs/9.2/static/sql-alterdefaultprivileges.html>

HTH,
Bosco.