Search Postgresql Archives

Re: Understanding database schemas

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Aug 2, 2013 at 12:56 PM, Melvin Call <melvincall979@xxxxxxxxx> wrote:
On Fri, Aug 2, 2013 at 11:56 AM, Bosco Rama <postgres@xxxxxxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux