Unable to grant permission on table

Question

I've been given credentials to a postgres database that I'm told it has privileges to create new users and grant them access to tables. This is half true.

I wish to create a new user:

create user foo login encrypted password 'bar';
commit;

This is OK. Now I'm trying to that user grant some specific access to selected tables:

grant select, insert, update on table baz to foo;

The response is [42501] ERROR: permission denied for relation baz.

It seems that this user I've been given can't event select from the tables I wish to grant access to. select * from baz also gives a 42501 error.

The query select distinct(grantor) from information_schema.role_table_grants; returns one username (let's call it 'bob'). Should I be speaking with Bob about getting the privs I need?

Answer 1

At a guess, your user isn't the owner of the tables and hasn't been granted rights to them WITH GRANT OPTION so it can delegate them.

The database administrator must do something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser WITH GRANT OPTION

and should probably also GRANT similar rights on SEQUENCEs, on the schema its self, etc.

The WITH GRANT OPTION gives the user myuser the right to in turn GRANT the assigned rights to others.

See the manual for details.

You might also be interested in ALTER DEFAULT PRIVILEGES.