Permission denied for relation <table>

Question

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.

Answer 1

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 specify 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;

A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES ignoring the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.

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

Answer 2

If the error

ERROR:  permission denied for relation migrations

pops up when trying to do a GRANT ACTION,

example:GRANT SELECT ON ALL TABLES IN SCHEMA schema_name to ro_user;

ensure the GRANT is performed by the user that is the owner of the respective tables. Owner of schemas might vary within a database. Even postgres user will not be allowed to provide this GRANT access.

Answer 3

For PostgreSQL. On bash terminal, run this:

psql db_name -c "GRANT ALL ON ALL TABLES IN SCHEMA public to db_user;"
psql db_name -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to db_user;"
psql db_name -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to db_user;"

Answer 4

This is working for me after some research. psql (9.6.12)

-- login to postgres database server as postgres user.
ssh <user>>@hostname.<domain>>.com
sudo su postgres
id
psql
postgres=# create user sentry_read with login password 'sentry_read';
postgres=# du+
postgres=# \l+
-- **very important: make sure we should connect required database before we grant to user.**
postgres=# \c sentry
postgres=# \dt
sentry=# \dp SENTRY_VERSION
sentry=# select * from "SENTRY_VERSION";
sentry=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO sentry_read;
postgres=# \q

[user@hostname ~]$ psql -U sentry_read -d sentry -h hostname.<domain>>.com -W
sentry=> select * from "SENTRY_VERSION";

Answer 5

This helped me:

ALTER TABLE IF EXISTS extensao.tipo_prestacao_servico
    OWNER to sigaa;

ALTER TABLE IF EXISTS extensao.categoria_prestacao_servico
    OWNER to sigaa;