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
