Portaner api endpoints - sudipRW/portainer-postgresql GitHub Wiki

**# # Documentation for Portainer BoltDB to PostgreSQL Migration*** This document outlines the modifications and code implementations made to transition Portainer’s data storage from BoltDB to PostgreSQL. The changes include a new transaction management system, adjustments to various methods to support PostgreSQL’s relational model, and updates to API endpoints to incorporate CSRF token handling. PostgreSQL Transaction Management Code Package: ‘postgres` The `postgres` package was introduced to handle interactions with the PostgreSQL database. It contains the `DbTransaction` struct and various methods for CRUD operations. Below is a summary of the implemented methods: Struct: `DbTransaction` // DbTransaction handles database transactions.

type DbTransaction struct {

conn *DbConnection
tx   *sql.Tx
ctx  context.Context

}

SetServiceName(bucketName string) error Creates a new table if it doesn’t already exist, mimicking BoltDB’s bucket system:

CREATE TABLE IF NOT EXISTS {bucketName} (

id TEXT PRIMARY KEY,
data JSONB NOT NULL

)

GetObject(bucketName string, key []byte, object any) error Retrieves a JSON object from the specified table using the key. Handles keys as integers or strings depending on the bucket name:

  • Converts binary keys to strings using ‘binary.BigEndian.Uint64`.

  • Unmarshals JSON data into the provided ‘object`.

UpdateObject(bucketName string, key []byte, object any) error Updates or inserts a JSON object into the specified table:

  • Checks if the key exists using ‘SELECT id FROM {bucketName}`.

  • Inserts or updates the record using ‘INSERT` or `UPDATE` queries.

DeleteObject(bucketName string, key []byte) error Deletes a record by key using: DELETE FROM {bucketName} WHERE id = $1

DeleteAllObjects(bucketName string, obj any, matchingFn func(o any) (id int, ok bool)) error Deletes all objects matching a criteria:

  • Fetches all records from the table.

  • Uses a user-defined matching function to filter objects for deletion.

GetNextIdentifier(bucketName string) int Generates the next identifier by finding the maximum ID in the table and adding one: SELECT COALESCE(CAST(MAX(CAST(id AS INTEGER)) AS INTEGER), 0) + 1 FROM {bucketName}

CreateObject(bucketName string, fn func(uint64) (int, any)) error Creates a new object with an auto-generated sequence ID:

  • Calls the user-provided function ‘fn` to generate the object.

  • Inserts the object into the table.

CreateObjectWithId(bucketName string, id int, obj any) error Inserts a new object with a specified integer ID: INSERT INTO {bucketName} (id, data) VALUES ($1, $2)

CreateObjectWithStringId(bucketName string, id []byte, obj any) error Inserts a new object with a specified string ID.

GetAll(bucketName string, obj any, appendFn func(o any) (any, error)) error Fetches all objects from a table and appends them using a user-defined function: SELECT data FROM {bucketName}

GetAllWithKeyPrefix(bucketName string, keyPrefix []byte, obj any, appendFn func(o any) (any, error)) error Fetches all objects with keys matching a specified prefix: SELECT data FROM {bucketName} WHERE id LIKE $1

API Endpoints with CSRF Token Support All endpoints now send a CSRF token in response headers to enhance security. Below is the list of updated API endpoints: System and Authentication Endpoints • GET localhost:9443/api/system/status - Generates the initial CSRF token in the response header. • POST localhost:9443/api/auth - Authenticates the user and returns a JWT token. Endpoint Management • GET localhost:9443/api/endpoints - Fetches all endpoints. • GET localhost:9443/api/endpoints/{id} - Fetches a specific endpoint. • POST localhost:9443/api/endpoints - Creates a new endpoint. • PUT localhost:9443/api/endpoints/{id} - Updates an existing endpoint. • DELETE localhost:9443/api/endpoints/{id} - Deletes an endpoint. Endpoint Group Management • GET localhost:9443/api/endpoint_groups - Fetches all endpoint groups. • GET localhost:9443/api/endpoint_groups/{id} - Fetches a specific endpoint group. • POST localhost:9443/api/endpoint_groups - Creates a new endpoint group. • PUT localhost:9443/api/endpoint_groups/{id} - Updates an endpoint group. • DELETE localhost:9443/api/endpoint_groups/{id} - Deletes an endpoint group. Tag Management • GET localhost:9443/api/tags - Fetches all tags. • POST localhost:9443/api/tags - Creates a new tag. • DELETE localhost:9443/api/tags/{id} - Deletes a tag. Additional Endpoints • GET localhost:9443/api/stacks - Fetches all stacks. • POST localhost:9443/api/stacks - Creates a new stack. • GET localhost:9443/api/stacks/{id} - Fetches a specific stack. • PUT localhost:9443/api/stacks/{id} - Updates an existing stack. • DELETE localhost:9443/api/stacks/{id} - Deletes a stack. • GET localhost:9443/api/users - Fetches all users. • POST localhost:9443/api/users - Creates a new user. • GET localhost:9443/api/users/{id} - Fetches a specific user. • PUT localhost:9443/api/users/{id} - Updates an existing user. • DELETE localhost:9443/api/users/{id} - Deletes a user. Future Additions This document will be updated with additional details as further changes are made to support PostgreSQL integration in Portainer.

⚠️ **GitHub.com Fallback** ⚠️