How do I grant permissions in Postgres? I followed the documentation, however it is not working.

$ psql tmadev
psql (9.2.4)
Type "help" for help.

tmadev=# grant all privileges on database tmadev to tma;
tmadev=# \z sample
                           Access privileges
 Schema |  Name  | Type  | Access privileges | Column access privileges
 public | sample | table |                   |
(1 row)

tmadev=# \q

Chloe@xps /srv/www/htdocs
$ psql -U tma tmadev
psql (9.2.4)
Type "help" for help.

tmadev=> select * from sample limit 2;
ERROR:  permission denied for relation sample
STATEMENT:  select * from sample limit 2;
ERROR:  permission denied for relation sample
tmadev=> \du
                             List of roles
 Role name |                   Attributes                   | Member of
 Chloe     | Superuser, Create role, Create DB, Replication | {}
 tma       |                                                | {}
  • 2
    I believe it is a Postgres problem over a cygwin problem eventhough you are running postgres under cygwin. – mdpc Aug 20 '13 at 20:58
  • Are there any errors in your postgres log? Also note that this sort of question is probably more appropriate for (and may already be answered on) our Database Administrators site. If you don't get an answer here in a day or so let me know and I'll migrate the question for you. – voretaq7 Aug 20 '13 at 21:26
  • @mdpc Yes it was a Postgres problem. @ voretaq7 Oh there's a new DBA site? Awesome! – Chloe Aug 23 '13 at 0:50

grant all privileges on database... does not grant any possible privilege within the database, but on it, which is much less than you'd think.

Per documentation, the privileges on a database are defined as:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

So grant all privileges on database tmadev to tma is equivalent to:

grant create,connect,temporary  on database tmadev to tma;

Presumably you want something like (when connected to tmadev)

grant all on all tables in schema public to tma;
grant all on all sequences in schema public to tma;
grant all on schema public to tma;

and possibly quite a few others.

On the other hand, if tma is going to be the only user or group that needs full access to this database, it's much more convenient to make it the owner of the database, in which case it has all the privileges within it and none of these grants are necessary.

  • 1
    Ah, thanks! I didn't want to grant all public tables to tma because there are other databases and other tables for other users. I ended up listing all tables \d and saw owner was not tma! Probably an error during import. I made a large text file of all tables and created commands to set owner to 'tma', copied/pasted, and it worked! Like ALTER TABLE wind_direction OWNER TO tma; – Chloe Aug 23 '13 at 0:57

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.