Postgresql – How to grant select, insert privileges in Postgres on Cygwin

cygwinpostgresqlsql

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

Best Answer

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.

Related Topic