You should know the security of data warehouse — the default permission implements the shared schema

Time:2021-12-20

Absrtact: a typical customer scenario is that some users are data producers and need to create tables and write data in the schema; Other users are data consumers and read the data in the schema for analysis. The permission management of this shared schema can be realized by using alter default privilege syntax. A simple example demonstrates the details and effectiveness of the alter default privilege syntax in dealing with this typical scenario.

preface

Recently, I encountered a customer scenario involving the permission of sharing schema. The scenario can be described as follows: some users are data producers and need to create tables and write data in the schema; Other users are data consumers and read the data in the schema for analysis. One implementation method of schema permission management is that the data producer informs the administrator that the user uses grant select on all tables in schema syntax to grant permissions to the consumer after each new table is created. This method has some limitations. If the manufacturer creates some new tables under the schema, in order to authorize the consumer to use these new tables, you also need to inform the administrator that the user can use grant select on all tables in schema again. Is there a simple solution? The answer is yes. You can use alter default privilege. Alter default privilege is used to grant or reclaim permissions for objects created in the future.

You should know the security of data warehouse -- the default permission implements the shared schema

Grammar introduction

ALTER DEFAULT PRIVILEGES

 [ FOR { ROLE | USER } target_role [, ...] ]
 [ IN SCHEMA schema_name [, ...] ]
 abbreviated_grant_or_revoke;

Of which abreviated_ grant_ or_ The revoke clause is used to specify which objects are authorized or reclaimed. The authorization syntax for a table is:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES }

 [, ...] | ALL [ PRIVILEGES ] }
 ON TABLES 
 TO { [ GROUP ] role_name | PUBLIC } [, ...]

Parameter description

  • target_role
    The name of an existing role. If for role / user is omitted, the default value is the current role / user.
    Value range: the name of an existing role.
  • schema_name
    Name of the existing schema.
    target_ Role must have schema_ Create permission for name.
    Value range: the name of the existing mode.
  • role_name
    The name of the role to which permission is granted or revoked.
    Value range: existing role name.

See detailshttps://support.huaweicloud.com/devg-dws/dws_04_0241.html

Scenario example

testdb=# create user creator1 password ‘Gauss_234’;
CREATE USER
testdb=# create user creator2 password ‘Gauss_234’;
CREATE ROLE
testdb=# create user user1 password ‘Gauss_234’;
Create user — create a shared schema, grant creater1 and creater2 the create permission, and grant user1 the use permission
testdb=# create schema shared_schema;
CREATE SCHEMA
testdb=> grant create, usage on schema shared_schema to creator1;
GRANT
testdb=> grant create, usage on schema shared_schema to creator2;
GRANT
testdb=# grant usage on schema shared_schema to user1;
Grant — set creater1 and creater2 in shared_ The select permission to create a table in the schema is granted to user1
testdb=# alter default privileges for user creator1, creator2 in schema shared_schema grant select on tables to user1;
Alter default privileges — switch to creater1 and create the table
testdb=# c testdb creator1
You are now connected to database “testdb” as user “creator1”.
testdb=> create table shared_schema.t1 (c1 int);
Create table — switch to creater2 to create a table
testdb=> c testdb creator2
You are now connected to database “testdb” as user “creator2”.
testdb=> create table shared_schema.t2 (c1 int);
Create table — switch to user1 and query OK
testdb=> c testdb user1
You are now connected to database “testdb” as user “user1”.
testdb=> select from shared_schema.t1 union select from shared_schema.t2;
c1 —- (0 rows)

View the status of granting default permissions

Query system table PG_ default_ ACL can view which schemas are currently granted default permissions. From the defalacl field, you can see that creater1 and creater2 grant user1 to shared respectively_ Select permission of objects in schema (R means read).

testdb=# select r.rolname, n.nspname, a.defaclobjtype, a.defaclacl from testdb-# pg_default_acl a, pg_roles r, pg_namespace n
testdb-# where a.defaclrole=r.oid and a.defaclnamespace=n.oid;
rolname | nspname | defaclobjtype | defaclacl ———-+—————+—————+——————– creator1 | shared_schema | r | {user1=r/creator1}
creator2 | shared_schema | r | {user1=r/creator2}
(2 rows)

Some details

All users who create objects in the shared schema should appear in the_ alter default privileges for user_ In the following list. Otherwise, if a user creater3 is not in the list, the objects created in the shared schema or those whose owner is creater3 will not be queried by user1. Because the table created by the creater3 user in the shared schema does not grant user1 the default permission.

testdb=# create user creator3 password ‘Gauss_234’;
CREATE USER
testdb=# grant create, usage on schema shared_schema to creator3;
GRANT
testdb=# c testdb creator3
You are now connected to database “testdb” as user “creator3”.
testdb=> create table shared_schema.t3 (c1 int);
CREATE TABLE
testdb=> c testdb user1
You are now connected to database “testdb” as user “user1”.
testdb=> select * from shared_schema.t3;
ERROR: permission denied for relation t3

Administrators can_ alter default privileges for user_ Put creater3 in the list and grant user1 the default permission to access the table created by the creater3 user, or the creater3 user can use it by himself_ alter default privileges_ Authorize to user1 In the previous syntax parameter description, if for role / user is omitted, the default value is the current user.

testdb=> c testdb creator3
You are now connected to database “testdb” as user “creator3”.
testdb=> alter default privileges in schema shared_schema grant select on tables to user1;
ALTER DEFAULT PRIVILEGES
testdb=> c testdb user1
You are now connected to database “testdb” as user “user1”.
testdb=> select * from shared_schema.t3;
ERROR: permission denied for relation t3
testdb=> c testdb creator3
testdb=> create table shared_schema.t4 (c1 int);
CREATE TABLE
testdb=> c testdb user1
You are now connected to database “testdb” as user “user1”.
testdb=> select * from shared_schema.t4;
c1 —- (0 rows)

The third line of the above code shows that the current user is in shared_ The select permission of the table created under schema is granted to user1. In line 7, user1 queries shared_ schema. T3 report authority is insufficient because_ alter default privileges_ Only future objects are processed. shared_ schema. T3 was created before. Let’s create a new table shared_ schema. T4, user1 user query is normal.

If you want to process permissions for an existing table, use_ grant_ sentence. Seehttps://support.huaweicloud.com/devg-dws/dws_04_0334.html

testdb=> c testdb creator3
You are now connected to database “testdb” as user “creator3”.
testdb=> grant select on all tables in schema shared_schema to user1;
ERROR: permission denied for relation t1
testdb=> grant select on table shared_schema.t3 to user1;
GRANT
testdb=> c testdb user1
You are now connected to database “testdb” as user “user1”.
testdb=> select * from shared_schema.t3;
c1 —- (0 rows)

Shared in line 3 of the code_ The schema contains three tables created by users, and creator 3 is only the owner of table T3. Therefore, an error will be reported when the permission is granted to the entire schema. After only the table T3 with creator 3 as the owner is granted, the query of user1 user is normal.

summary

_ alter default privileges_ Only future objects are processed_ grant_ Only existing objects are processed. Further, the objects involved in granting permissions by these two grammars only include the object whose owner is the current user. If you want to grant permissions to the objects of all owners under the shared schema, you need to use the administrator user_ alter default privileges for user_ Grammar and_ grant_ Grammar.

This article is shared from the Huawei cloud community “data warehouse security you should know – default permission implementation sharing schema”, by Zhang kunhn

Click focus to learn about Huawei cloud’s new technologies for the first time~