How do I grant select, insert privileges in Postgres on Cygwin?

Question

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       |                                                | {}

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 [dba.SE] 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 00:50

score 4 · Accepted Answer · answered Aug 22 '13 at 21:34

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.

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 00:57

How do I grant select, insert privileges in Postgres on Cygwin?

1 Answers1