Re: Restricted access on DataBases

Dear Adrian and Charles!

I tried to create a step by step instruction.

The real commands are separated by ";"

Other commands are: "login as username", "use db"
I ran them in PGAdmin with changing the connection to simulate what I feel
as problem.
I suppused the ex_dbuser have owner rights to the DB, and with "default
privileges" it must see the new tables created by ex_mainuser. Without them
I would understand why (inherited role would have diffferent rights on
creation).

If I want to represent this in other way, I would say:
- ex_mainuser have all rights as ex_dbuser, but it could have more
- but when ex_dbuser got all rights to future objects, it must see what
ex_mainuser created on his database

If this not happened then my idea crashes, because we must login with
ex_dbuser to create objects, or we must create all objects by ex_mainuser
WITH ONLY OWNER SETTING (as ex_dbuser).

The example:

-- login as su
-- CREATE DATABASE ct_db WITH OWNER = ex_dbuser ENCODING = 'UTF8'
TABLESPACE = pg_default template = template0;
-- use ct_db

-- login as ex_dbuser
-- begin; create table t_dbuser (id integer);commit;

-- login as ex_mainuser
-- begin; create table t_mainuser (id integer); commit;

-- login as ex_dbuser
-- select * from t_mainuser; -- ERROR!

-- login as su
-- ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

-- login as ex_mainuser
-- begin; create table t_mainuser2 (id integer); commit;

-- login as ex_dbuser
-- select * from t_mainuser2; -- ERROR!

-- login as su
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT, UPDATE,
DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

-- login as ex_mainuser
-- begin; create table t_mainuser3 (id integer); commit;

-- login as ex_dbuser
-- select * from t_mainuser3; -- ERROR!

Thanks: dd

2016-09-14 16:52 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 09/14/2016 06:52 AM, Durumdara wrote:
>
>> Dear Charles!
>>
>>
>
>> I thought before this test that mainuser get all rights as dbuser, so it
>> have rights to the next (future) objects too.
>> So mainuser and dbuser have equivalent rights in db database.
>>
>> Thanks for your every info!
>>
>
> In my previous post I mentioned using \dp or \z. The output from those
> commands can be hard to understand without a key, which I forgot to
> mention. The key can be found here:
>
> https://www.postgresql.org/docs/9.5/static/sql-grant.html
>
> in the Notes section.
>
>
>
>> Regards
>> dd
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>