Repository: cloudnative-pg/cloudnative-pg

Issue:
[Feature]: access to db from another namespace
### Is there an existing issue already for this bug?

- [x] I have searched for an existing issue, and could not find anything. I believe this is a new bug.

### I have read the troubleshooting guide

- [x] I have read the troubleshooting guide and I think this is a new bug.

### I am running a supported version of CloudNativePG

- [x] I have read the troubleshooting guide and I think this is a new bug.

### Contact Details

gb12335@gmail.com

### Version

1.26 (latest patch)

### What version of Kubernetes are you using?

1.33

### What is your Kubernetes environment?

Self-managed: kind (evaluation)

### How did you install the operator?

YAML manifest

### What happened?

Good day !

I accidentally found that it is tricky to access db from another namespace as secret does not contain fqdn - preferably with .svc.cluster.local. suffix or appropriate db suffix.

The documentation supposes that the operator of the cluster should create postgres hostname manually which sounds ridiculous. 

https://cloudnative-pg.io/documentation/current/applications/

Expected solution:
- put an internal fqdn with k8s in postgresql secret in host and uri fields.
- for every additional lb - as I want to access the cluster with ease, generate two secrets - for internal access and for external access. So as an user of cnpg operator I would be able to replicate secret outside or just grab the jdbc uri and manually paste to the application config.

### Cluster resource

```shell

```

### Relevant log output

```shell

```

### Code of Conduct

- [x] I agree to follow this project's Code of Conduct

Discussion:
@dosubot: <!-- Greeting -->
Hi @gecube! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the cloudnative-pg team.

<!-- Answer -->
There isn't an existing issue that exactly matches your request, but there is a related discussion about namespace handling: when the operator is not running in cluster-wide mode (`clusterWide=false`), it only manages resources in its own namespace, which can cause confusion if you try to interact with clusters or secrets across namespaces [see discussion](https://github.com/cloudnative-pg/cloudnative-pg/issues/7713).

On how secrets are generated: CloudNativePG creates connection secrets with host and URI fields based on the service and namespace, but by default, the host is typically in the form `<cluster-name>.<namespace>:5432` and does not include the full `.svc.cluster.local` FQDN. The code that builds these secrets uses the namespace and service name, so the FQDN is technically derivable, but not always present in the secret itself [see code reference](https://github.com/cloudnative-pg/cloudnative-pg/blob/9c2769815ff78cbfc28e2a2818f3a04add33477c/pkg/specs/secrets.go).

If you need the full FQDN (e.g., `<cluster-name>.<namespace>.svc.cluster.local`), you can manually construct it, or patch the secret after creation. There is currently no built-in feature to generate both internal and external URIs or to automatically include the full FQDN in the secret. The operator's design assumes secrets are namespace-scoped and that applications running in other namespaces will need to reference the correct FQDN manually or via replicated/modified secrets.

If you want this to be automated (e.g., secrets with both internal and external URIs, or always including the full FQDN), it would require a feature enhancement. For now, the workaround is to manually adjust the secret or connection string as needed.


<!-- Dosu Comment Footer -->
*To reply, just mention [@dosu](https://go.dosu.dev/dosubot).*

---
<sup>How did I do? [Good](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/9f46d544-ba22-4293-8de6-4b81dc877d0d?feedback_type=other)</sup>&nbsp;&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/cloudnative-pg/cloudnative-pg/issues/7807)@armru: Hello, I've seen that this is labeled as bug, could you please clarify what the operator states do vs what is happening? 

If I understand correctly this is more of a feature request but I could be wrong@gecube: @armru Hi! Thanks for the attention. I think it depends on what was mentioned by authors. If the namespace should be shown in secret - it means - bug. If no - feature request.

I found what is disappointing me.

Let's say, we have a secret from cnpg.

The `uri` would look like `postgresql://app:******@top1-rw.postgresql:5432/app` - the namespace is present

But if I would check the `host` field: `top1-rw` (without namespace).

If the application for different reasons could not consume `uri` or `jdbc-uri` I am in deadlock situation. I can't use directly `host` as I need to enrich it with namespace.

Regarding `top1-rw.postgresql` - as I said in the first message it is beneficial to use the whole fqdn dedicated to a particular cluster (like `top1-rw.postgresql.svc.clusterl.local.`- with dot in the end) because it would not generate excessive amount of DNS queries against CoreDNS.

May be I just don't understand clearly the purpose of each key in the secret, but I can summarise all improvements that could be made, if necessary.@armru: Hello, thanks again for opening the issue :)

I'm labeling this as feature, just so it is clear for us how to proceed with priorities, feel free to enrich as needed the suggested improvements!

On this point we introduced: https://github.com/cloudnative-pg/cloudnative-pg/commit/76459889f4aee499107921715ef8416f2cae3cb5, so we could extend further this work to include the fqdn in the secret, but this requires some investigation on the amount of effort and risk on existing clusters


Solution patch:
diff --git a/.wordlist-en-custom.txt b/.wordlist-en-custom.txt
index 839186d3..4f4ae020 100644
--- a/.wordlist-en-custom.txt
+++ b/.wordlist-en-custom.txt
@@ -831,6 +831,7 @@ fio
 fips
 firstRecoverabilityPoint
 firstRecoverabilityPointByMethod
+fqdn
 freddie
 fuzzystrmatch
 gapped
diff --git a/docs/src/applications.md b/docs/src/applications.md
index 5828e99e..f2a7f8dc 100644
--- a/docs/src/applications.md
+++ b/docs/src/applications.md
@@ -72,6 +72,13 @@ Each secret contain the following:
 * a working [`.pgpass file`](https://www.postgresql.org/docs/current/libpq-pgpass.html)
 * [uri](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)
 * [jdbc-uri](https://jdbc.postgresql.org/documentation/use/#connecting-to-the-database)
+* [fqdn-uri](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)
+* [fqdn-jdbc-uri](https://jdbc.postgresql.org/documentation/use/#connecting-to-the-database)
+
+The FQDN to be used in the URIs is calculated using the Kubernetes cluster
+domain specified in the `KUBERNETES_CLUSTER_DOMAIN` configuration parameter.
+See [the operator configuration documentation](operator_conf.md) for more information
+about that.
 
 The `-app` credentials are the ones that should be used by applications
 connecting to the PostgreSQL cluster, and correspond to the user *owning* the
diff --git a/pkg/specs/secrets.go b/pkg/specs/secrets.go
index 1ec99496..52888fc6 100644
--- a/pkg/specs/secrets.go
+++ b/pkg/specs/secrets.go
@@ -26,6 +26,7 @@ import (
 	corev1 "k8s.io/api/core/v1"
 	metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
 
+	"github.com/cloudnative-pg/cloudnative-pg/internal/configuration"
 	"github.com/cloudnative-pg/cloudnative-pg/pkg/postgres"
 	"github.com/cloudnative-pg/cloudnative-pg/pkg/utils"
 )
@@ -40,7 +41,28 @@ func CreateSecret(
 	password string,
 	usertype utils.UserType,
 ) *corev1.Secret {
-	uriBuilder := newConnectionStringBuilder(hostname, dbname, username, password, namespace)
+	hostWithNamespace := fmt.Sprintf("%s.%s:%d", hostname, namespace, postgres.ServerPort)
+	hostWithFQDN := fmt.Sprintf(
+		"%s.%s.svc.%s:%d",
+		hostname,
+		namespace,
+		configuration.Current.KubernetesClusterDomain,
+		postgres.ServerPort,
+	)
+
+	namespacedBuilder := &connectionStringBuilder{
+		host:     hostWithNamespace,
+		dbname:   dbname,
+		username: username,
+		password: password,
+	}
+
+	fqdnBuilder := &connectionStringBuilder{
+		host:     hostWithFQDN,
+		dbname:   dbname,
+		username: username,
+		password: password,
+	}
 
 	return &corev1.Secret{
 		ObjectMeta: metav1.ObjectMeta{
@@ -66,28 +88,19 @@ func CreateSecret(
 				dbname,
 				username,
 				password),
-			"uri":      uriBuilder.buildPostgres(),
-			"jdbc-uri": uriBuilder.buildJdbc(),
+			"uri":           namespacedBuilder.buildPostgres(),
+			"jdbc-uri":      namespacedBuilder.buildJdbc(),
+			"fqdn-uri":      fqdnBuilder.buildPostgres(),
+			"fqdn-jdbc-uri": fqdnBuilder.buildJdbc(),
 		},
 	}
 }
 
 type connectionStringBuilder struct {
-	host      string
-	dbname    string
-	username  string
-	password  string
-	namespace string
-}
-
-func newConnectionStringBuilder(hostname, dbname, username, password, namespace string) *connectionStringBuilder {
-	return &connectionStringBuilder{
-		host:      fmt.Sprintf("%s.%s:%d", hostname, namespace, postgres.ServerPort),
-		dbname:    dbname,
-		username:  username,
-		password:  password,
-		namespace: namespace,
-	}
+	host     string
+	dbname   string
+	username string
+	password string
 }
 
 func (c connectionStringBuilder) buildPostgres() string {



Test patch:
diff --git a/pkg/specs/secrets_test.go b/pkg/specs/secrets_test.go
index 22ab7650..8693fadb 100644
--- a/pkg/specs/secrets_test.go
+++ b/pkg/specs/secrets_test.go
@@ -44,6 +44,14 @@ var _ = Describe("Secret creation", func() {
 		Expect(secret.StringData["jdbc-uri"]).To(
 			Equal("jdbc:postgresql://thishost.namespace:5432/thisdb?password=thispassword&user=thisuser"),
 		)
+
+		Expect(secret.StringData["fqdn-uri"]).To(
+			Equal("postgresql://thisuser:thispassword@thishost.namespace.svc.cluster.local:5432/thisdb"),
+		)
+		Expect(secret.StringData["fqdn-jdbc-uri"]).To(
+			Equal("jdbc:postgresql://thishost.namespace.svc.cluster.local:5432/thisdb?password=thispassword&user=thisuser"),
+		)
+
 		Expect(secret.Labels).To(
 			HaveKeyWithValue(utils.UserTypeLabelName, string(utils.UserTypeApp)))
 	})