3

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?

  • What do you mean with root user? By default there is no user named "root" in Postgres. Did you create that account yourself? If yes how and with which privileges? – a_horse_with_no_name May 15 '15 at 6:42
  • @a_horse_with_no_name has a good point... Do you mean that you have been given root access to the [unix] server that runs the database, rather than the database software itself? You so that you are told that they are root, but is your username root? Or are you using postgres? Please clarify. – Greenonline May 15 '15 at 7:25
  • 1
    @Greenonline: no, you don't need root access to the database server. You don't even need shell access to the database server - you only need to be able to connect to the Postgres server using a SQL tool (e.g. psql) – a_horse_with_no_name May 15 '15 at 7:36
  • Thank you @a_horse_with_no_name. I've edited the question in light of what you've taught me. – Synesso May 15 '15 at 7:43
  • 1
    @a_horse_with_no_name That's answered in the first sentence. – Synesso May 15 '15 at 8:38
4

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.