Subject: Re: User granted all access to schema, but can't see tables
To: pgsql-admin <pgsql-admin@postgresql.org>
References: <0bfc7514-e4ee-375e-be44-24479e728adc@gmail.com>
 <41AFAC73-A145-4EAC-B211-70D12AFD7D77@gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <43309465-eb73-557c-8941-ddad7e31a5ff@gmail.com>
Date: Fri, 18 Dec 2020 06:57:37 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <41AFAC73-A145-4EAC-B211-70D12AFD7D77@gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

The database name is "sides", as show in the psql prompt.  User "stransuser" 
can connect to the database as shown below when it tries to query tables but 
gets "permission denied".

On 12/18/20 6:26 AM, John Scalia wrote:
> Does the stansuser have connect privileges to the actual database where the schema and tables are?
>
> GRANT connect ON DATABASE <stans_whatever> TO stansuser;
>
> Note that since you didn’t tell us what the database name is here, you’ll need to substitute the actual name of it where I indicated <stans_whatever>.
> —
> Jay
>
> Sent from my iPad
>
>> On Dec 18, 2020, at 7:07 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> ﻿This is v12 from RHEL repos.
>>
>> As user postgres, I run:
>>
>> sides=# grant all on SCHEMA strans TO stransuser;
>> GRANT
>>
>> sides=# ALTER DEFAULT PRIVILEGES IN SCHEMA strans GRANT all on tables to stransuser;
>> ALTER DEFAULT PRIVILEGES
>>
>> sides=# GRANT USAGE ON SCHEMA strans TO stransuser;
>> GRANT
>>
>> But user stransuser still can’t access tables
>>
>> sides=> \d
>>               List of relations
>> Schema |    Name    |   Type   |  Owner
>> --------+------------+----------+----------
>> strans | foo        | table    | postgres
>> strans | foo_id_seq | sequence | postgres
>> (2 rows)
>>
>> sides=> select * from strans.foo;
>> ERROR:  permission denied for table foo
>>
>> sides=> \z strans.foo
>>                                  Access privileges
>> Schema | Name | Type  |     Access privileges     | Column privileges | Policies
>> --------+------+-------+---------------------------+-------------------+----------
>> strans | foo  | table | postgres=arwdDxt/postgres+|                   |
>>         |      |       | READWRITE=arwdD/postgres +|                   |
>>         |      |       | READONLY=r/postgres |                   |
>> (1 row)
>>
>> What am I missing?
>>
>> -- 
>> Angular momentum makes the world go 'round.
>>
>>

-- 
Angular momentum makes the world go 'round.