Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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;
GRANT
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=>
tmadev=> \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 Chloe     | Superuser, Create role, Create DB, Replication | {}
 tma       |                                                | {}
share|improve this question
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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.