pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUser granted all access to schema, but can't see tables
5+ messages / 3 participants
[nested] [flat]
* User granted all access to schema, but can't see tables
@ 2020-12-18 12:07 Ron <ronljohnsonjr@gmail.com>
2020-12-18 12:26 ` Re: User granted all access to schema, but can't see tables John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2020-12-18 12:07 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User granted all access to schema, but can't see tables
2020-12-18 12:07 User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
@ 2020-12-18 12:26 ` John Scalia <jayknowsunix@gmail.com>
2020-12-18 12:57 ` Re: User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: John Scalia @ 2020-12-18 12:26 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User granted all access to schema, but can't see tables
2020-12-18 12:07 User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
2020-12-18 12:26 ` Re: User granted all access to schema, but can't see tables John Scalia <jayknowsunix@gmail.com>
@ 2020-12-18 12:57 ` Ron <ronljohnsonjr@gmail.com>
2020-12-18 13:06 ` Re: User granted all access to schema, but can't see tables John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2020-12-18 12:57 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User granted all access to schema, but can't see tables
2020-12-18 12:07 User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
2020-12-18 12:26 ` Re: User granted all access to schema, but can't see tables John Scalia <jayknowsunix@gmail.com>
2020-12-18 12:57 ` Re: User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
@ 2020-12-18 13:06 ` John Scalia <jayknowsunix@gmail.com>
2020-12-18 13:08 ` RE: User granted all access to schema, but can't see tables Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
0 siblings, 1 reply; 5+ messages in thread
From: John Scalia @ 2020-12-18 13:06 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
Try doing a \dpp <table_name> and see if the table contains a line for the stansuser. Perhaps there was a problem with the default privileges. Oh, and case you didn’t know this, newly set default privileges only effect an object created after the fact, they do not alter privileges for any existing object.
Sent from my iPad
> On Dec 18, 2020, at 7:57 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> 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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: User granted all access to schema, but can't see tables
2020-12-18 12:07 User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
2020-12-18 12:26 ` Re: User granted all access to schema, but can't see tables John Scalia <jayknowsunix@gmail.com>
2020-12-18 12:57 ` Re: User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
2020-12-18 13:06 ` Re: User granted all access to schema, but can't see tables John Scalia <jayknowsunix@gmail.com>
@ 2020-12-18 13:08 ` Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kanninen Anssi EXT @ 2020-12-18 13:08 UTC (permalink / raw)
To: pgsql-admin
If you want to grant the privileges after the tables have created, do:
GRANT ALL ON ALL TABLES IN SCHEMA strans TO stransuser;
-----Original Message-----
From: John Scalia <jayknowsunix@gmail.com>
Sent: perjantai 18. joulukuuta 2020 15.06
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: User granted all access to schema, but can't see tables
Try doing a \dpp <table_name> and see if the table contains a line for the stansuser. Perhaps there was a problem with the default privileges. Oh, and case you didn’t know this, newly set default privileges only effect an object created after the fact, they do not alter privileges for any existing object.
Sent from my iPad
> On Dec 18, 2020, at 7:57 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> 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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-12-18 13:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-18 12:07 User granted all access to schema, but can't see tables Ron <ronljohnsonjr@gmail.com>
2020-12-18 12:26 ` John Scalia <jayknowsunix@gmail.com>
2020-12-18 12:57 ` Ron <ronljohnsonjr@gmail.com>
2020-12-18 13:06 ` John Scalia <jayknowsunix@gmail.com>
2020-12-18 13:08 ` Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox