42

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

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
nskalis
  • 1,721
  • 4
  • 15
  • 12

3 Answers3

58

this solves it:

postgres=# \connect ip_spotlight
You are now connected to database "ip_spotlight" as user "postgres".
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netopsapp ;

ip_spotlight=# GRANT SELECT ON ALL TABLES IN SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA doc,net TO netops ;
ip_spotlight-# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA doc,net TO netops ;

ip_spotlight-# GRANT ALL ON ALL TABLES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL SEQUENCES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL FUNCTIONS IN SCHEMA doc,net TO netopsapp ;
nskalis
  • 1,721
  • 4
  • 15
  • 12
5

You're not running the GRANT / GRANT ALL when connected to the right database. Perhaps you're confusing devel and production?

When you run \dn+ on ip_spotlight you can see that you have no permissions granted to a netopsapp. That should otherwise be listed there.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
4

For those wondering about security of running GRANT [ALL] USAGE statement, here is what Postgres doc have to say:

For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables.

Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access. For sequences, this privilege allows the use of the currval and nextval functions.

For servers, this privilege enables the grantee to create, alter, and drop his own user's user mappings associated with that server. Also, it enables the grantee to query the options of the server and associated user mappings.

Fusion
  • 141
  • 3