PostgreSQL Permissions – Permission Denied for Relation

permissionspostgresql

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.

Best Answer

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:

• Grant all on a specific schema in the db to a group role in PostgreSQL

Related Solutions

Postgresql – Problem with permissions for user

GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ALL on a database just grants CONNECT and TEMPORARY rights to the database, with no effect on contained schemas and tables.

The default GRANTs are, from the docs on GRANT:

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.

So you can see you don't need to GRANT anything on the database unless you want the user to be able to create schemas, etc. You need to:

• GRANT USAGE ON SCHEMA myschema TO theuser; for any schema other than public. CREATE can be granted if you want the user to be able to make tables, views, etc.
• GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE sometable TO theuser; for tables. I've omitted the TRUNATE, REFERENCES and TRIGGER rights as you probably don't want to grant them.
• GRANT USAGE ON SEQUENCE sometable_somecolumn_seq TO theuser; for any sequences that are used in table defaults, either explicitly or via a SERIAL or BIGSERIAL column.

... etc. See the manual for GRANT linked above for full definitions of what the privileges do, which are available on which objects, etc. Take note of the wildcard ALL TABLES and ALL SEQUENCES options.

If this seems like too much hassle to do for each table, view, schema, sequence, etc, you can in Pg 9.1 and above use ALTER DEFAULT PRIVILEGES to change the default GRANTs on new objects.

• The manual for GRANT
• The manual for ALTER DEFAULT PRIVILEGES

PostgreSQL Privileges – Database Owner and Application User

You want ALTER DEFAULT PRIVILEGES.

Give the rds_superuser default access rights to all new tables.

This only affects tables created after the ALTER. For existing tables you must GRANT rights.

Related Question

• Postgresql – Adding new user in Postgres
• PostgreSQL – Group Permissions Not Inheriting to Table Level
• PostgreSQL – Resolving ‘Alter Default Privileges’ Issue
• Sql-server – The EXECUTE permission was denied on the object ‘SPROC’, database ‘DATABASE’, schema ‘dbo’
• Mysql – Creator of a MySQL table automatically granted all table permissions
• PostgreSQL – Administration as Non-Superuser