Re: Understanding database schemas

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;

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