KlustronDB User and Permission Guide

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release_notes

Overview

This article introduces the implementation of the bottom-level storage of KlustronDB tables through examples, as well as SQL syntax related to users and permissions. The examples in the text involve creating test users, databases, and corresponding schemas for a certain business test, and granting appropriate permissions to the users.

The Kluscomp instances of the KlustronDB cluster are deployed on the server kunlun2 (192.168.40.152).

01 Create User

The syntax for creating a user is as follows:

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

create user command is equivalent to create role, except for one point: create user has implicit login privileges and can log into the database, while create role does not have this privilege.

KlustronDB's default pg_hba.conf (located in /kunlun/server_datadir/47001) file has the following added configuration content (trust does not verify passwords, MD5 indicates that passwords need to be verified)

host all all 192.168.40.152/32 trust
host all all 127.0.0.1/32  trust
host all agent 0.0.0.0/0 reject
host all all 0.0.0.0/0 md5

Indicates that logging in to the Kluscomp instance locally does not require a password, while clients from other machines do require password verification.

pg_hba.conf The file format is as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

TYPE: Refers to the connection type, generally there are two types: local and host (for SSL type connections, you can refer to the documentationopen in new window). 'Local' refers to a local Unix Socket connection, while 'host' refers to a connection from a remote host or the localhost address of the local machine.

DATABASE: refers to the database to connect to; 'all' means all, and you can also use a specific database name, such as postgres.

USER: Refers to the username used for connecting. 'all' represents all users, and you can also use a specific username, such as 'postgres'.

ADDRESS: Refers to the source of the client IP address connecting to the database. 127.0.0.1/32 means that only connections from the local machine itself are allowed; 0.0.0.0/0 means that connections from all IPs are allowed; 192.168.40.0/24 means that IP addresses in the range 192.168.40.1-192.168.40.255 are allowed to connect.

METHOD: Indicates the authentication method used when connecting. Common ones include trust, which means trusting all connections, and md5, which means the client attempting to connect must provide an encrypted password to log in.

First, create a test user joe on 192.168.40.152, because the following rules are configured

host all all 192.168.40.152/32 trust

So allow the kunlun user to log in on this machine without entering a password

psql -h 192.168.40.152 -p 47001 postgres
create user joe with password 'joe123';

A connection type of 'local' indicates that using a Unix Socket connection is allowed.

# "local" is for Unix domain socket connections only
local   all             all                                     trust

Test logging into the Postgres database without a password using Unix Socket.

[kunlun@kunlun2 47001]$ psql -h /kunlun/server_datadir/47001 -p 47001 postgres
psql (Kunlun-1.1.1 on x86_64-pc-linux-gnu, 64-bit)
Type "help" for help.

postgres=#

For example, define a new connection rule that allows joe to log in to the database from the client 192.168.40.151, but does not allow him to log in from 192.168.40.153. Add the following rules to pg_hba.conf.

# host all all 0.0.0.0/0 md5
host all joe 192.168.40.153/32 reject
host all joe 192.168.40.151/32 md5

Note: If multiple rules in pg_hba.conf match, the earlier rules override the later ones.

After modifying the pg_hba.conf file, you also need to run the following command to make the changes to the configuration file take effect:

[kunlun@kunlun2 47001]$ pg_ctl reload -D /kunlun/server_datadir/47001
server signaled

Then perform a login test:

On 151, Joe can log in normally.

[kunlun@kunlun1 ~]$ psql -h 192.168.40.152 -p 47001 -U joe postgres
Password for user joe: 
psql (Kunlun-1.1.1 on x86_64-pc-linux-gnu, 64-bit)
Type "help" for help.

postgres=> quit

Logging in on 153 is denied.

[kunlun@kunlun3 ~]$ psql -h 192.168.40.152 -p 47001 -U joe postgres
psql: FATAL:  pg_hba.conf rejects connection for host "192.168.40.153", user "joe", database "postgres", SSL off

In actual business operations, it is recommended that users define the same database connection rules for users in the pg_hba.conf of multiple KlustronDB Kluscomp instances, so as to facilitate user connection management.

02 Create Database

The syntax for creating a database is as follows:

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

The user executing the create database command must be a superuser or have CREATEDB privileges. By default, a new database is created by cloning the standard system database template1. A different template can be specified by writing the template name. In addition, by specifying template0, the user can create a pristine database containing only the standard objects predefined by PostgreSQL. If the user wishes to avoid copying any locally installed objects that may have been added to template1, the database can be created using template0.

Note: Due to the architectural design of KlustronDB, it does not support the create tablespace syntax.

postgres=# create tablespace test location '/kunlun/server_datadir/47001';
ERROR:  Statement 'CREATE TABLESPACE' is not supported in Kunlun.

Create test database kunlun_testing

create database kunlun_testing with owner joe encoding utf8 template template0;

When creating the database, the template template0 was used; the database's character set is utf8, and the user joe has all privileges on the newly created database kunlun_testing.

03 Create Schema

In KlustronDB, the distribution of data has three levels: database, schema, and user data (including tables, indexes, stored procedures, etc.). A database can contain any number of schemas.

A schema can be seen as a logical collection of database objects. After a database is created, the default schema is public, and users can also add more schemas at any time according to business needs.

A schema can contain views, indexes, data types, functions, and operators.

The same object name can be used in different schemas without conflict; for example, schema1 and schema2 can both contain a table named kltest.

The advantages of using patterns are:

Allows multiple users to use a database without interfering with each other.
Organize database objects into logical groups for easier management.
Objects of third-party applications can be placed in a separate schema, so they will not conflict with the names of other objects.

The model is similar to a directory at the operating system level, but the model cannot be nested.

Log in to the kunlun_testing database, create a schema named joe_testing, and then set the default search_path of user joe to the joe_testing schema, so that in joe's user session, when operating objects in the joe_testing schema, there is no need to explicitly add the 'joe_testing' prefix.

psql -h 192.168.40.153 -p 47001 -U joe kunlun_testing
create schema joe_testing;
alter user joe set search_path to joe_testing;

Use the user joe to connect to the kunlun_testing database, then create the table kltest under the schema joe_testing, and insert some test records.

\c - joe
create table kltest (id int);
insert into kltest select generate_series(1,100);

In the KlustronDB database, the schemas in the backend storage shard each correspond to a MySQL database to store all the table shards.

In the example above, in the Kluscomp instance there is a database named kunlun_testing, and a schema joe_testing is created in the database. Then, the database created in the underlying MySQL cluster is named kunlun_testing_$$joe_testing. The naming rule is {database_name}$$_{schema_name}.

View information related to the Klustore instances where the kltest table is stored

psql -h 192.168.40.152 -p 47001 kunlun_testing
select relname table_name, name shard_name, hostaddr host from pg_class t1,pg_shard t2,pg_shard_node t3 where t1.relshardid = t2.id and t2.id = t3.id and t1.relname like '%test%';

mysql -h192.168.40.151 -P57005 -upgx -ppgx_pwd
show databases;

use kunlun_testing_$$_joe_testing;
show tables;

select count(*) from kltest;

The user creates a non-partitioned table kunlun_testing.joe_testing.kltest on a Kluscomp instance. The Kluscomp instance will automatically allocate a storage shard to store the table kltest and create a table with the same name in the MySQL database kunlun_testing_$$_joe_testing on that shard to store the data. Additionally, KlustronDB will record in the Kluscomp instance's metadata table that the data of kltest is stored on shard2.

In this way, when the Kluscomp instance reads and writes data, the Kluscomp instance can automatically locate the Klustore instance shard2 of kltest, and then interact with shard2 to read and write data in the kunlun_testing.joe_testing.kltest table.

psql -h 192.168.40.152 -p 47001 kunlun_testing joe
select count(*) from kltest;

04 User Permission Granting and Revocation

Create another database user Tom, and grant user Tom the permission to query the table kltest.

psql -h 192.168.40.152 -p 47001 -U kunlun_testing
grant usage on schema joe_testing to tom;
grant select on joe_testing.kltest to tom;

The user needs the following permission verification logic to access objects in read mode:

Is there a patterned 'USAGE'

No: User access denied

Yes: Whether the user has permission for the corresponding table

No: Access Denied

Yes: Check permissions for accessing the field

Permission Description:

SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许

用COPY TO。在UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。

 

INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。

 

UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。

 

DELETE：允许删除指定表中的行，需要SELECT权限。

 

TRUNCATE：TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大表上非常有用。

 

TRIGGER：允许在指定的表上创建triggers。 (1.2版本支持)

 

CREATE：对于数据库，允许在数据库中创建新的schema、table、index。

 

CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。

 

EXECUTE：允许使用指定的函数或过程以及在函数。

 

USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。

 

ALL PRIVILEGES：一次授予所有可用权限。

You can use the following statement to grant Tom all privileges on the tables under joe_testing.

grant select, insert, update, delete on all tables in schema joe_testing to tom;

You can query the objects that the user Tom currently has permissions for using the following statement.

\c - tom;
set search_path=joe_testing;
\dp

After granting Tom the permissions, Tom can log in and query and modify the table kltest.

psql -h 192.168.40.152 -p 47001 -U tom kunlun_testing
search_path=joe_testing;
select * from kltest limit 10;
insert into kltest values(101);
delete from kltest where id=100;

For detailed usage of the GRANT command for granting permissions, please refer to linkopen in new window.

Use the REVOKE statement to revoke a user's privileges. The following statement revokes all table operation privileges of tom under the joe_testing schema. After revoking the privileges, tom will no longer be able to operate on the records in the tables of joe_testing.

psql -h 192.168.40.152 -p 47001 kunlun_testing
revoke select, insert, update, delete on all tables in schema joe_testing from tom;
\c - tom
set search_path=joe_testing;
select * from kltest limit 10;
ERROR: permission denied for table kltest

For the specific usage of the REVOKE command to withdraw permissions, please refer to linkopen in new window.

KlustronDB User and Permission Guide

KlustronDB User and Permission Guide

** Overview **

01 Create User

02 Create Database

03 Create Schema

04 User Permission Granting and Revocation

END

Overview