Обсуждение: Restricted access on DataBases
I think, it worth to try pg_hba.conf configuration[1]. [1]. https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html Regards, Amul Sul On Mon, Sep 5, 2016 at 6:15 PM, Durumdara <durumdara@gmail.com> wrote: > Dear PG-masters! > > We want to put more databases to one server, to "public" schema: > DB_A, DB_B, DB_C. > 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. > > 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. > > I can reown DB_A to US_A, but this revoke all rights from Main_Admin. > > 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... > > 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). > > 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 >
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
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; 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 | | | +-----------------------+
Hello, On Mon, 2016-09-05 at 14:45 +0200, Durumdara wrote: > Dear PG-masters! > > We want to put more databases to one server, to "public" schema: > DB_A, DB_B, DB_C. > 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. > > 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. > > I can reown DB_A to US_A, but this revoke all rights from Main_Admin. > > 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). > I'm not a pg_master. Do you mean multiple databases or multiple schemas? If you have multiple databases then main_admin would have to connect and disconnect over and over in order to look at each database. If you are setting this up for students to learn RDBMS skills, then wouldn't you be better off with a single database instance containing multiple schemas? Create all the roles with the necessary privileges, then:- CREATE SCHEMA db_a AUTHORIZATION us_a; GRANT ALL ON ALL TABLES IN SCHEMA db_a TO main_admin; Repeat as necessary for each schema/role combination. Then main_admin would have to prefix all tables, sequences, etc. with the schema name in order to run queries, etc. HTH, Rob
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 | | | +-----------------------+
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?
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;
Hello On 09/07/2016 03:24 PM, Durumdara wrote: > Dear Everybody! > > I read the documentation based on your example. First reactions. > 2016-09-05 18:25 GMT+02:00 Charles Clavadetscher > <clavadetscher@swisspug.org <mailto:clavadetscher@swisspug.org>>: > > > GRANT us_a, us_b, us_c TO main_admin; > > > > Ah, it's good. I can merge the "owner" rights to one. :-) > It's like "doubling"! :-) > > 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; > > > What could be the problem with the revoking only "connect" priv? What > are/would be the silent side-effects? None. Just before I go on answering your questions, a general statement from my side. If I understood you correctly you have a set of customers that each is owner of his database. Other customers are not supposed to look into his data. But for maintenance reasons you have a main_admin user that must have the same privileges as your various customers. So we have distinct databases, not distinct schemas in a single database. > For example: > Ok, us_b can't connect to db_a, but... > He can connect to db_b and may he can start(?) a multidatabase query... He can't, how should he? In order to access other databases you would need to set up foreign data wrappers and adjust the privileges on it. But for your use case there is no need for it. Even if the community would implement something like multidatabase query natively I would expect the privileges on the database to hold on. > He can set his role to bla, and he can insert the table db_a.X... If you are speaking still of us_b, well he can't. A user can only set a role he belongs to (see documentation). Now if you have a user bla that has access to db_a and you granted that role to us_b, well, yes he can, but this is your responsibility. PostgreSQL does not protect you from doing security design errors. > He can read the temp tables of db_a... Well, a temp table is usually created within a transaction, so no other users have access to them anyway. Besides they are created in the owner's database, so without connect, no way. > He can read the structure of db_a No. User us_b has access to the structure of db_b not db_a. This is defined on a database level and not global (like, e.g. roles). > He can break out from his sandbox by...??? AFAIK he can't. But maybe some other specialist will be able to build an attack vector to it. So far, I would say that you are on a pretty sound ground and that is due to a very clean implementation from the community. Charles > > --- > > Other question: > Can I imagine the GRANT as present, and the DEFAULT PRIVILEGES as future? > > Your two solutions are seem to be better like "revoke public in all and > grant all rights in all object in the present (GRANT) and in the future > (DEF. PRIV)". > > Very-very thank you! > > 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 | | | +-----------------------+
Hello I did oversee the additional questions... On 09/07/2016 06:45 PM, Charles Clavadetscher wrote: > Hello > > On 09/07/2016 03:24 PM, Durumdara wrote: >> Dear Everybody! >> >> I read the documentation based on your example. First reactions. >> 2016-09-05 18:25 GMT+02:00 Charles Clavadetscher >> <clavadetscher@swisspug.org <mailto:clavadetscher@swisspug.org>>: >> >> >> GRANT us_a, us_b, us_c TO main_admin; >> >> >> >> Ah, it's good. I can merge the "owner" rights to one. :-) >> It's like "doubling"! :-) >> >> 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; >> >> >> What could be the problem with the revoking only "connect" priv? What >> are/would be the silent side-effects? > > None. > > Just before I go on answering your questions, a general statement from > my side. If I understood you correctly you have a set of customers that > each is owner of his database. Other customers are not supposed to look > into his data. But for maintenance reasons you have a main_admin user > that must have the same privileges as your various customers. > > So we have distinct databases, not distinct schemas in a single database. > >> For example: >> Ok, us_b can't connect to db_a, but... >> He can connect to db_b and may he can start(?) a multidatabase query... > > He can't, how should he? In order to access other databases you would > need to set up foreign data wrappers and adjust the privileges on it. > But for your use case there is no need for it. Even if the community > would implement something like multidatabase query natively I would > expect the privileges on the database to hold on. > >> He can set his role to bla, and he can insert the table db_a.X... > > If you are speaking still of us_b, well he can't. A user can only set a > role he belongs to (see documentation). Now if you have a user bla that > has access to db_a and you granted that role to us_b, well, yes he can, > but this is your responsibility. PostgreSQL does not protect you from > doing security design errors. > >> He can read the temp tables of db_a... > > Well, a temp table is usually created within a transaction, so no other > users have access to them anyway. Besides they are created in the > owner's database, so without connect, no way. > >> He can read the structure of db_a > > No. User us_b has access to the structure of db_b not db_a. This is > defined on a database level and not global (like, e.g. roles). > >> He can break out from his sandbox by...??? > > AFAIK he can't. But maybe some other specialist will be able to build an > attack vector to it. > > So far, I would say that you are on a pretty sound ground and that is > due to a very clean implementation from the community. > > Charles > > >> >> --- >> >> Other question: >> Can I imagine the GRANT as present, and the DEFAULT PRIVILEGES as future? It depends. From the requirements that you submitted, you don't need to alter default privileges. Your single user, let's say us_a, can create objects, including schemas and then tables in that schema within db_a and your main_admin will be able to access them via the grant of role us_a to him. >> Your two solutions are seem to be better like "revoke public in all and >> grant all rights in all object in the present (GRANT) and in the future >> (DEF. PRIV)". No. You need to change the default privileges in other scenarios. If the explanations so far don't match your requirements, I may have misunderstood what you are trying to achieve. In that case send please a more clarifying use case. Charles >> >> Very-very thank you! >> >> 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 | | | +-----------------------+
The operation (overlord):
Hmmm... the owner of test_mainuser is mainuser...
Then I dropped the test_mainuser table.
I set them all.
ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES to dbuser;
I thought this makes all rights to the newly generated table.
On 09/14/2016 06:52 AM, Durumdara wrote: > Dear Charles! > > I checked your solution. For example: > db - database > dbuser, mainuser > > 1. dbuser own the database, and the objects in it. > 2. mainuser member of dbuser. > 3. public connection revoked. > > Ok. > > Then dbuser can see all tables, and mainuser too. > > Ok. > > The operation (overlord): > 1. set role to mainuser (or login). > 2. create table test_mainuser(id integer); > 3. set role to dbuser (or login). > 4. select * from test_mainuser; > > Result: Permission denied. > > Hmmm... the owner of test_mainuser is mainuser... > > Then I dropped the test_mainuser table. > > I tried to use default privileges. They are for future, so they must be > affected on newly created table. > I set them all. > > > ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, > TRUNCATE, REFERENCES, TRIGGER ON TABLES to dbuser; > > I thought this makes all rights to the newly generated table. > > I do the test again, but I got same result. > > Why? What I do wrong? (Maybe only my mistake). > > 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! Without seeing the actual GRANT commands you issued it will be difficult to sort out what the state of the permissions is. FYI, in psql you can use \dp or \z to see the privileges on a table. That information would also be helpful. > > Regards > dd > > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.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.klaver@aklaver.com
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:
-- login as su
-- CREATE DATABASE ct_db WITH OWNER = ex_dbuser ENCODING = 'UTF8' TABLESPACE = pg_default template = template0;
-- use ct_db
-- begin; create table t_dbuser (id integer);commit;
-- begin; create table t_mainuser (id integer); commit;
-- select * from t_mainuser; -- ERROR!
-- ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
-- begin; create table t_mainuser2 (id integer); commit;
-- select * from t_mainuser2; -- ERROR!
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
-- begin; create table t_mainuser3 (id integer); commit;
-- select * from t_mainuser3; -- ERROR!
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.
Hello
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Durumdara
Sent: Mittwoch, 14. September 2016 17:13
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
As Adrian said, it would help to know what you granted to whom.
Also it would help to know what the exact error message is that you get. In may give an idea if you forgot somewhere some privilege.
And last but not least it would help to know who is CURRENT_USER and in which DB you are before you execute a statement. I don’t mean what you think it is, but what is delivered by
SELECT SESSION_USER, CURRENT_USER;
SELECT current_database();
Since you are using a graphical tool and submit from some editor queries to the DB, I would not assume that you are sending the query to the right database with the correct user without checking it out.
Could you also provide the result of
\ddp
from a psql shell (you can open one from pgAdmin: click on the database you want to check, in the menu plugins->PSQL Console). This is a list of you custom default privileges.
Also try this:
ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects created by su and not ex_mainuser, unless you specify it with FOR ex_mainuser.
Besides, if the objects in the table will not be created by the owner, but by your admin, then I don’t very much see the point in giving ownership. That could be done anyway in the public schema, unless you changed that.
I have to leave now, but there are some more things that could be verified
Regards
Charles
2016-09-14 16:52 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.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.
Hello
Also try this:
ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects created by su and not ex_mainuser, unless you specify it with FOR ex_mainuser.
Besides, if the objects in the table will not be created by the owner, but by your admin, then I don’t very much see the point in giving ownership. That could be done anyway in the public schema, unless you changed that.
--- login with postgres:
CREATE DATABASE db_testrole
WITH ENCODING='UTF8'
TEMPLATE=template0
CONNECTION LIMIT=-1;CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE u_tr_main LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT u_tr_db TO u_tr_main;ALTER DATABASE db_testrole
OWNER TO u_tr_db;REVOKE ALL ON DATABASE db_testrole FROM public;
GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public;
GRANT ALL ON DATABASE db_testrole TO u_tr_db;
ALTER DEFAULT PRIVILEGES
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
TO u_tr_db;
create table t_canyouseeme_1 (k int);
select * from t_canyouseeme_1;
ERROR: permission denied for relation t_canyouseeme_1
SQL state: 42501
A.)
---- login with u_tr_main:set role u_tr_db;---- login with u_tr_db:
create table t_canyouseeme_2 (k int);
select * from t_canyouseeme_2; -- OK!B.)
---- login with su:ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db;---- login with u_tr_main:create table t_canyouseeme_3 (k int);---- login with u_tr_db:
select * from t_canyouseeme_3; -- OK!
ALTER DEFAULT PRIVILEGES
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
TO u_tr_db;
1. ALTER DEFAULT PRIVILEGES
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
TO u_tr_db;2. ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db;
db_testrole-# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-----------+--------+-------+-----------------------------
postgres | | table | postgres=arwdDxt/postgres +
| | | u_tr_db=arwdDxt/postgres
u_tr_main | | table | u_tr_db=arwdDxt/u_tr_main +
| | | u_tr_main=arwdDxt/u_tr_main
(2 rows)
db_testrole-# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+-----------
public | t_canyouseeme_1 | table | u_tr_main
public | t_canyouseeme_2 | table | u_tr_db
public | t_canyouseeme_3 | table | u_tr_main
(3 rows)
Durumdara wrote: [...] > --- login with postgres: [...] > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES > TO u_tr_db; > > ---- login with u_tr_main: > > create table t_canyouseeme_1 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_1; > > ERROR: permission denied for relation t_canyouseeme_1 > SQL state: 42501 > > As you see before, u_tr_db got all default privileges on future tables, so I don't understand why he > don't get to "t_canyouseeme_1". You should have written ALTER DEFAULT PRIVILEGES FOR ROLE u_tr_main ... The way you did it, you effectively wrote "FOR ROLE postgres" because you were connected as that user. Than means that all future tables created *by postgres* will have privileges for user "u_tr_db" added. But you want tables created *by u_tr_main* to get the privileges. Yours, Laurenz Albe
Hello > > Also try this: > > ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER > > ON TABLES TO ex_dbuser; > > > > You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects created by su and not > > ex_mainuser, unless you specify it with FOR ex_mainuser. > > > > So... I repeated the test. > > --- login with postgres: > > CREATE DATABASE db_testrole > WITH ENCODING='UTF8' > TEMPLATE=template0 > CONNECTION LIMIT=-1; > > CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; > > > CREATE ROLE u_tr_main LOGIN > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; > GRANT u_tr_db TO u_tr_main; > > > ALTER DATABASE db_testrole > OWNER TO u_tr_db; > > REVOKE ALL ON DATABASE db_testrole FROM public; > GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public; > GRANT ALL ON DATABASE db_testrole TO u_tr_db; > > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES > TO u_tr_db; Here you are telling PostgreSQL to grant those privileges to u_tr_db on tables created by user postgres. > ---- login with u_tr_main: > > create table t_canyouseeme_1 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_1; > > ERROR: permission denied for relation t_canyouseeme_1 > SQL state: 42501 > As you see before, u_tr_db got all default privileges on future tables, so I don't understand why he don't get to > "t_canyouseeme_1". This is not correct. You issued the ALTER DEFAULT PRIVILEGES statement as user postgres. So u_tr_db is granted privilegesonly on tables created by user postgres. Since you created the table as user u_tr_main the default privileges don'tapply, because there are none defined. > If I try to use these things they would work: > > A.) > > ---- login with u_tr_main: > > set role u_tr_db; > > create table t_canyouseeme_2 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_2; -- OK! Yes, because the owner of the table is u_tr_db. With set role user u_tr_main is impersonating user u_tr_db. > B.) > > ---- login with su: > > > ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, > REFERENCES, TRIGGER ON TABLES TO u_tr_db; Here you are telling PostgreSQL to grant privileges on tables created by u_tr_main to u_tr_db. > ---- login with u_tr_main: > > create table t_canyouseeme_3 (k int); > > ---- login with u_tr_db: > > select * from t_canyouseeme_3; -- OK! > > > A.) is because I can set role to u_tr_db and then he is the creator, he get all rights. > B.) I don't understand this statement... :-( :-( :-( > > So the main questions. > Why the default privilege settings aren't affected on newly created table? > See: > > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES > TO u_tr_db; They do if the user creating the table is the user that issued the statement. In the case above postgres. > What are the meaning of this statement if they won't usable for object created by another users? > U_TR_DB is owner, so they have all privileges for next tables he will create. > So I supposed that "default privileges" is for future objects created by different users. > But this not works here. > > I don't understand case B. > U_TR_MAIN gives all privileges to U_TR_DB for all newly created table? Yes. You may also choose to restrict the privileges, instead of granting all of them. > What are the differences between? > > 1. ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES > TO u_tr_db; > 2. ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, > TRIGGER ON TABLES TO u_tr_db; In 1 the rule apply for tables created by the user that created the default privileges. Specifically the current_user isthe one used for authorization checks. In 2 you say explicitly that the rule applies to tables created by user u_tr_main. > Why the second works and first not? They both work. In the first statement it works if you create tables as the user who was the current_user when you issuedthe alter default privileges statement. In the second it works if you create a table as user u_tr_main. > --- > > > db_testrole-# \ddp > Default access privileges > Owner | Schema | Type | Access privileges > -----------+--------+-------+----------------------------- > postgres | | table | postgres=arwdDxt/postgres + > | | | u_tr_db=arwdDxt/postgres > u_tr_main | | table | u_tr_db=arwdDxt/u_tr_main + > | | | u_tr_main=arwdDxt/u_tr_main > (2 rows) Here you see in different form what I already mentioned above. Bye Charles > > db_testrole-# \d > List of relations > Schema | Name | Type | Owner > --------+-----------------+-------+----------- > public | t_canyouseeme_1 | table | u_tr_main > public | t_canyouseeme_2 | table | u_tr_db > public | t_canyouseeme_3 | table | u_tr_main > (3 rows) > > > --- > > > > Thank you for your help! > > Best wishes > dd >
Durumdara wrote:
[...]
> --- login with postgres:
[...]
> ALTER DEFAULT PRIVILEGES
> GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
> TO u_tr_db;
>
> ---- login with u_tr_main:
>
> create table t_canyouseeme_1 (k int);
>
> ---- login with u_tr_db:
>
> select * from t_canyouseeme_1;
>
> ERROR: permission denied for relation t_canyouseeme_1
> SQL state: 42501
>
> As you see before, u_tr_db got all default privileges on future tables, so I don't understand why he
> don't get to "t_canyouseeme_1".
You should have written
ALTER DEFAULT PRIVILEGES FOR ROLE u_tr_main ...
The way you did it, you effectively wrote "FOR ROLE postgres" because
you were connected as that user.
Than means that all future tables created *by postgres* will have
privileges for user "u_tr_db" added. But you want tables created
*by u_tr_main* to get the privileges.
Yours,
Laurenz Albe