0

pg_hba.conf contains:
...
host 10.10.10.10 service_db admin md5
...

Executed the following commands:

###CREATE DB ADMIN
sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER 'admin' ENCRYPTED PASSWORD 'admin_pwd';"
###CREATE DB
sudo -u postgres psql -p 5432 -d postgres -c "CREATE DATABASE service_db OWNER 'admin' ENCODING 'UTF-8' TABLESPACE service_ts LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' LIMIT -1;"
###CREATE SCHEMA
sudo -u postgres psql -p 5432 -d service_db -c "CREATE SCHEMA 'service_schema';"
###REVOKE PREVILEGES ON service_schema from public
sudo -u postgres psql -p 5432 -d service_db -c "REVOKE ALL ON SCHEMA 'service_schema' FROM 'public';"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON TABLES IN SCHEMA 'service_schema' TO 'admin';"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON SEQUENCES IN SCHEMA 'service_schema' TO 'admin';"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON FUNCTIONS IN SCHEMA 'service_schema' TO 'admin';"

All commands were successfully executed.
Observed that:
a)

sudo -u postgres psql -H 10.10.10.10 -p 5432 -d service_db -U admin
service_db=#
service_db=#select * from service_schema.customers;
permission denied

b) While connecting and executing the following command:

service_db=#select * from service_schema.customers;

from Netbeans IDE getting the following error:

Exception, Error Code 0, SQL State 42501 ERROR permission denied for schema admin

Please guide me in resolving the above issue.

Thanks in Advance!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.