1

I have been stuck on this problem for a while now and cannot figure it out. Hoping someone can help me.

I think my situation is pretty simple, so I feel extra stupid for having to post this Nonetheless -- I have a database, lets call it tempdb, that was created by user ikaros on Postgres 13.3 (Ubuntu 13.3-1.pgdg16.04+1)

Here is the output from \l+ with irrelevant information omitted.

                                                                          List of databases
         Name          |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 ...
 ikaros                | ikaros   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8029 kB | pg_default |
 tempdb                | ikaros   | UTF8     | C           | C           | =T/ikaros            +| 13 GB   | pg_default |
                       |          |          |             |             | ikaros=CTc/ikaros    +|         |            |
                       |          |          |             |             | johndoe=CTc/ikaros    |         |            |
...

Currently, johndoe can connect to the database tempdb, but when executing a query, gets a message about not having sufficient table level privilege's. Error: Unable to execute query: Fatal Error; Reason: Error: (ERROR: permission denied for table settings )

I want johndoe to have full read privilege's on the tempdb along with all tables inside. How can I go about that? Thanks in advance!

dmcmulle
  • 339
  • 3
  • 11

1 Answers1

1

According to Postgres documents, You can use below queries to add permission to users:

-- This query used for access to the database
grant connect on database [YOUR_DATABASE] to [USERNAME];

-- This query used for access to the schema
grant usage on schema [SCHEMA1, SCHEMA2, ...] to [USERNAME];

-- This query is used for access to all tables of a schema (or can use just **select** instead of **all**
grant all on all tables in schema [SCHEMA1, SCHEMA2, ...] to [USERNAME];
grant select on all tables in schema [SCHEMA1, SCHEMA2, ...] to [USERNAME];

-- If need add select permission to a specific table
grant select on table [YOUR_SCHEMA].[YOUR_TABLE] to [USERNAME];
Pooya
  • 2,968
  • 2
  • 12
  • 18
  • Just a slight add to the answer by @Pooya. Since user `ikaros` owns the database then that user must issue the above grants, or the superuser (posgres?) can issue them. – Belayer Dec 19 '21 at 06:10
  • Both of them can alter permission expect `grant connect` permission, this permission should alter by superuser – Pooya Dec 19 '21 at 06:16
  • Yes, but it must be one of them, or another superuser. Perhaps I misread the question but it seems OP is neither. Therefore they cannot use their own `user` i.d. Agreed with connect, but then again it appears OP already has that. – Belayer Dec 19 '21 at 06:25