PostGIS "relation does not exist" and Permission Errors: How to Fix Them
Problem statement
The table is there. You can see it in pgAdmin. And yet:
gpd.read_postgis("SELECT * FROM parcels", engine, geom_col="geom")psycopg.errors.UndefinedTable: relation "parcels" does not exist
LINE 1: SELECT * FROM parcels
^
Or the table is found and then this:
psycopg.errors.InsufficientPrivilege: permission denied for table parcels
Or the write fails on something that sounds unrelated:
psycopg.errors.InsufficientPrivilege: permission denied for schema public
Or, most confusing of all, it works from psql and fails from Python with the same credentials.
These are four different errors with one family of causes: PostgreSQL is not looking where you think it is looking, or you are not who you think you are. Both are answerable with two queries.
Quick answer
Ask the database what it can actually see:
from sqlalchemy import create_engine, text
engine = create_engine("postgresql+psycopg://user@localhost/gis")
with engine.begin() as con:
print("database ", con.execute(text("SELECT current_database()")).scalar())
print("user ", con.execute(text("SELECT current_user")).scalar())
print("search_path", con.execute(text("SHOW search_path")).scalar())
rows = con.execute(text("""
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE :name
"""), {"name": "%parcels%"}).all()
print("matches ", rows)database gis
user analyst
search_path "$user", public
matches [('raw', 'parcels'), ('staging', 'parcels_staging')]
There it is: the table is in the raw schema, and search_path does not include raw.
| Error | Most likely cause | Fix |
|---|---|---|
relation "x" does not exist |
wrong schema, or search_path |
qualify it: raw.parcels |
relation "X" does not exist (capitals) |
the table was created with quotes | "Parcels", quotes included |
permission denied for table |
no SELECT grant on the table |
GRANT SELECT ON … TO role |
permission denied for schema |
no USAGE on the schema, qualified name |
GRANT USAGE ON SCHEMA … TO role |
| works in psql, fails in Python | different user, database or search_path |
print all three from Python |
Step-by-step solution
1. Find where the table actually is
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_name = 'parcels'; table_schema | table_name | table_type
--------------+------------+------------
raw | parcels | BASE TABLE
Or, for anything spatial, PostGIS's own catalogue is more informative:
SELECT f_table_schema, f_table_name, f_geometry_column, srid, type
FROM geometry_columns
ORDER BY 1, 2; f_table_schema | f_table_name | f_geometry_column | srid | type
----------------+--------------+-------------------+-------+--------------
raw | parcels | geom | 27700 | MULTIPOLYGON
public | wards | geom | 27700 | MULTIPOLYGON
Two schemas, and only one of them is on the default search_path. That is the whole bug.
Note to_regclass, which is the cheapest possible existence test and returns NULL rather than raising:
SELECT to_regclass('raw.parcels'); -- raw.parcels
SELECT to_regclass('parcels'); -- NULL (not on the search_path)2. Understand search_path
SHOW search_path; search_path
-----------------
"$user", public
An unqualified name like parcels is looked up in each schema on this list in order. "$user" expands to a schema named after the connecting role — usually nonexistent, and harmlessly skipped. So the PostgreSQL default effectively means "look in public, nowhere else".
(A database with the postgis_topology and postgis_tiger_geocoder extensions installed reports "$user", public, topology, tiger instead — those extensions append their own schemas. It changes nothing about your tables.)
Three ways to fix it, in increasing order of durability:
-- (a) per query — always unambiguous, and the one to prefer
SELECT * FROM raw.parcels;# (b) per connection
engine = create_engine(
"postgresql+psycopg://user@localhost/gis",
connect_args={"options": "-csearch_path=raw,public"},
)-- (c) per role, persistent across all future sessions
ALTER ROLE analyst SET search_path TO raw, public;Option (a) is what belongs in code. A query that works only under a particular search_path is a query that will break in a colleague's session, in a cron job, or in CI — the same class of environment dependency as a script that works manually but not from cron.
3. Check the case
PostgreSQL folds unquoted identifiers to lower case. A table created as "Parcels" — which is what to_postgis("Parcels", …) produces — is genuinely named Parcels and can only ever be referenced with quotes:
SELECT * FROM Parcels; -- ERROR: relation "parcels" does not exist
SELECT * FROM "Parcels"; -- worksNote the error message's own giveaway: you typed Parcels and it reports "parcels" in lower case, which tells you the folding happened.
-- find the ones that will bite you
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name <> lower(table_name);Renaming is cheaper than quoting forever:
ALTER TABLE "Parcels" RENAME TO parcels;The same applies to columns. A column created as "Ward Name" needs quotes in every query, in every view, in every application, for the rest of its life. Sanitise names at load time — see how to load a folder of shapefiles into PostGIS.
4. Separate "not visible" from "not permitted"
Which error you get depends on how you spelled the name, and this catches people out. Tested against PostgreSQL 16 with PostGIS 3.4:
-- unqualified, with raw on the search_path but no USAGE granted
SELECT * FROM zones;ERROR: relation "zones" does not exist
-- the same table, schema-qualified
SELECT * FROM raw.zones;ERROR: permission denied for schema raw
Same role, same table, same missing grant. An unqualified name cannot be resolved without USAGE, so it reports as nonexistent; a qualified name resolves and is then refused. So relation does not exist genuinely can mean "you may not look" — but only for unqualified names, which is one more reason to qualify.
The same asymmetry affects the introspection functions, and it is easy to trip over:
SET ROLE analyst;
SELECT has_schema_privilege(current_user, 'raw', 'USAGE'); -- returns f
SELECT has_table_privilege(current_user, 'raw.parcels', 'SELECT');
SELECT to_regclass('raw.parcels'); has_schema_privilege
----------------------
f
ERROR: permission denied for schema raw
ERROR: permission denied for schema raw
has_schema_privilege answers. has_table_privilege and to_regclass both raise when you lack USAGE on the schema, because they have to resolve the name first. Always check the schema before the table — the pre-flight function in Example 1 is ordered that way for exactly this reason.
The catalogue views differ too. information_schema.tables and geometry_columns filter by privilege and show nothing; pg_tables does not filter and still lists the table. If you can see it in pg_tables and nowhere else, the problem is a grant.
Once diagnosed, the fix is two grants:
GRANT USAGE ON SCHEMA raw TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA raw TO analyst;
-- and for tables created later
ALTER DEFAULT PRIVILEGES IN SCHEMA raw GRANT SELECT ON TABLES TO analyst;That last line is the one people miss. Grants apply to tables that exist at the time; a table created tomorrow has no grant unless a default privilege covers it. It is the usual reason a nightly job works for weeks and then fails on a newly added table.
5. Fix the write-permission cases
Writing needs more than INSERT:
GRANT USAGE ON SCHEMA staging TO loader;
GRANT CREATE ON SCHEMA staging TO loader; -- needed to CREATE TABLE
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA staging TO loader;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA staging TO loader;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO loader;Two of these are easy to forget:
CREATE ON SCHEMAis whatto_postgis(..., if_exists="replace")needs. Without it you getpermission denied for schema stagingon the create, even with full table privileges.- Sequence privileges matter whenever a table has a
serialoridentitycolumn. Inserting into such a table callsnextvalon its sequence, andINSERTon the table does not implyUSAGEon the sequence.
Since PostgreSQL 15, CREATE on public is no longer granted to PUBLIC by default, so a script that worked on PostgreSQL 14 can fail on 15 with exactly this error.
6. Confirm you are connected where you think
from sqlalchemy import create_engine, text
def whoami(engine):
with engine.begin() as con:
r = con.execute(text("""
SELECT current_database() AS db,
current_user AS user,
session_user AS login,
current_schema() AS schema,
inet_server_addr() AS host,
inet_server_port() AS port,
version() AS version
""")).mappings().one()
for k, v in r.items():
print(f" {k:<9} {v}")
return dict(r)
whoami(engine) db gis
user analyst
login analyst
schema public
host 10.0.4.12
port 5432
version PostgreSQL 16.2 … POSTGIS="3.4.3"
Run this from Python whenever "it works in psql". Nine times out of ten the answer is a different host, a different db, or a different user — psql reading ~/.pgpass and a service file that the Python connection string does not.
current_user differing from session_user means a SET ROLE is in effect, which changes which grants apply.
Code examples
Example 1: a pre-flight check that fails clearly
Turn four cryptic errors into one clear message, before the job starts:
from sqlalchemy import create_engine, text
def preflight(engine, requirements):
"""requirements: {"raw.parcels": ["SELECT"], "staging": ["USAGE", "CREATE"]}"""
problems = []
with engine.begin() as con:
ident = con.execute(text(
"SELECT current_database(), current_user, current_schema()")).one()
print(f"connected as {ident[1]} to {ident[0]} (schema {ident[2]})")
for obj, privs in requirements.items():
if "." in obj:
schema = obj.split(".")[0]
# USAGE first: to_regclass and has_table_privilege both RAISE
# without it, rather than returning null or false.
if not con.execute(text(
"SELECT has_schema_privilege(current_user, :s, 'USAGE')"),
{"s": schema}).scalar():
problems.append(
f"{obj}: no USAGE on schema {schema} — the table may exist "
f"but is unreachable from this role")
continue
if con.execute(text("SELECT to_regclass(:o)"),
{"o": obj}).scalar() is None:
problems.append(f"{obj}: not found in schema {schema}")
continue
for p in privs:
ok = con.execute(text(
"SELECT has_table_privilege(current_user, :o, :p)"),
{"o": obj, "p": p}).scalar()
if not ok:
problems.append(f"{obj}: missing {p}")
else:
for p in privs:
ok = con.execute(text(
"SELECT has_schema_privilege(current_user, :s, :p)"),
{"s": obj, "p": p}).scalar()
if not ok:
problems.append(f"schema {obj}: missing {p}")
if problems:
raise PermissionError(
"database pre-flight failed:\n - " + "\n - ".join(problems))
print(" ✓ all required objects and privileges present")
preflight(engine, {
"raw.parcels": ["SELECT"],
"raw.wards": ["SELECT"],
"staging": ["USAGE", "CREATE"],
})connected as analyst to gis (schema public)
PermissionError: database pre-flight failed:
- raw.parcels: no USAGE on schema raw — the table may exist but is unreachable from this role
- raw.wards: no USAGE on schema raw — the table may exist but is unreachable from this role
- schema staging: missing CREATE
Checking schema USAGE before touching the table is not tidiness — it is required. to_regclass and has_table_privilege both raise on a schema you cannot use, so the obvious ordering fails with the very error it was meant to diagnose. Getting it right also produces a better message: "unreachable from this role" and "not found in schema raw" send you to different people, a DBA for the first and a data engineer for the second. Running this at the start of a scheduled job turns a 3 a.m. failure at step 14 into an immediate, self-explaining one — the discipline described in validating pipeline inputs and outputs.
Example 2: always qualifying names from Python
import geopandas as gpd
from sqlalchemy import create_engine, text
class SpatialDB:
"""Every table reference is schema-qualified and quoted, always."""
def __init__(self, url, schema="public"):
self.engine = create_engine(url)
self.schema = schema
def qualified(self, table, schema=None):
return f'"{schema or self.schema}"."{table}"'
def exists(self, table, schema=None):
with self.engine.begin() as con:
return con.execute(
text("SELECT to_regclass(:o) IS NOT NULL"),
{"o": f"{schema or self.schema}.{table}"},
).scalar()
def read(self, table, schema=None, where=None, geom_col="geom", limit=None):
if not self.exists(table, schema):
raise LookupError(f"{self.qualified(table, schema)} not found or not visible")
sql = f"SELECT * FROM {self.qualified(table, schema)}"
if where:
sql += f" WHERE {where}"
if limit:
sql += f" LIMIT {int(limit)}"
return gpd.read_postgis(sql, self.engine, geom_col=geom_col)
def write(self, gdf, table, schema=None, if_exists="fail", srid=None):
schema = schema or self.schema
if srid:
gdf = gdf.to_crs(srid)
gdf.to_postgis(table, self.engine, schema=schema,
if_exists=if_exists, index=False, chunksize=10_000)
with self.engine.begin() as con:
con.execute(text(
f'CREATE INDEX IF NOT EXISTS "{table}_geom_idx" '
f'ON {self.qualified(table, schema)} USING GIST (geometry)'))
con.execute(text(f"ANALYZE {self.qualified(table, schema)}"))
return self.qualified(table, schema)
db = SpatialDB("postgresql+psycopg://analyst@localhost/gis", schema="raw")
parcels = db.read("parcels", where="class = 'residential'", limit=1000)Qualifying and quoting every identifier removes both the search_path class of bug and the case-folding class at once, and it does so in one place rather than in every query. The exists check before reading converts UndefinedTable — which arrives from deep inside the driver — into a LookupError naming exactly what was looked for.
The where string is interpolated, so it must come from your code and never from user input. Values go through params; identifiers and expressions cannot, which is precisely why they need this care.
Example 3: a role setup that does not need revisiting
-- read-only analysts
CREATE ROLE gis_read NOLOGIN;
GRANT USAGE ON SCHEMA public, raw TO gis_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public, raw TO gis_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, raw GRANT SELECT ON TABLES TO gis_read;
-- loaders that write to staging and publish to public
CREATE ROLE gis_write NOLOGIN;
GRANT gis_read TO gis_write;
GRANT USAGE, CREATE ON SCHEMA staging TO gis_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA staging, public TO gis_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA staging, public TO gis_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO gis_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging
GRANT USAGE, SELECT ON SEQUENCES TO gis_write;
-- actual login accounts inherit from the group roles
CREATE ROLE analyst LOGIN PASSWORD :'analyst_pw';
GRANT gis_read TO analyst;
CREATE ROLE etl LOGIN PASSWORD :'etl_pw';
GRANT gis_write TO etl;
ALTER ROLE etl SET search_path TO staging, public, raw;Two structural choices repay themselves. Granting to NOLOGIN group roles rather than to individuals means a new analyst needs one GRANT, not a dozen. And ALTER DEFAULT PRIVILEGES on every schema anyone writes to is what stops the "worked for weeks, failed on the new table" failure — without it, every newly created table starts with no grants at all.
The ALTER ROLE etl SET search_path is a convenience for interactive use. Scheduled jobs should still qualify their names, because a role-level setting is exactly the kind of environment state that differs between your session and the server's.
Explanation
Both families of error come from the same root: a name in SQL is not an address. parcels is a request to resolve a name, and resolution depends on state that lives outside the query.
Schemas are namespaces, and search_path is the resolution order. When you write parcels, PostgreSQL tries each schema in turn and takes the first match. So the same query can find different tables — or none — depending on who is connected and how their session is configured. This is a feature: it is what lets a staging.parcels and a public.parcels coexist, and what lets a test schema shadow production tables for one session. It is also why an unqualified name is a portability hazard, and why qualifying is the fix rather than a style preference.
Case folding is the second resolution rule. Unquoted identifiers are folded to lower case, so Parcels, PARCELS and parcels are the same name. A quoted identifier is taken literally, so "Parcels" is a genuinely different name that no unquoted spelling can reach. Tools that quote by default — including to_postgis — create tables that require quoting forever, which is why sanitising names at load time is worth the ten lines it takes.
Permissions are two-layered, and the layers are independent. USAGE on a schema is permission to look inside it; privileges on a table are permission to use that table. Table grants are worthless without schema USAGE, which is why the most confusing permission failures come from missing a grant nobody thinks about. And the error you get depends on how you spelled the name: an unqualified name that cannot be resolved reports as nonexistent, while the same table named as raw.parcels reports permission denied for schema raw. So relation does not exist really does cover both "absent" and "unreachable", but only in the unqualified case.
Grants are also point-in-time. GRANT SELECT ON ALL TABLES IN SCHEMA raw applies to the tables that exist right now, and nothing else. Tomorrow's table is not covered. ALTER DEFAULT PRIVILEGES is the standing instruction that covers future objects, and its absence is the single most common cause of a pipeline that works for a month and then fails on one new table.
Finally, "it works in psql" is almost never about SQL. psql reads ~/.pgpass, PGSERVICE, PGDATABASE and role-level settings; a Python connection string names everything explicitly and inherits none of it. Printing current_database(), current_user and search_path from the failing connection settles it in one query — and it is worth doing first, because every other hypothesis costs more to test.
Edge cases or notes
to_regclass('name')returnsNULLinstead of raising, which makes it the right existence test.- Missing
USAGEgives two different errors. Unqualified:relation does not exist. Qualified:permission denied for schema.to_regclassandhas_table_privilegeraise;has_schema_privilegeanswers. pg_tablesdoes not filter by privilege, whileinformation_schema.tablesandgeometry_columnsdo. Visible in one and not the others means a grant is missing.ALTER DEFAULT PRIVILEGESonly applies to objects created by the role that ran it, unless you addFOR ROLE.- PostgreSQL 15 removed the default
CREATEonpublicforPUBLIC. Scripts that worked on 14 can fail on 15. current_userversussession_userdiffer whenSET ROLEis active, and grants followcurrent_user.- Views need grants of their own, and the view's owner needs access to the underlying tables.
geometry_columnsis a view over the catalogue and only lists tables the current role can see.- Sequence privileges are separate. Inserting into a table with a
serialcolumn needsUSAGEon its sequence. \dt *.*in psql lists tables in every schema, which is faster than guessing.- Connection pooling can reuse a session with a modified
search_path. Set it inconnect_args, not with an ad-hocSET.
Internal links
- GeoPandas cannot connect to PostGIS — when the connection itself fails
- How to connect GeoPandas to PostGIS — connection strings and drivers
- How to load a folder of shapefiles into PostGIS — sanitising names before they become permanent
- How to write a GeoDataFrame to PostGIS — the privileges a write needs
- How to validate pipeline inputs and outputs automatically — where a pre-flight check belongs
- Python GIS script works manually but not from cron — the same environment-state problem
- How to handle credentials and secrets in an automated GIS job — connecting as the right role
- PostGIS write fails on SRID or geometry type — the next error after this one
FAQ
Why does psql find the table but Python does not?
Usually a different database, host, user or search_path. psql reads ~/.pgpass and service files that a Python connection string does not. Print current_database(), current_user and SHOW search_path from the failing connection.
What is search_path and why does it matter?
It is the ordered list of schemas an unqualified name is resolved against. The default is effectively public only, so a table in any other schema is invisible unless you qualify it.
Why does my table need quotes?
It was created with capitals or spaces, so its real name contains them. PostgreSQL folds unquoted identifiers to lower case, so only the quoted spelling reaches it. Rename it.
I have SELECT on the table but still get permission denied.
You are probably missing USAGE on its schema. Table grants do nothing without permission to look inside the schema that holds the table — and has_table_privilege will raise rather than answer, so check has_schema_privilege first.
Why did it work for weeks and then fail on a new table?
GRANT … ON ALL TABLES only covers tables that existed when it ran. Add ALTER DEFAULT PRIVILEGES so future tables are covered too.
permission denied for schema public on PostgreSQL 15?
Version 15 stopped granting CREATE on public to PUBLIC by default. Grant it explicitly to the role that creates tables.
Should I set search_path or qualify every name?
Qualify. A query that depends on search_path breaks in another session, in cron, or in CI. Set the path for interactive convenience only.