On Fri, Aug 2, 2013 at 11:56 AM, Bosco Rama <postgres@xxxxxxxxxxxxx> wrote:
On 08/02/13 09:33, Melvin Call wrote:You've created 'hrschema' schema in the 'postgres' database at this
>
> $ 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;
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
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.
