Postgresql – How to assign privileges on a postgresql schema to a user

permissionspostgresql

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

Logged in as root, 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 TABLES 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 SEQUENCES 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';"

and all commands were executed successfully.

Observed that:
a) Got permission denied error while selecting from a table in that schema in cli, as follows:

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

While connecting and executing the command from Netbeans:

select * from service_schema.customers;

throws 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!

Update:

GRANT USAGE ON SCHEMA… just before GRANT PRIVILEGES… resolved the problem. ie.

###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 USAGE ON service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT USAGE ON SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON TABLES 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 SEQUENCES 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";"

works fine for me!

Best Answer

You don't mention which user created service_schema.customers and what permissions were given, if any, to the admin user for that table.

That user is presumably not admin and it presumably gave no permission at all to admin, so that would explain the error.

After creating the schema, the script does that:

GRANT ALL ON TABLES IN SCHEMA service_schema TO admin;

(BTW I've removed the single quotes around the schema name and user name. It's an error to put single quotes around identifiers, only double quotes or no quotes at all are allowed).

Anyway that command doesn't really affect anything because there are no tables yet in a schema that has just been created.

What you probably want is to (quoting the doc here) set the privileges that will be applied to objects created in the future with

ALTER DEFAULT PRIVILEGES IN SCHEMA service_schema GRANT ALL ON TABLES TO admin;

See ALTER DEFAULT PRIVILEGES in the documentation.

Related Question