3

I have a schema of a table in PostgreSQL that looks like:

CREATE TABLE IF NOT EXISTS  data.pmacct (

    i                       BIGSERIAL PRIMARY KEY,

And there are users:

  • A: superuser privileges
  • B: read/write privileges
  • C: read privileges

And I have made the following modifications in regards to the privileges (as user A)

CREATE SCHEMA data ;
ALTER DATABASE ip_spotlight SET search_path TO data ;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA data FROM PUBLIC ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT SELECT ON TABLES TO C ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO B ;

as user postgres

GRANT USAGE ON SCHEMA data TO netops ;
GRANT USAGE ON SCHEMA data TO netopsapp ;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO C ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA data TO C ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data TO C ;
GRANT ALL ON ALL TABLES IN SCHEMA data TO B ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA data TO B ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA data TO B ;

But I am getting the following error message as user B

ERROR: permission denied for sequence pmacct_i_seq

Could you please enlighten me what I have done wrong and user B is not able to do an INSERT statement into the data.pmacct table ?

4
  • You should have an error, but not that one. When is B granted USAGE to the data schema? Nov 9, 2017 at 12:51
  • @DanielVérité am not sure if i understand the question: doesn't ALL include USAGE ?
    – nskalis
    Nov 9, 2017 at 16:52
  • What ALL means depends on the ON clause. In the case of the schema itself , there's no GRANT ALL ON SCHEMA data to B in the commands mentioned above. Nov 9, 2017 at 19:45
  • Could you please show the output of \dp pmacct_i_seq (run in psql)?
    – dezso
    Mar 9, 2018 at 14:41

3 Answers 3

1
GRANT USAGE, SELECT ON SEQUENCE some_table_name_i_seq TO some_user;

In this case:

GRANT USAGE, SELECT ON SEQUENCE pmacct_i_seq TO B;
0

You miss

ALTER DEFAULT PRIVILEGES GRANT USAGE ON SEQUENCES TO B;

pmacct_i_seq is a sequence that backs the BIGSERIAL column i. Since user B can modify the table, it needs the privilege to access the sequence.

-2

Try to make changes on all tables Answer from Alex Soto


Since you're changing the ownership for all tables, you likely want views and sequences too. Here's what I did:

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

You could probably [DRY][1] that up a bit since the alter statements are identical for all three.

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.