Postgres 9.1 GRANT does not work

Question

I try to grant privileges like that:

zielony=# GRANT ALL PRIVILEGES ON DATABASE baza_tag to strona_user;
GRANT

But nothing happends:

usename   | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig 
-------------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres    |       10 | t           | t        | t         | t       | ******** |          | 
zielony     |    16384 | t           | t        | t         | t       | ******** |          | 
strona_user |    16440 | f           | f        | f         | f       | ******** |          |

Also I don't have any access via php. What am i missing?

Postgres' privilege system doesn't resemble much the MySQL's one. What do you expect to happen after that GRANT statement? What do you try to do but fails? — Milen A. Radev, Feb 25 '12 at 18:38
I fallow this: [link](http://wiki.gentoo.org/wiki/PostgreSQL). I expected to have permission to select, delete, use functions etc on all tables in database. When I try to select something it yell at me: "No permission to do that" — zie1ony, Feb 25 '12 at 19:11
You should have. But your output does not show the permissions of a user regarding to a certain existing database but whether a user can create a new database (for example). — A.H., Feb 25 '12 at 19:28

Eelke · Accepted Answer · 2022-11-30T17:18:15.267

34

PostgreSQL rights system doesn't work like that. You will have to set rights on the objects themselves. Like so:

GRANT ALL ON ALL TABLES IN SCHEMA public TO strona_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO strona_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO strona_user;

You may also need to grant privileges the your schema's.

GRANT ALL ON SCHEMA public TO strona_user;

edited Nov 30 '22 at 17:18

answered Feb 25 '12 at 21:16

Eelke

20,897
4
50
76

I am using 8.4 and this doesn't work. Flags error on second "ALL". – Brad Sep 13 '12 at 20:17
5

This is indeed only supported on 9.0 or greater. The original question was for 9.1. 8.4 Cannot do this. You can however write a script that queries the system tables to see which tables exist and runs a query for each one of them. – Eelke Sep 16 '12 at 11:19
GRANT ALL ON ALL TABLES IN SCHEMA public TO user does nothing. Incorrect answer. – Tyguy7 Mar 25 '20 at 01:10
2

@Tyguy7, it certainly does something but maybe not what you need. I guess your problem is slightly different but as you didn't bother to post an actual question I cannot help you. – Eelke Mar 25 '20 at 06:07
This answer is wrong or incomplete, which is easy to proof, as you get no results when checking for grants like `select * from information_schema.role_table_grants where grantee='strona_user' and table_schema='public' and is_grantable ='YES' ` – syr Nov 30 '22 at 12:00
must be `GRANT ON ....` AND `GRANT USAGE ..` see next reply, which is the actual correct answer – syr Nov 30 '22 at 12:11
@syr Your filter on `is_grantable ='YES'` is incorrect. The `GRANT` statements did not contain `WITH GRANT OPTION` which is what you are checking for. But I'll give you that for completeness my answer should contain the `GRANT ON SCHEMA` thought for many people the original answer would work because every body has usage rights on the PUBLIC schema per default. – Eelke Nov 30 '22 at 17:15
Also connect to the corresponding database first, etc using psql `/c database_name`. – Simon Jan 02 '23 at 10:41
I just want to add something that happened with me. Even when I type `\du` to list all users I don't see any changes in their roles but these queries worked for me. – mad4n7 Feb 28 '23 at 19:19
@mad4n7, that command cannot list privileges you need to use separate commands like `\l` for all databases, `\dn+` for all schemas (n is for namespace which is still an internal name for schema's), `\z mytablename` for a single table or you can query the catalogs see for some examples https://stackoverflow.com/questions/7336413/query-grants-for-a-table-in-postgres – Eelke Mar 01 '23 at 07:06
I can not thanks you enough man. – Hamedio Aug 04 '23 at 13:19

score 3 · Answer 2 · edited Apr 08 '22 at 19:02

3

Besides the usual GRANT on the Postgres object itself, it is also necessary to grant usage on the schema in which the object is (this is done by the schema owner).

Example:

GRANT ALL ON TABLE <table_name> TO <user_name>
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>

edited Apr 08 '22 at 19:02

Arrowsome

2,649
3
10
35

answered Jan 21 '21 at 23:55

maleckicoa

481
5
8

Postgres 9.1 GRANT does not work

2 Answers2