Hello
On 09/05/2016 05:56 PM, Charles Clavadetscher wrote:
Hello
On 09/05/2016 04:19 PM, Adrian Klaver wrote:
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.
When use speak of "their database", do you mean that they are the owner
of it or that they simply should have specific privileges?
If not, is main_admin the owner of all databases?
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
Yes, read this document, it helps a lot.
Pragmatically I find a simple way to restrict access to a database is to
revoke CONNECT on it from public and then GRANT CONNECT and, if
necessary, privileges on objects in that database to the legitimate
user(s):
REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;
This will still not free you from managing the privileges on the objects
created. If main_admin is a superuser it will hav.e access to everything
anyway and you don't need to manage grants for it. If not, as Adrian
said, and assuming in db_a, only us_a will create objects, you will have
to alter the default privileges of us_a to grant privileges to
main_admin. This must be done for each database, i.e. db_b, db_c, etc.
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...).
Actually you could:
GRANT us_a, us_b, us_c TO main_admin;
Here an example (obviously you will choose secure passwords and
initialize them using \password <username>. This is just a very simple
example). I used 9.5 but it would work with earlier versions as well.
-- Create roles and databases
CREATE ROLE main_admin LOGIN PASSWORD 'xxx';
CREATE ROLE us_a LOGIN PASSWORD 'xxx';
CREATE DATABASE db_a;
ALTER DATABASE db_a OWNER TO us_a;
CREATE ROLE us_b LOGIN PASSWORD 'xxx';
CREATE DATABASE db_b;
ALTER DATABASE db_b OWNER TO us_b;
-- Restrict access
REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;
REVOKE CONNECT ON DATABASE db_b FROM public;
GRANT CONNECT ON DATABASE db_b TO us_b;
-- Grant all user rights to main_admin:
GRANT us_a, us_b TO main_admin;
Test:
-- Connect as us_a to db_a:
charles@charles.localhost=# \c db_a us_a
Password for user us_a:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "db_a" as user "us_a".
-- Create a table and enter some data:
us_a@db_a.localhost=> CREATE TABLE test (id INTEGER, tst TEXT);
CREATE TABLE
us_a@db_a.localhost=> INSERT INTO test VALUES (1,'Blabla');
INSERT 0 1
-- Try to connect as user us_b to db_a:
us_a@db_a.localhost=> \c db_a us_b
Password for user us_b:
FATAL: permission denied for database "db_a"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
-- Connect as user main_admin to db_a:
us_a@db_a.localhost=> \c db_a main_admin
Password for user main_admin:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "db_a" as user "main_admin".
-- Make some stuff:
main_admin@db_a.localhost=> SELECT * FROM test;
id | tst
----+--------
1 | Blabla
(1 row)
main_admin@db_a.localhost=> INSERT INTO test VALUES (2,'Blublu');
INSERT 0 1
-- Connect again as us_a:
main_admin@db_a.localhost=> \c db_a us_a
Password for user us_a:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "db_a" as user "us_a".
-- You see the changes done by main_admin:
us_a@db_a.localhost=> SELECT * FROM test;
id | tst
----+--------
1 | Blabla
2 | Blublu
(2 rows)
Bye
Charles
Now, if you have time for it, I would suggest that you take it to read
about the roles and privileges system in PostgreSQL. This will strongly
help you understanding what you are doing.
Charles
Thank you for the help. information, or an example!
DD
--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich
http://www.swisspug.org
+-----------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| PostgreSQL 1996-2016 |
| 20 Years of Success |
| |
+-----------------------+
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
