CODEWITHSUNDEEP
postgresqlpostgispostgresql-9.3
t.pimentel
t.pimentel

Reputation: 1575

PostGIS only works with root user

I have a database and am trying to use PostGis with it. When I run:

$ psql -h localhost -d $APP_DB_NAME -U $APP_DB_USER
# SELECT PostGIS_version();

I get the following error:

ERROR:  function postgis_version() does not exist
LINE 1: SELECT PostGIS_version();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

But when I enter the database as root:

$ sudo su postgres -c psql $APP_DB_NAME
# SELECT PostGIS_version();

It runs fine:

            postgis_version
---------------------------------------
 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

I am installing the PostGis extension as the root user, since my user doesn't have super_user access.

$ su - postgres -c psql $APP_DB_NAME
# CREATE EXTENSION postgis;

Upvotes: 2

Views: 365

Answers (1)

chrismarx
chrismarx

Reputation: 12555

My guess is that the owner of the schema in which postgis is installed is not the user you're logging in as, but rather the "root" user (which is postgres). 

$ psql postgres
postgres=# \dn
    List of schemas
    Name        |  Owner
--------------------+----------
public             | postgres
pg_catalog         | postgres
my_data            | someuser

When connecting as postgres, postgis will work in this case, but if you connect as any other user, it will fail, and say it's not there, even though if you try to create extension postgis it will say it's installed already.

To correct this, make sure all the schemas are owned by the user you're actually connecting as:

ALTER SCHEMA public OWNER TO my_user;

Upvotes: 1

Related Questions