privileges oddity
when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", it triggers this error:
2020-08-06 17:27:27.664 UTC [15914]: [3] user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: permission denied for schema zoewang at character 15
--- YET ---risk_oltp_prod=# \dn+ zoewang
List of schemas
Name | Owner | Access privileges | Description
---------+----------+------------------------+-------------
zoewang | srv_risk | srv_risk=UC/srv_risk +|
| | akanzler=UC/srv_risk +|
| | srv_risk_ro=U/srv_risk |
(1 row)
HUH? (And the user also has all privs on all the tables in the schema...)
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
Scott Ribe <scott_ribe@elevated-dev.com> writes:
when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", it triggers this error:
2020-08-06 17:27:27.664 UTC [15914]: [3] user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: permission denied for schema zoewang at character 15
Gonna need more context. The session-level user seems to have the
right privileges, but maybe something is happening inside a
security-definer function that doesn't have privileges?
regards, tom lane
On 8/6/20 11:11 AM, Scott Ribe wrote:
when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", it triggers this error:
2020-08-06 17:27:27.664 UTC [15914]: [3] user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: permission denied for schema zoewang at character 15
--- YET ---risk_oltp_prod=# \dn+ zoewang
List of schemas
Name | Owner | Access privileges | Description
---------+----------+------------------------+-------------
zoewang | srv_risk | srv_risk=UC/srv_risk +|
| | akanzler=UC/srv_risk +|
| | srv_risk_ro=U/srv_risk |
(1 row)HUH? (And the user also has all privs on all the tables in the schema...)
Schema for the table?
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
--
Adrian Klaver
adrian.klaver@aklaver.com
On Aug 6, 2020, at 12:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Schema for the table?
Nothing relevant:
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
curve_name | character varying(30) | | |
curve_type | character varying(15) | | |
tenor_name | character varying(10) | | |
tenor_date | date | | |
value_date | date | | |
curve_value | numeric | | |
On Aug 6, 2020, at 12:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gonna need more context. The session-level user seems to have the
right privileges, but maybe something is happening inside a
security-definer function that doesn't have privileges?
The only security definer function in the db is a simple pg_shadow lookup used by pgbouncer.
Hmm, I should check both direct to PG and through PG bouncer--even though he is getting connected as the correct user, per PG's error in the log.
On 8/6/20 11:35 AM, Scott Ribe wrote:
On Aug 6, 2020, at 12:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Schema for the table?
Nothing relevant:
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
curve_name | character varying(30) | | |
curve_type | character varying(15) | | |
tenor_name | character varying(10) | | |
tenor_date | date | | |
value_date | date | | |
curve_value | numeric | | |
No triggers or FOREIGN KEYS?
--
Adrian Klaver
adrian.klaver@aklaver.com
Scott Ribe <scott_ribe@elevated-dev.com> writes:
On Aug 6, 2020, at 12:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gonna need more context. The session-level user seems to have the
right privileges, but maybe something is happening inside a
security-definer function that doesn't have privileges?
The only security definer function in the db is a simple pg_shadow lookup used by pgbouncer.
Hmph. Any chance of getting a stack trace from the point of the error?
Also, which PG version is this?
regards, tom lane
On Aug 6, 2020, at 12:36 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
No triggers or FOREIGN KEYS?
No. No keys or indexes either--that was the entire table def.
On 8/6/20 11:39 AM, Scott Ribe wrote:
On Aug 6, 2020, at 12:36 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
No triggers or FOREIGN KEYS?
No. No keys or indexes either--that was the entire table def.
echo "Hmph"
--
Adrian Klaver
adrian.klaver@aklaver.com
On Aug 6, 2020, at 12:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hmph. Any chance of getting a stack trace from the point of the error?
possibly
Also, which PG version is this?
12.3
It is probably relevant that we cleaned up roles & privs yesterday, lots of REVOKE & GRANT, and some DROP ROLE. I started out thinking I'd made a mistake with that, but now I'm starting to wonder if there's a bug I hit in some of that which fubar'd something in system catalog...
Maybe also relevant that the original creator & owner of the schema (zoewang) was dropped after schema owner was changed srv_risk. (Likewise, changed ownership of tables in the schema...)
Greetings,
* Scott Ribe (scott_ribe@elevated-dev.com) wrote:
when user akanzler tries to run query "SELECT * FROM zoewang.sometable...", it triggers this error:
2020-08-06 17:27:27.664 UTC [15914]: [3] user=akanzler,db=risk_oltp_prod,app=[unknown],client=10.8.170.24: ERROR: permission denied for schema zoewang at character 15
--- YET ---risk_oltp_prod=# \dn+ zoewang
List of schemas
Name | Owner | Access privileges | Description
---------+----------+------------------------+-------------
zoewang | srv_risk | srv_risk=UC/srv_risk +|
| | akanzler=UC/srv_risk +|
| | srv_risk_ro=U/srv_risk |
(1 row)HUH? (And the user also has all privs on all the tables in the schema...)
Are you 110% sure that you're actually connecting to the same instance
in both cases (I'd say database too, but hopefully psql isn't lying to
you about that on your prompt, but maybe double-check anyway...).
Have you re-tried from the app (maybe someone fixed it in the
meantime)?
Thanks,
Stephen
On Aug 6, 2020, at 12:53 PM, Stephen Frost <sfrost@snowman.net> wrote:
Are you 110% sure that you're actually connecting to the same instance
in both cases (I'd say database too, but hopefully psql isn't lying to
you about that on your prompt, but maybe double-check anyway...).
yes--double checked
Have you re-tried from the app (maybe someone fixed it in the
meantime)?
still seeing it from psql
\
FYI, REVOKE ALL ON SCHEMA... followed by GRANT ALL ON SCHEMA... did not change anything
Further update:
create a new user, grant all on schema & the table, works
reboot of server did not change anything, so the problem is in persistent state
On 8/7/20 9:23 AM, Scott Ribe wrote:
Further update:
create a new user, grant all on schema & the table, works
reboot of server did not change anything, so the problem is in persistent state
What happens if you do?:
select has_schema_privilege('akanzler', 'zoewang', 'usage');
In psql what does
\ddp
show?
--
Adrian Klaver
adrian.klaver@aklaver.com
What happens if you do?:
select has_schema_privilege('akanzler', 'zoewang', 'usage');
risk_oltp_prod=# select has_schema_privilege('akanzler', 'zoewang', 'usage');
has_schema_privilege
----------------------
t
(1 row)
In psql what does
\ddp
show?
risk_oltp_prod=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
----------------+----------+-------+----------------------------------------
...
srv_risk | zoewang | table | akanzler=r/srv_risk +
| | | srv_risk=arwdD/srv_risk
...
(40 rows)
Wondering if there's a code path somewhere that lets the default take precedence???
On Aug 7, 2020, at 11:31 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
Wondering if there's a code path somewhere that lets the default take precedence???
So, I changed the defaults, now I see akanzler=arwdDxt/srv_risk, problem persists
On 8/7/20 10:39 AM, Scott Ribe wrote:
On Aug 7, 2020, at 11:31 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
Wondering if there's a code path somewhere that lets the default take precedence???
So, I changed the defaults, now I see akanzler=arwdDxt/srv_risk, problem persists
Well if this for the same line as before it represents table privileges.
The problem is with schema access. Continuing grasping at straws:
select * from pg_roles where rolname = 'aakanzler';
--
Adrian Klaver
adrian.klaver@aklaver.com
On Aug 7, 2020, at 12:17 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Well if this for the same line as before it represents table privileges. The problem is with schema access. Continuing grasping at straws:
select * from pg_roles where rolname = 'aakanzler';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-------------------------------+-------
akanzler | f | t | f | f | t | f | -1 | ******** | | f | {role=confidential_read_only} | 16391
On 8/7/20 11:25 AM, Scott Ribe wrote:
On Aug 7, 2020, at 12:17 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Well if this for the same line as before it represents table privileges. The problem is with schema access. Continuing grasping at straws:
select * from pg_roles where rolname = 'aakanzler';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-------------------------------+-------
akanzler | f | t | f | f | t | f | -1 | ******** | | f | {role=confidential_read_only} | 16391
So what privileges does role 'confidential_read_only' have?
--
Adrian Klaver
adrian.klaver@aklaver.com