Understanding database schemas

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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.

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&gt;

HTH,
Bosco.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.