Re: Understanding database schemas

On Fri, Aug 2, 2013 at 12:56 PM, Melvin Call <melvincall979(at)gmail(dot)com>wrote:

> On Fri, Aug 2, 2013 at 11:56 AM, Bosco Rama <postgres(at)boscorama(dot)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.
>