Provider PostgreSQL

https://github.com/tagesjump/provider-postgresql
Admin
Provider PostgreSQL is a Crossplane provider built with Upjet that exposes XRM-conformant managed...
Tokens:5,507
Snippets:59
Trust Score:5.2
License:Apache-2.0
Update:4 months ago
Show doc for...
Tokens:
Raw
### Complete Application Environment Setup

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

A comprehensive example demonstrating database creation, role management, and schema policy definition.

```yaml
# 1. Create the application database
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Database
metadata:
  name: ecommerce-db
spec:
  forProvider:
    name: ecommerce
    owner: postgres
    encoding: UTF8
    lcCollate: en_US.UTF-8
    lcCtype: en_US.UTF-8
  providerConfigRef:
    name: default
---
# 2. Create application roles
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Role
metadata:
  name: ecommerce-admin
spec:
  forProvider:
    name: ecommerce_admin
    login: true
    createDatabase: false
    passwordSecretRef:
      key: password
      name: ecommerce-admin-secret
      namespace: crossplane-system
  providerConfigRef:
    name: default
---
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Role
metadata:
  name: ecommerce-app
spec:
  forProvider:
    name: ecommerce_app
    login: true
    connectionLimit: 50
    passwordSecretRef:
      key: password
      name: ecommerce-app-secret
      namespace: crossplane-system
  providerConfigRef:
    name: default
---
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Role
metadata:
  name: ecommerce-readonly
spec:
  forProvider:
    name: ecommerce_readonly
    login: true
    passwordSecretRef:
      key: password
      name: ecommerce-readonly-secret
      namespace: crossplane-system
  providerConfigRef:
    name: default
---
# 3. Create application schema
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Schema
metadata:
  name: ecommerce-schema
spec:
  forProvider:
    name: ecommerce
    database: ecommerce
    owner: ecommerce_admin
    policy:
      - role: ecommerce_app
        usage: true
        create: false
      - role: ecommerce_readonly
        usage: true
        create: false
      - role: ecommerce_admin
        usageWithGrant: true
        createWithGrant: true
  providerConfigRef:
    name: default
```

--------------------------------

### Install Provider via CLI

Source: https://github.com/tagesjump/provider-postgresql/blob/main/README.md

Use the Crossplane CLI to install the provider package.

```bash
up ctp provider install xpkg.upbound.io/tages/provider-postgresql:v0.1.0
```

--------------------------------

### Install Provider PostgreSQL

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Methods for installing the provider using the Upbound CLI or a Kubernetes manifest.

```bash
# Install using the Upbound CLI
up ctp provider install xpkg.upbound.io/tages/provider-postgresql:v0.1.0
```

```yaml
# Declarative installation via Kubernetes manifest
apiVersion: pkg.crossplane.io/v1
kind: Provider
metadata:
  name: provider-postgresql
spec:
  package: xpkg.upbound.io/tages/provider-postgresql:v0.1.0
```

--------------------------------

### Install PostgreSQL Extension

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Installs the 'uuid-ossp' extension on the 'ecommerce' database. Ensure the 'default' provider configuration is set up.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Extension
metadata:
  name: ecommerce-uuid
spec:
  forProvider:
    name: uuid-ossp
    database: ecommerce
  providerConfigRef:
    name: default
```

--------------------------------

### Install Provider via Declarative YAML

Source: https://github.com/tagesjump/provider-postgresql/blob/main/README.md

Apply a Kubernetes Provider resource to install the package declaratively.

```yaml
cat <<EOF | kubectl apply -f -
apiVersion: pkg.crossplane.io/v1
kind: Provider
metadata:
  name: provider-postgresql
spec:
  package: xpkg.upbound.io/tages/provider-postgresql:v0.1.0
EOF
```

--------------------------------

### Install PostGIS extension

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Installs the PostGIS extension, enabling geospatial capabilities in the database. The 'createCascade' option ensures dependent objects are also created.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Extension
metadata:
  name: postgis
spec:
  forProvider:
    name: postgis
    database: geo_db
    schema: public
    createCascade: true
  providerConfigRef:
    name: default
```

--------------------------------

### Install uuid-ossp extension with version

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Installs the uuid-ossp extension, specifying a version and controlling cascade behavior for creation and dropping. Ensure the version is compatible.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Extension
metadata:
  name: uuid-ossp
spec:
  forProvider:
    name: uuid-ossp
    database: my_db
    schema: public
    version: "1.1"
    createCascade: true
    dropCascade: false
  providerConfigRef:
    name: default
```

--------------------------------

### Publish all tables for replication

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Creates a publication named 'full_publication' that includes all tables in the database and allows INSERT, UPDATE, DELETE, and TRUNCATE operations to be replicated. This is a comprehensive replication setup.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Publication
metadata:
  name: full-publication
spec:
  forProvider:
    name: full_publication
    database: my_db
    allTables: true
    publishParam:
      - insert
      - update
      - delete
      - truncate
  providerConfigRef:
    name: default
```

--------------------------------

### Install pg_trgm extension

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Installs the pg_trgm extension in a specified database and schema. This extension provides trigram-based text similarity measures.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Extension
metadata:
  name: pg-trgm
spec:
  forProvider:
    name: pg_trgm
    database: my_db
    schema: public
  providerConfigRef:
    name: default
```

--------------------------------

### Development Commands

Source: https://github.com/tagesjump/provider-postgresql/blob/main/README.md

Standard Makefile targets for development, building, and deployment.

```makefile
make run
```

```makefile
make all
```

```makefile
make build
```

--------------------------------

### Create PostgreSQL Subscription

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Configures a subscription to receive logical replication streams from a publisher.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Subscription
metadata:
  name: app-subscription
spec:
  forProvider:
    name: app_subscription
    database: replica_db
    publications:
      - app_publication
    conninfoSecretRef:
      key: connection_string
      name: publisher-connection
      namespace: crossplane-system
    createSlot: true
    slotName: app_subscription_slot
  providerConfigRef:
    name: default
```

--------------------------------

### Configure Foreign Data Wrapper Server

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Sets up a foreign server to access external data sources via PostgreSQL foreign data wrappers.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Server
metadata:
  name: remote-postgres
spec:
  forProvider:
    serverName: remote_postgres_server
    fdwName: postgres_fdw
    serverType: postgres
    serverVersion: "15"
    options:
      host: "remote.postgresql.example.com"
      port: "5432"
      dbname: "remote_db"
    serverOwner: postgres
    dropCascade: false
  providerConfigRef:
    name: default
```

--------------------------------

### Run Code Generation

Source: https://github.com/tagesjump/provider-postgresql/blob/main/README.md

Execute the generator pipeline to update provider code.

```bash
go run cmd/generator/main.go "$PWD"
```

--------------------------------

### Manage PostgreSQL Roles

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Create roles with specific privileges, password references, and security constraints.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Role
metadata:
  name: app-user
spec:
  forProvider:
    name: app_user
    login: true
    passwordSecretRef:
      key: password
      name: app-user-secret
      namespace: crossplane-system
    connectionLimit: 10
    createDatabase: false
    createRole: false
    inherit: true
    replication: false
    bypassRowLevelSecurity: false
    superuser: false
    validUntil: "2025-12-31T23:59:59Z"
    roles:
      - readonly_role
    searchPath:
      - "$user"
      - public
    statementTimeout: 30000
  providerConfigRef:
    name: default
---
# Role with replication privileges for streaming replication
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Role
metadata:
  name: replication-user
spec:
  forProvider:
    name: replication_user
    login: true
    replication: true
    passwordSecretRef:
      key: password
      name: replication-secret
      namespace: crossplane-system
  providerConfigRef:
    name: default
```

--------------------------------

### Manage PostgreSQL Schema

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Define a schema with owner settings and access policies for specific roles.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Schema
metadata:
  name: app-schema
spec:
  forProvider:
    name: app_schema
    database: my_db
    owner: postgres
    ifNotExists: true
    dropCascade: false
    policy:
      # Grant usage to app_user
      - role: app_user
        usage: true
        create: false
      # Grant full access to app_admin
      - role: app_admin
        usage: true
        create: true
      # Grant with grant option to dba
      - role: dba
        usageWithGrant: true
        createWithGrant: true
  providerConfigRef:
    name: default
```

--------------------------------

### Publish specific tables for replication

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Creates a publication named 'app_publication' that includes specific tables (users, orders, products) and allows INSERT, UPDATE, and DELETE operations to be replicated. 'publishViaPartitionRootParam' is set to false.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Publication
metadata:
  name: app-publication
spec:
  forProvider:
    name: app_publication
    database: my_db
    tables:
      - public.users
      - public.orders
      - app_schema.products
    publishParam:
      - insert
      - update
      - delete
    publishViaPartitionRootParam: false
  providerConfigRef:
    name: default
```

--------------------------------

### Manage PostgreSQL Database

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Define a database resource with specific encoding, collation, and connection settings.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Database
metadata:
  name: my-db
spec:
  forProvider:
    name: my_db
    owner: my_role
    encoding: UTF8
    lcCollate: en_US.UTF-8
    lcCtype: en_US.UTF-8
    template: template0
    allowConnections: true
    connectionLimit: 100
    isTemplate: false
    tablespaceName: pg_default
  providerConfigRef:
    name: default
```

--------------------------------

### Configure ProviderConfig

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Define authentication credentials using a Kubernetes Secret and link them to the provider configuration.

```yaml
# Create a secret with PostgreSQL connection string
apiVersion: v1
kind: Secret
metadata:
  name: postgresql-creds
  namespace: crossplane-system
type: Opaque
stringData:
  credentials: |
    {
      "host": "postgresql.example.com",
      "port": 5432,
      "username": "postgres",
      "password": "secretpassword",
      "sslmode": "require"
    }
---
# Configure the provider to use the credentials
apiVersion: postgresql.upbound.io/v1beta1
kind: ProviderConfig
metadata:
  name: default
spec:
  credentials:
    source: Secret
    secretRef:
      name: postgresql-creds
      namespace: crossplane-system
      key: credentials
```

--------------------------------

### Create function with multiple arguments and OUT parameter

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Defines a PL/pgSQL function 'calculate_order_total' that accepts an order ID and a boolean for tax inclusion, returning the calculated total. It uses an OUT parameter and has a default value for 'include_tax'.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Function
metadata:
  name: calculate-order-total
spec:
  forProvider:
    name: calculate_order_total
    database: my_db
    schema: app_schema
    language: plpgsql
    arg:
      - name: order_id
        type: integer
        mode: IN
      - name: include_tax
        type: boolean
        mode: IN
        default: "true"
      - name: total
        type: numeric
        mode: OUT
    body: |
      BEGIN
          SELECT CASE
              WHEN include_tax THEN SUM(quantity * price * 1.1)
              ELSE SUM(quantity * price)
          END INTO total
          FROM order_items
          WHERE order_items.order_id = calculate_order_total.order_id;
      END;
    volatility: STABLE
    parallel: RESTRICTED
    securityDefiner: false
    dropCascade: false
  providerConfigRef:
    name: default
```

--------------------------------

### Manage Replication Slots

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Defines logical replication slots for WAL retention, supporting different plugins like pgoutput or test_decoding.

```yaml
# Logical replication slot
apiVersion: replication.postgresql.upbound.io/v1alpha1
kind: Slot
metadata:
  name: logical-slot
spec:
  forProvider:
    name: my_logical_slot
    database: my_db
    plugin: pgoutput
  providerConfigRef:
    name: default
---
# Slot for test_decoding plugin
apiVersion: replication.postgresql.upbound.io/v1alpha1
kind: Slot
metadata:
  name: test-slot
spec:
  forProvider:
    name: test_slot
    database: my_db
    plugin: test_decoding
  providerConfigRef:
    name: default
```

--------------------------------

### Set Default Privileges

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Configures default access control for future database objects like tables and sequences.

```yaml
apiVersion: default.postgresql.upbound.io/v1alpha1
kind: Privileges
metadata:
  name: default-table-privileges
spec:
  forProvider:
    database: my_db
    schema: public
    owner: app_admin
    role: app_user
    objectType: table
    privileges:
      - SELECT
      - INSERT
      - UPDATE
  providerConfigRef:
    name: default
---
# Default privileges for sequences
apiVersion: default.postgresql.upbound.io/v1alpha1
kind: Privileges
metadata:
  name: default-sequence-privileges
spec:
  forProvider:
    database: my_db
    schema: public
    owner: app_admin
    role: app_user
    objectType: sequence
    privileges:
      - USAGE
      - SELECT
  providerConfigRef:
    name: default
```

--------------------------------

### Grant Database Connect Privileges

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Grants CONNECT privilege on the 'ecommerce' database to the 'ecommerce_app' role. This is for application access.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Grant
metadata:
  name: ecommerce-connect-app
spec:
  forProvider:
    database: ecommerce
    role: ecommerce_app
    objectType: database
    privileges:
      - CONNECT
  providerConfigRef:
    name: default
```

--------------------------------

### Grant schema usage

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Grants USAGE and CREATE privileges on a schema to a role, allowing the role to use and create objects within that schema. Ensure the schema exists.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Grant
metadata:
  name: schema-usage
spec:
  forProvider:
    database: my_db
    role: app_user
    schema: app_schema
    objectType: schema
    privileges:
      - USAGE
      - CREATE
  providerConfigRef:
    name: default
```

--------------------------------

### Grant Read-Only Database Connect Privileges

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Grants CONNECT privilege on the 'ecommerce' database to the 'ecommerce_readonly' role. This is for read-only access.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Grant
metadata:
  name: ecommerce-connect-readonly
spec:
  forProvider:
    database: ecommerce
    role: ecommerce_readonly
    objectType: database
    privileges:
      - CONNECT
  providerConfigRef:
    name: default
```

--------------------------------

### Set Default Table Privileges for Application Role

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Sets default privileges (SELECT, INSERT, UPDATE, DELETE) for the 'ecommerce_app' role on future tables in the 'ecommerce' schema. The 'ecommerce_admin' is the owner.

```yaml
apiVersion: default.postgresql.upbound.io/v1alpha1
kind: Privileges
metadata:
  name: ecommerce-default-app
spec:
  forProvider:
    database: ecommerce
    schema: ecommerce
    owner: ecommerce_admin
    role: ecommerce_app
    objectType: table
    privileges:
      - SELECT
      - INSERT
      - UPDATE
      - DELETE
  providerConfigRef:
    name: default
```

--------------------------------

### Create simple increment function

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Defines a simple PL/pgSQL function named 'increment' that takes an integer and returns an integer incremented by one. It is marked as IMMUTABLE, SAFE for parallel execution, and STRICT.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Function
metadata:
  name: increment
spec:
  forProvider:
    name: increment
    database: my_db
    schema: public
    language: plpgsql
    returns: integer
    arg:
      - name: i
        type: integer
        mode: IN
    body: |
      BEGIN
          RETURN i + 1;
      END;
    volatility: IMMUTABLE
    parallel: SAFE
    strict: true
    securityDefiner: false
  providerConfigRef:
    name: default
```

--------------------------------

### Grant all privileges on all tables in schema

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Grants all common DML privileges (SELECT, INSERT, UPDATE, DELETE) on all tables within a specified schema to a role. An empty 'objects' list signifies all tables.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Grant
metadata:
  name: app-full-access
spec:
  forProvider:
    database: my_db
    role: app_user
    schema: app_schema
    objectType: table
    objects: []  # Empty means all tables
    privileges:
      - SELECT
      - INSERT
      - UPDATE
      - DELETE
  providerConfigRef:
    name: default
```

--------------------------------

### Grant SELECT on specific tables

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Grants SELECT privilege on a list of specified tables to a role. Ensure the role and tables exist.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Grant
metadata:
  name: readonly-tables
spec:
  forProvider:
    database: my_db
    role: readonly_role
    schema: public
    objectType: table
    objects:
      - users
      - products
      - orders
    privileges:
      - SELECT
    withGrantOption: false
  providerConfigRef:
    name: default
```

--------------------------------

### Grant database-level privileges

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Grants database-level privileges such as CONNECT and TEMPORARY to a role. This is useful for controlling access to the database itself.

```yaml
apiVersion: postgresql.postgresql.upbound.io/v1alpha1
kind: Grant
metadata:
  name: db-connect
spec:
  forProvider:
    database: my_db
    role: app_user
    objectType: database
    privileges:
      - CONNECT
      - TEMPORARY
  providerConfigRef:
    name: default
```

--------------------------------

### Set Default Table Privileges for Read-Only Role

Source: https://context7.com/tagesjump/provider-postgresql/llms.txt

Sets default SELECT privilege for the 'ecommerce_readonly' role on future tables in the 'ecommerce' schema. The 'ecommerce_admin' is the owner.

```yaml
apiVersion: default.postgresql.upbound.io/v1alpha1
kind: Privileges
metadata:
  name: ecommerce-default-readonly
spec:
  forProvider:
    database: ecommerce
    schema: ecommerce
    owner: ecommerce_admin
    role: ecommerce_readonly
    objectType: table
    privileges:
      - SELECT
  providerConfigRef:
    name: default
```
Tokens:
Raw