Thread: 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:
permissiondenied 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:
permissiondenied 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: permissiondenied 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 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
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, perPG's error in the log.
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 startedout 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 whichfubar'd something in system catalog... Maybe also relevant that the original creator & owner of the schema (zoewang) was dropped after schema owner was changedsrv_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: permissiondenied 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
Attachment
> 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.
Continuinggrasping 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.
Continuinggrasping 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
On Aug 7, 2020, at 12:27 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 thatuser to confidential_read_only, still worked.
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> On Aug 7, 2020, at 12:27 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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
> On Aug 7, 2020, at 12:45 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.
On 8/7/20 11:56 AM, Scott Ribe wrote: >> On Aug 7, 2020, at 12:45 PM, Tom Lane <tgl@sss.pgh.pa.us> 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 accidentallyset, that would certainly seem to be the problem. But I turned that off, and it still doesn't work--even in anew 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.klaver@aklaver.com
> On Aug 7, 2020, at 1:08 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role hasthe INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this caseSET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a memberof, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERITattribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privilegesavailable to the named role. > " So it would only have removed privs if I had used set role in the session, which I am not.
Scott Ribe <scott_ribe@elevated-dev.com> 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
On 8/7/20 12:27 PM, Scott Ribe wrote:
>> On Aug 7, 2020, at 1:08 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role
hasthe INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this
caseSET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a
memberof, leaving only the privileges available to the named role. On the other hand, if the session user role has the
NOINHERITattribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the
privilegesavailable 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.klaver@aklaver.com
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 <adrian.klaver@aklaver.com> >>> 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.klaver@aklaver.com
> On Aug 7, 2020, at 1:32 PM, Tom Lane <tgl@sss.pgh.pa.us> 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 <role> at some point in the past, *AND* thatthe role contained privs to the user-specific schemas when it should not have. So revoking all privs from the role andadding 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.
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 settingthat 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 displaywith \du[+], but the fact that a role abandons all its privs and adopts a different set seems like pretty importantinfo to surface ;-)
Scott Ribe <scott_ribe@elevated-dev.com> 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
settingthat 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
todisplay with \du[+], but the fact that a role abandons all its privs and adopts a different set seems like pretty
importantinfo 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