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_r...@elevated-dev.com https://www.linkedin.com/in/scottribe/
Re: privileges oddity
Scott Ribe 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
Re: privileges oddity
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_r...@elevated-dev.com https://www.linkedin.com/in/scottribe/ -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 6, 2020, at 12:22 PM, Adrian Klaver 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 | | |
Re: privileges oddity
On 8/6/20 11:35 AM, Scott Ribe wrote: On Aug 6, 2020, at 12:22 PM, Adrian Klaver 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.kla...@aklaver.com
Re: privileges oddity
On Aug 6, 2020, at 12:22 PM, Tom Lane 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.
Re: privileges oddity
Scott Ribe writes: > On Aug 6, 2020, at 12:22 PM, Tom Lane 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
Re: privileges oddity
> On Aug 6, 2020, at 12:36 PM, Adrian Klaver wrote: > > No triggers or FOREIGN KEYS? No. No keys or indexes either--that was the entire table def.
Re: privileges oddity
On 8/6/20 11:39 AM, Scott Ribe wrote: On Aug 6, 2020, at 12:36 PM, Adrian Klaver wrote: No triggers or FOREIGN KEYS? No. No keys or indexes either--that was the entire table def. echo "Hmph" -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 6, 2020, at 12:38 PM, Tom Lane 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...)
Re: privileges oddity
Greetings, * Scott Ribe (scott_r...@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 signature.asc Description: PGP signature
Re: privileges oddity
> On Aug 6, 2020, at 12:53 PM, Stephen Frost 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 \
Re: privileges oddity
FYI, REVOKE ALL ON SCHEMA... followed by GRANT ALL ON SCHEMA... did not change anything
Re: privileges oddity
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
Re: privileges oddity
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.kla...@aklaver.com
Re: privileges oddity
>
> 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???
Re: privileges oddity
> On Aug 7, 2020, at 11:31 AM, Scott Ribe 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
Re: privileges oddity
On 8/7/20 10:39 AM, Scott Ribe wrote: On Aug 7, 2020, at 11:31 AM, Scott Ribe 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.kla...@aklaver.com
Re: privileges oddity
> On Aug 7, 2020, at 12:17 PM, Adrian Klaver 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
Re: privileges oddity
On 8/7/20 11:25 AM, Scott Ribe wrote:
On Aug 7, 2020, at 12:17 PM, Adrian Klaver 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.kla...@aklaver.com
Re: privileges oddity
On Aug 7, 2020, at 12:27 PM, Adrian Klaver wrote: > > So what privileges does role 'confidential_read_only' have? read on everything I tried creating a new user without it, just doing the same grants otherwise as for akanzler, that worked. Then I added that user to confidential_read_only, still worked.
Re: privileges oddity
Scott Ribe writes: > On Aug 7, 2020, at 12:27 PM, Adrian Klaver wrote: >> So what privileges does role 'confidential_read_only' have? > read on everything ... including usage on the schema in question? If I'm reading this correctly, you have set things up so that any session logging in as akanzler will immediately do "SET ROLE confidential_read_only", after which it's the privileges of that role not akanzler that determine what happens. regards, tom lane
Re: privileges oddity
> On Aug 7, 2020, at 12:45 PM, Tom Lane wrote: > > If I'm reading this correctly, you have set things up so that any > session logging in as akanzler will immediately do "SET ROLE > confidential_read_only", after which it's the privileges of that > role not akanzler that determine what happens. YES, confidential_read_only has privs on everything *except* individual user's schemas, and rolinherit was accidentally set, that would certainly seem to be the problem. But I turned that off, and it still doesn't work--even in a new connection.
Re: privileges oddity
On 8/7/20 11:56 AM, Scott Ribe wrote: On Aug 7, 2020, at 12:45 PM, Tom Lane wrote: If I'm reading this correctly, you have set things up so that any session logging in as akanzler will immediately do "SET ROLE confidential_read_only", after which it's the privileges of that role not akanzler that determine what happens. YES, confidential_read_only has privs on everything *except* individual user's schemas, and rolinherit was accidentally set, that would certainly seem to be the problem. But I turned that off, and it still doesn't work--even in a new connection. https://www.postgresql.org/docs/12/sql-set-role.html "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privileges available to the named role. " -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 7, 2020, at 1:08 PM, Adrian Klaver wrote: > > "Using this command, it is possible to either add privileges or restrict > one's privileges. If the session user role has the INHERIT attribute, then it > automatically has all the privileges of every role that it could SET ROLE to; > in this case SET ROLE effectively drops all the privileges assigned directly > to the session user and to the other roles it is a member of, leaving only > the privileges available to the named role. On the other hand, if the session > user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned > directly to the session user and instead acquires the privileges available to > the named role. > " So it would only have removed privs if I had used set role in the session, which I am not.
Re: privileges oddity
Scott Ribe writes: > So it would only have removed privs if I had used set role in the session, > which I am not. Yes, you are. It looks like what you actually issued is ALTER USER akanzler SET role confidential_read_only; but that would have the effect that subsequent session starts would automatically do "SET ROLE confidential_read_only". regards, tom lane
Re: privileges oddity
On 8/7/20 12:27 PM, Scott Ribe wrote: On Aug 7, 2020, at 1:08 PM, Adrian Klaver wrote: "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privileges available to the named role. " So it would only have removed privs if I had used set role in the session, which I am not. See Tom's answer. To confirm do: SELECT s.setdatabase, s.setrole, rolname, s.setconfig, rolname FROM pg_db_role_setting AS s JOIN pg_roles AS r ON r.oid = s.setrole; -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
On 8/7/20 12:40 PM, Adrian Klaver wrote: On 8/7/20 12:27 PM, Scott Ribe wrote: On Aug 7, 2020, at 1:08 PM, Adrian Klaver wrote: "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privileges available to the named role. " So it would only have removed privs if I had used set role in the session, which I am not. See Tom's answer. To confirm do: SELECT s.setdatabase, s.setrole, rolname, s.setconfig, rolname^^^ Surplus to requirements FROM pg_db_role_setting AS s JOIN pg_roles AS r ON r.oid = s.setrole; Also log in as 'akanzler' to psql and do: select session_user; select current_user; -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 7, 2020, at 1:32 PM, Tom Lane wrote: > > Yes, you are. It looks like what you actually issued is > > ALTER USER akanzler SET role confidential_read_only; > > but that would have the effect that subsequent session starts would > automatically do "SET ROLE confidential_read_only". AHA! This is the correct answer, and it is solved now. I *know* I executed grant role properly--it's right there in the script. However, I think that SET ROLE had been accidentally misused instead of GRANT at some point in the past, *AND* that the role contained privs to the user-specific schemas when it should not have. So revoking all privs from the role and adding back the proper ones resulted in inadvertently removing privs from users who'd properly had them explicitly granted. (I've never even used SET ROLE and was unaware you could even do that!) Anyway, thanks a million for being patient and sticking with this.
Re: privileges oddity
So, one last follow-up, perhaps \du or \du+ should show when a role is mapped that way. If I'd seen a clue to this setting that had been made "before I got here" it would have been figured out sooner. I realize ALTER ROLE... SET... can be used to set many more defaults, and there could be some debate about how much to display with \du[+], but the fact that a role abandons all its privs and adopts a different set seems like pretty important info to surface ;-)
Re: privileges oddity
Scott Ribe writes: > So, one last follow-up, perhaps \du or \du+ should show when a role is mapped > that way. If I'd seen a clue to this setting that had been made "before I got > here" it would have been figured out sooner. \drds does already show this; of course, you have to know to look at it, but the same could be said of \du ... > I realize ALTER ROLE... SET... can be used to set many more defaults, and > there could be some debate about how much to display with \du[+], but the > fact that a role abandons all its privs and adopts a different set seems like > pretty important info to surface ;-) IIRC, you aren't the first to get burnt this way. I've wondered for some time if we shouldn't forbid certain GUCs from being set via ALTER ROLE or ALTER DATABASE. "role" and "session authorization" are the poster children here but there might be others. On the other hand, if we do so somebody will likely complain that they have a legit use-case for it. regards, tom lane
