0

I found out that in my PostgreSQL server, there are 3 tables that I can call from any database. When I use \d command in psql I see those tables no matter to which database I'm connected.
I didn't even know it is possible for tables to be accessible from all databases.

What is this feature in PostgreSQL and how should I hide them from other databases?

2
  • 1
    Are you sure that the tables are in other databases? Or are they in other schemas? Please show us the output of show search_path ;. Also can you tell us the names of these tables? Are they tables created by you? And does this happen when you connect as postgres user, as another superuser or as any user (without superuser rights)? Apr 21, 2018 at 10:36
  • 1
    Who owns these tables? What are their names? Are they the same table, or different tables with the same name? Do they exist in template1?
    – jjanes
    Apr 21, 2018 at 13:11

1 Answer 1

4

This quote from the manual should help to lift the fog. From "Managing Databases" - "Overview":

A database is a named collection of SQL objects (“database objects”). Generally, every database object (tables, functions, etc.) belongs to one and only one database. (However there are a few system catalogs, for example pg_database, that belong to a whole cluster and are accessible from each database within the cluster.) More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

Bold emphasis mine.

So there are some tables that are visible in every database of the same cluster. But only a few system catalogs like pg_database. Most other system catalogs, also present in each database, are separate instances, sharing the same name.

Nothing keeps you from creating tables of the same name in distinct databases or distinct schemas within the same database. If you have created tables (or other objects) in the template1 database of your cluster, one copy is added to every new database automatically. All of these are distinct tables regardless.

Finally, "server" is a fuzzy term. Might refer to a single DB cluster or to a system with several PostgreSQL DB clusters (listening to distinct ports) ... Use accurate terminology to avoid confusion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.