Re: Restricted access on DataBases

On 09/05/2016 05:45 AM, Durumdara wrote:
> Dear PG-masters!
>
> We want to put more databases to one server, to "public" schema:
> DB_A, DB_B, DB_C.

The PUBLIC schema is contained within a database not the other way
around, so further explanation is necessary.

> And users:
> US_A, US_B, US_C, and Main_Admin.
> We want to setup the environment.
> Every simple user can access his database:
> DB_A - US_A
> DB_B - US_B
> DB_C - US_C
>
> They can't access other databases only theirs.
>
> Main_Admin can access all databases.

Is Main_Admin created as a superuser?

If not what role attributes does it have?

>
> I'm not sure how to do it perfectly.
> We tried to remove "public" role, and add US_A to DB_A.
> But the subobjects (table named "teszt") aren't accessable.

How did you specify GRANTing permissions on DB_A to US_A?

You might to want to look at the privileges that are provided to various
objects by GRANT:

https://www.postgresql.org/docs/9.5/static/sql-grant.html

GRANT on Database Objects

For instance;

CREATE

     For databases, allows new schemas to be created within the database.


>
> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.

Hard to answer until we know what permissions Main_Admin has.

>
> What is the simple way to we can avoid the access from another users,
> but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences, etc).
>
> And how we keep this state later? For example: DB_A creates a new table.
> Main_Admin must access this automatically...

Defualt privileges:

https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html

>
> I don't understand this area properly. For me the "public" means "access
> for all users", which isn't good (DB_A vs. US_C).

Actually it is not as broad as that.

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default privileges
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for
databases; EXECUTE privilege for functions; and USAGE privilege for
languages. The object owner can, of course, REVOKE both default and
expressly granted privileges. (For maximum security, issue the REVOKE in
the same transaction that creates the object; then there is no window in
which another user can use the object.) Also, these initial default
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
command.

"
>
> As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).
>
> Thank you for the help. information, or an example!
>
>     DD
>


--
Adrian Klaver
adrian.klaver@aklaver.com