Specify setup.sql when deploying with custom resources

[kevinlmadison]

** Which example are you working with? **
I'm deploying a postgres cluster using the custom resource manifests
What is the current behavior?
The current behavior is that I get a working pg cluster
What is the expected behavior?
I would like to be able to apply setup.sql to our cluster on start-up using the custom resources, our setup does not allow for using the pgo tool.
Other information (e.g. detailed explanation, related issues, etc)
Basically, I want to be able to do the things described on this page:
https://access.crunchydata.com/documentation/postgres-operator/4.4.1/advanced/custom-configuration/
using the process described on this page:
https://access.crunchydata.com/documentation/postgres-operator/4.4.1/custom-resources/

It looks like I should be able to do this using the CustomConfig section of the pgcluster crd but I'm not sure what that configmap needs to look like. I'm hoping I can keep digging and figure it out, at which point I'll comment the solution on this issue but if anyone knows of some docs I may have missed that would be awesome. Thank you and I apologize if I didn't describe this issue fully enough.

Please tell us about your environment:

• Operating System:
  Centos 7
• Where is this running ( Local , Cloud Provider)
  Cloud
• Storage being used (NFS, Hostpath, Gluster, etc):
  Rook-Ceph
• Container Image Tag:
  crunchy-postgres-ha:centos7-11.9-4.4.1
• PostgreSQL Version:
  11.9
• Platform (Docker, Kubernetes, OpenShift):
  Kubernetes
• Platform Version:
  1.17.7

If possible please run the following on the kubernetes or OpenShift (oc) commands and provide the result:
kubectl describe yourPodName
kubectl describe pvc
kubectl get nodes
kubectl log yourPodName

[jkatz]

Are you looking to apply a custom configuration, or run some SQL scripts once the database is booted (or both)?

[kevinlmadison]

@jkatz I'm looking just to run some SQL scripts once the database is booted.

[jkatz]

@kevinlmadison Great. So indeed, if you comb through the docs, you may be able to find the pieces to put this together, but I admit that this will be a challenge.

What you are looking for is something called a "policy" -- the name going back to a point where it was used for managing ACL type policies, but really it can run SQL files.

You can create a policy either by using the CLI as suggested, or creating a custom resource in pgpolicies.crunchydata.com , which all you would need to add is:

• name
• namespace
• sql

(and, admittedly, that CRD needs to be documented on the custom resources[(https://access.crunchydata.com/documentation/postgres-operator/latest/custom-resources/) page).

Creating a policy is a one-time action within a namespace (unless you wipe the namespace, delete the Operator CRDs), and it can be re-used across different clusters.

Once you have this policy created, you can add it to your CR in the policies field in the spec. When your PostgreSQL instance is up and running, it will execute any policies that are reference in that attribute.

I'm currently working on expanding our docs and will add this case as part of the "customization tutorial", though that is covering the case of doing things with the pgo client.

If you're interested, we're looking to improve things in this area (which I hope I've demonstrated with all of the "we need to document XYZ") and I'd like to learn more about your deployment/use-case. If you're willing to chat, please send me a note: jonathan.katz@crunchydata.com

Hope this helps!

[kevinlmadison]

This is extremely helpful! Thank you so much, I'm implementing this at the moment and I'll let you know if I'm successful!

[kevinlmadison]

Hey @jkatz so I'm working on this some more now and it seems like there is some sort of disconnect between the policy I'm creating and my pg cluster. I'm able to successfully create a policy using this definition.

---
apiVersion: crunchydata.com/v1
kind: Pgpolicy
metadata:
  name: my-policy
  namespace: my-ns
spec:
  name: my-policy
  namespace: my-ns
  sql: |
    CREATE USER myuser;
    ALTER USER myuser WITH ENCRYPTED PASSWORD myuser;
    CREATE DATABASE mydb;
    GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
    GRANT USAGE ON SCHEMA public TO myuser;

but when I include the policy name in my CR manifest I'm not seeing this reflected in the actual database, for reference this is how I'm including this in the CR:

  policies: "my-policy"

I tried creating the policy using the pgo cli tool as well and then looked at it with kubectl to see that my manifest is formatted correctly and I believe that it is. I've tried a simpler SQL script, just CREATE TABLE mytable to no avail.
I've wiped my cluster many times now, as well as removing the operator itself. I've not seen anything in the logs to indicate that anything is erring but I'm not seeing any reference to my policy.
Once I get this working I would gladly contribute to the docs so anyone else might be able to work through this much more easily in the future! Thank you

[clementnero]

@jkatz I have the same problem as described by @kevinlmadison. @kevinlmadison did you managed to make it works ?

[clementnero]

@jkatz, from what I see from the following, a pgtask is needed to trigger the execution of a policy. The pgtask includes a reference to an existing pgpolicy. The policies attribute of a pgcluster seems to be never read.

postgres-operator/internal/operator/task/applypolicies.go

Lines 30 to 44 in e126780

	func ApplyPolicies(clusterName string, clientset kubeapi.Interface, RESTConfig *rest.Config, ns string) {
	ctx := context.TODO()
	taskName := clusterName + "-policies"
	task, err := clientset.CrunchydataV1().Pgtasks(ns).Get(ctx, taskName, metav1.GetOptions{})
	if err == nil {
	//apply those policies
	for k := range task.Spec.Parameters {
	log.Debugf("applying policy %s to %s", k, clusterName)
	applyPolicy(clientset, RESTConfig, k, clusterName, ns)
	}
	//delete the pgtask to not redo this again
	clientset.CrunchydataV1().Pgtasks(ns).Delete(ctx, taskName, metav1.DeleteOptions{})
	}
	}

)

[jkatz]

@andrewlecuyer has pointed out there may be an easier way to accomplish this with a custom resource workflow.

I will note that this is fairly trivial with the pgo client (sub the apply bits for pgo create cluster ... --policies=policy1,policy2). You can run the pgo-client within a Kubernetes cluster as well.

[depesr]

same issue here. Can't get policy using CRD to work :(

[andrewlecuyer]

Using the Custom Configuration capability described here, you can create a custom setup.sql file that is run when a cluster is initialized for the first time (the default version of this file can be found here). Specifically, by creating a ConfigMap within the PG cluster's namespace with a setup.sql file containing the various custom SQL you would like to run during cluster initialization (ensuring the SQL included in the default setup.sql file is also included as needed, e.g. to create the standard user account), you can then reference that ConfigMap using the customconfig field in the pgcluster spec:

  clustername: ${pgo_cluster_name}
  customconfig: ""
  database: ${pgo_cluster_name}

For an example on how to do this, please see our custom-config example. This example contains a create.sh script that will create a custom ConfigMap called pgo-custom-pg-config within your specific namespace (as specified using environment variable PGO_NAMESPACE) containing the postgres-ha.yaml and setup.sql files also included in that directory (which you can modify as needed), which would then be applied during cluster creation if referenced from the pgcluster spec, e.g.:

  clustername: ${pgo_cluster_name}
  customconfig: pgo-custom-pg-config
  database: ${pgo_cluster_name}

[depesr]

@andrewlecuyer i'm afraid there's something wrong with the create.sh script, since pgo does not know configmap.
trying to manually call ./pgo delete configmap pgo-custom-pg-config -n postgres-operator results in

Error: You must specify the type of resource to delete.  Valid resource types include:
	* backup
	* cluster
	* label
	* pgadmin
	* pgbouncer
	* pgouser
	* pgorole
	* policy
	* namespace
	* user

Care about correcting create.sh script?

[jkatz]

Care about correcting create.sh script?

Pull requests are more than welcome.