Klustron Users and Permissions Guide
Klustron Users and Permissions Guide
Note:
Unless otherwise specified, version numbers in this document can be replaced with the version numbers of any released version. For a list of all released versions, please refer to the Release Notes.
Document Objectives:
This article introduces, through examples, the implementation of Klustron's underlying table storage as well as the SQL syntax related to users and permissions. The examples provided in the article are for creating test users, databases, and corresponding schemas for a particular business test, and then granting appropriate permissions to those users.
The computing nodes of the Klustron cluster are deployed on the server named 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
The create user command is equivalent to create role, with one difference: create user has an implicit login privilege, allowing the user to log in to the database, whereas create role does not have this privilege.
Klustron's default pg_hba.conf (/kunlun/server_datadir/47001) file has added configuration content as follows (where trust means no password validation, and MD5 indicates that password validation is required):
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
This configuration indicates that local login on the computing nodes does not require password validation, while clients from other machines require password validation.
The format of the pg_hba.conf file is as follows:
# TYPE DATABASE USER ADDRESS METHOD
TYPE: Specifies the connection type, generally including local and host (for SSL connection types, refer to the documentation). local refers to a local Unix Socket connection, while host refers to connections from remote hosts or the localhost address on the local host.
DATABASE: Specifies the database to connect to, where all represents all databases, or you can use a specific database name, such as postgres.
USER: Specifies the username used for the connection, where all represents all users, or you can use a specific username, such as postgres.
ADDRESS: Specifies the source IP address of the client connecting to the database. 127.0.0.1/32 indicates that only connections from the local machine are allowed, 0.0.0.0/0 indicates connections from all IP addresses are allowed, and 192.168.40.0/24 indicates connections are allowed from the IP address range 192.168.40.1-192.168.40.255.
METHOD: Specifies the authentication method used during the connection. Common methods include trust, which means trusting all connections, and md5, which means the connecting client must provide an encrypted password to log in.
To begin, create a test user, "joe," on 192.168.40.152. Since the following rule is configured:
host all all 192.168.40.152/32 trust
This allows the use of the "kunlun" user without entering a password when logging in locally.
psql -h 192.168.40.152 -p 47001 postgres
create user joe with password 'joe123';
The connection type is "local," indicating that Unix Socket connections are allowed.
# "local" is for Unix domain socket connections only
local all all trust
Test using a Unix Socket connection without a password to log in to the "postgres" database.
[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=#
As an example, let's define a new connection rule that allows "joe" to log in from a client at 192.168.40.151 but does not allow login 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 simultaneously, the earlier rules will take precedence over the later ones.
After modifying the pg_hba.conf file, run the following command to apply the changes to the configuration file:
[kunlun@kunlun2 47001]$ pg_ctl reload -D /kunlun/server_datadir/47001
server signaled
Then, perform login tests:
On 192.168.40.151, "joe" can log in successfully.
[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
On 192.168.40.153, the login attempt is rejected.
[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 scenarios, it's recommended that users define the same user database connection rules in pg_hba.conf on multiple Klustron computing nodes for easier 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 the CREATEDB privilege. By default, new databases are created by cloning the standard system database template1. A different template can be specified by writing its name. Additionally, specifying template0 allows users to create a raw database that contains only the standard objects predefined by the PostgreSQL version. If a user wishes to avoid copying any locally added objects from template1, they can use the template0 template to create the database.
Note: Klustron's architecture design means 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 a test database named "kunlun_testing."
create database kunlun_testing with owner joe encoding utf8 template template0;
When creating the database, the template0 template is used, and the database's character set is utf8. The user "joe" has all privileges on the newly created database, "kunlun_testing."
03 Create Schema
In Klustron, data is organized into 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 thought of as a logical collection of database objects. The default schema created for each database is "public," but users can add additional schemas as needed for their business requirements.
A schema can contain views, indexes, data types, functions, operators, and more.
Objects with the same name can be used in different schemas without conflicts. For example, "schema1" and "schema2" can both contain a table named "kltest."
The advantages of using schemas include:
- Allowing multiple users to use a single database without interference.
- Organizing database objects into logical groups for easier management.
- Keeping objects from third-party applications in separate schemas to avoid naming conflicts with other objects.
Schemas are similar to directories in the operating system, but schemas cannot be nested.
To illustrate, log in to the "kunlun_testing" database, create a schema named "joe_testing," and set the default search_path for user "joe" to the "joe_testing" schema. This way, when the "joe" user's session operates on objects in the "joe_testing" schema, there is no need to explicitly prefix them with "joe_testing."
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;
Using the user "joe," connect to the "kunlun_testing" database, create a table named "kltest" in the "joe_testing" schema, and insert some test records.
\c - joe
create table kltest (id int);
insert into kltest select generate_series(1,100);
In Klustron databases, schemas are stored on the backend storage clusters, and each schema corresponds to a MySQL database that stores all the table shards.
In the example above, when a database named "kunlun_testing" is created on the computing node, and a schema named "joe_testing" is established within the database, a MySQL database named "kunlun_testing__joe_testing" is created in the underlying MySQL cluster. The naming convention is {database_name}__{schema_name}.
View information related to the storage nodes 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%';
Log in to "shard2."
mysql -h192.168.40.151 -P57005 -upgx -ppgx_pwd
show databases;
use kunlun_testing_$$_joe_testing;
show tables;
select count(*) from kltest;
When a user creates an unpartitioned table "kunlun_testing.joe_testing.kltest" on a computing node, the computing node automatically assigns a certain storage shard to store the "kltest" table. It creates a table with the same name in the MySQL database "kunlun_testing_$$_joe_testing" within that shard to store the data. Klustron also records in the computing node's metadata table that "kltest" data is stored on shard2.
This allows the computing node to automatically locate shard2 when reading and writing data, allowing it to 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 Grant and Revoke
Create another database user named "tom" and grant the "tom" user permission to query the "kltest" table.
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;
To access objects under a schema, users are subject to the following permission validation logic:
Is there "USAGE" permission on the schema?
No: User access is denied.
Yes: Does the user have permission for the corresponding table?
No: Access is denied.
Yes: Check permission to access the required fields.
Permission Explanations:
SELECT: Allows selecting any column or specific columns listed from a specified table, view, or sequence. Also allows the use of COPY TO. This permission is required when referencing existing column values in UPDATE or DELETE. For sequences, this permission also allows using the currval function. For large objects, this permission allows reading the object.
INSERT: Allows inserting new rows into the specified table. If specific columns are listed, only those columns can be assigned values in the INSERT command (other columns receive default values). Also allows COPY FROM.
UPDATE: Allows updating any column or specific columns listed in the specified table, requiring SELECT permission.
DELETE: Allows deleting rows from the specified table, requiring SELECT permission.
TRUNCATE: TRUNCATE TABLE has the same effect as DELETE, but it's faster because it doesn't actually scan the table. Additionally, TRUNCATE TABLE can immediately release table space without the need for subsequent VACUUM operations, which is useful for large tables.
TRIGGER: Allows creating triggers on the specified table (supported in version 1.2).
CREATE: For databases, allows creating new schemas, tables, and indexes within the database.
CONNECT: Allows users to connect to the specified database. This permission is checked at connection startup.
EXECUTE: Allows using the specified function or procedure, as well as executing functions within functions.
USAGE: For schemas, allows access to objects contained in the specified schema. For sequences, allows using the currval and nextval functions. For types and domains, allows using the type or domain when creating tables, functions, and other schema objects.
ALL PRIVILEGES: Grants all available permissions at once.
You can grant "tom" permission to operate on all tables under the "joe_testing" schema using the following statement:
grant select, insert, update, delete on all tables in schema joe_testing to tom;
You can query the current object permissions granted to the user "tom" using the following statement.
\c - tom;
set search_path=joe_testing;
\dp
After granting permissions to "tom," they can log in and query or modify the "kltest" table.
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 specific usage of the GRANT command to grant permissions, please refer to this link.
To revoke a user's permissions, you can use the REVOKE statement. The following statement revokes all operational permissions on tables under the "joe_testing" schema for the user "tom." After revoking the permissions, "tom" will no longer be able to manipulate records in the "joe_testing" tables.
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 specific usage of the REVOKE command to revoke permissions, please refer to this link.