9

I have ran the following SQL in psql:

CREATE USER bspu LOGIN;

CREATE DATABASE bsp OWNER bspu;

GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu;

\c bsp

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  api_key VARCHAR(100) NOT NULL,
  api_secret VARCHAR(100) NOT NULL,
  auth_token VARCHAR(128) NOT NULL
);

When I login as bspu, and try to query the users table, I get the error:

permission denied for relation users

I tried running:

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO bspu;

But it doesn't help. What am I doing wrong? Why does database owner NOT have permissions to query its own database?

EDIT: I upgraded bspu to superuser for now, so I can continue work. Any further guidance appreciated.

  • What does the output of \dt "users" show? – bma Nov 25 '13 at 17:29
  • @bma only my main acc is listed. – if __name__ is None Nov 25 '13 at 18:43
  • You ran the initial commands as what user? What do you get from SELECT session_user, current_user at the top of your script? And what is your Postgres version? – Erwin Brandstetter Nov 26 '13 at 0:31
  • If these were the exact commands you've run, then you are still connected with your 'main acc'. So it comes down what privileges you have. Otherwise, see Erwin's excellent answer. (Let me admit that I feel it dumb that this error message does not tell which role does not have permission. For example, when one runs a chain of functions with SECURITY DEFINER, it pretty fast gets confusing who is who.) – dezso Nov 26 '13 at 9:06
15

DEFAULT PRIVILEGES do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not define the role when running ALTER DEFAULT PRIVILEGES, it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.

Also, since you are using a serial column, which creates a SEQUENCE, you'll want to set default privileges for sequences as well.

Run this on the user you create objects with, before you run the CREATE command:

ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON TABLES TO bspu;
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON SEQUENCES TO bspu;

If you should use pgAdmin, a word of caution. There is a bug in the current version 1.20 (or older) in the display of the reverse engineered SQL script for DEFAULT PRIVILEGES. The display ignores the owning user and is therefore incorrect in certain situations. I reported the bug, the matter is pending.

For existing objects you may also be interested in this "batch" form of the GRANT command:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bspu;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO bspu;

More under this related question on SO:

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.