I am new to postgresql and I would grateful if
you could please advise on how-to resolve the following error..
I have issued the followed commands:
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ;
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA net FROM PUBLIC ;
ip_spotlight# GRANT USAGE ON SCHEMA doc TO netopsapp ;
ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA doc GRANT ALL ON TABLES TO netopsapp ;
ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA net GRANT ALL ON TABLES TO netopsapp ;
Below is a list of the privileges:
ip_spotlight# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
doc | postgres | |
net | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(3 rows)
And the error is:
python3 -m pwiz --engine=postgresql --host=x.x.x.x --port=5432 --user=netopsapp --password --schema=doc --tables=bgp_communities ip_spotlight
Password:
Traceback (most recent call last):
File "/usr/lib/python3.4/site-packages/peewee.py", line 3768, in execute_sql
cursor.execute(sql, params or ())
psycopg2.ProgrammingError: permission denied for schema doc
LINE 1: SELECT * FROM "doc"."bgp_communities" LIMIT 1
Could you please advise on how to setup the privileges so that netopsapp
user to have access to the tables defined in schema doc
PS: the first 2 commands were mentioned as best practice in the postgresql book
Best Answer
this solves it: