Re: [HACKERS] postgres_fdw super user checks
Robert, all, * Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Oct 5, 2017 at 1:02 PM, Jeff Janeswrote: > > I don't see a reason to block a directly-logged-in superuser from using a > > mapping. I asked in the closed list whether the current (released) > > behavior was a security bug, and the answer was no. And I don't know why > > else to block superusers from doing something other than as a security bug. > > Also it would create a backwards compatibility hazard to revoke the ability > > now. > > Well, my thought was that we ought to be consistent about whose > authorization matters. If we're using the view owner's credentials in > general, then we also (defensibly, anyway) ought to use the view > owner's superuser-ness to decide whether to enforce this restriction. > Using either the view owner's superuser-ness or the session user's > superuser-ness kind of puts you halfway in the middle. The view > owner's rights are what matters mostly, but your own rights also > matter a little bit around the edges. That's a little strange. > > I don't have violently strong opinions about this - does anyone else > have a view? I haven't been following this closely, but I tend to agree with you- if we're using the view owner's privileges then that's what everything should be based on, not whether the caller is a superuser or not. Consider a security-definer function. Clearly, such a function should always run as the owner of the function, even if the caller is a superuser. Running as the caller instead of the owner of the function when the caller is a superuser because that would allow the function to access more clearly isn't a good idea, imv. Yes, that means that sometimes when superusers run things they get permission denied errors. That's always been the case, and is correct. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] postgres_fdw super user checks
On Thu, Oct 5, 2017 at 1:02 PM, Jeff Janeswrote: > I don't see a reason to block a directly-logged-in superuser from using a > mapping. I asked in the closed list whether the current (released) > behavior was a security bug, and the answer was no. And I don't know why > else to block superusers from doing something other than as a security bug. > Also it would create a backwards compatibility hazard to revoke the ability > now. Well, my thought was that we ought to be consistent about whose authorization matters. If we're using the view owner's credentials in general, then we also (defensibly, anyway) ought to use the view owner's superuser-ness to decide whether to enforce this restriction. Using either the view owner's superuser-ness or the session user's superuser-ness kind of puts you halfway in the middle. The view owner's rights are what matters mostly, but your own rights also matter a little bit around the edges. That's a little strange. I don't have violently strong opinions about this - does anyone else have a view? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On 4 October 2017 at 18:13, Jeff Janeswrote: > On Thu, Sep 14, 2017 at 1:08 PM, Robert Haas wrote: >> >> On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janes wrote: >> > I think that foreign tables ought to behave as views do, where they run >> > as >> > the owner rather than the invoker. No one has talked me out of it, but >> > no >> > one has supported me on it either. But I think it is too late to change >> > that now. >> >> That's an interesting point. I think that you can imagine use cases >> for either method. Obviously, if what you want to do is drill a hole >> through the Internet to another server and then expose it to some of >> your fellow users, having the FDW run with the owner's permissions >> (and credentials) is exactly right. But there's another use case too, >> which is where you have something that looks like a multi-user >> sharding cluster. You want each person's own credentials to carry >> over to everything they do remotely. > > > OK. And if you want the first one, you can wrap it in a view currently, but > if it were changed I don't know what you would do if you want the 2nd one > (other than having every user create their own set of foreign tables). So I > guess the current situation is more flexible. Sounds like it would be a useful option on a Foreign Server to allow it to run queries as either the invoker or the owner. We have that choice for functions, so we already have the concept and syntax available. We could have another default at FDW level that specifies what the default is for that type of FDW, and if that is not specified, we keep it like it currently is. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Thu, Sep 14, 2017 at 04:08:08PM -0400, Robert Haas wrote: > On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janeswrote: > > I think that foreign tables ought to behave as views do, where they run as > > the owner rather than the invoker. No one has talked me out of it, but no > > one has supported me on it either. But I think it is too late to change > > that now. > > That's an interesting point. I think that you can imagine use cases > for either method. Obviously, if what you want to do is drill a hole > through the Internet to another server and then expose it to some of > your fellow users, having the FDW run with the owner's permissions > (and credentials) is exactly right. But there's another use case too, > which is where you have something that looks like a multi-user > sharding cluster. You want each person's own credentials to carry > over to everything they do remotely. Hmmm, I don't think that's really right. What I'd like instead is for the FDW client to tell the FDW server the session_user/current_user on behalf of which it's acting, and let the FDW server decide how to proceed. This could be done by doing a SET SESSION fdw.client.session_user... and so on. We use Kerberos principal names as PG user/role names, _with_ @REALM included, so a user foo@BAR is likely to make sense to the FDW server. Of course, if you're not using Kerberos then the local and remote user namespaces might be completely distinct, but by letting the FDW server know a) the FDW client's username (via authentication) and b) the true username on the client side (via SET/set_config()), the server might have enough information to decide whether it trusts (a) to impersonate (b) and how to map (b) to a local user. Nico -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Thu, Oct 5, 2017 at 6:44 AM, Robert Haaswrote: > On Wed, Oct 4, 2017 at 6:13 PM, Jeff Janes wrote: > > OK. And if you want the first one, you can wrap it in a view currently, > but > > if it were changed I don't know what you would do if you want the 2nd one > > (other than having every user create their own set of foreign tables). > So I > > guess the current situation is more flexible. > > So where does that leave this patch? Sorry, I thought we were just having a digression. I didn't think that part was about this patch specifically, but what more could be done later. > I don't think it makes this > patch a bad idea, although I kind of lean towads the view that the > patch should just be checking superuser_arg(), not superuser() || > superuser_arg(). > I don't see a reason to block a directly-logged-in superuser from using a mapping. I asked in the closed list whether the current (released) behavior was a security bug, and the answer was no. And I don't know why else to block superusers from doing something other than as a security bug. Also it would create a backwards compatibility hazard to revoke the ability now. Cheers, Jeff
Re: [HACKERS] postgres_fdw super user checks
On Wed, Oct 4, 2017 at 6:13 PM, Jeff Janeswrote: > OK. And if you want the first one, you can wrap it in a view currently, but > if it were changed I don't know what you would do if you want the 2nd one > (other than having every user create their own set of foreign tables). So I > guess the current situation is more flexible. So where does that leave this patch? I don't think it makes this patch a bad idea, although I kind of lean towads the view that the patch should just be checking superuser_arg(), not superuser() || superuser_arg(). > It does seem like it would then be a good idea to have a user mapping option > of "pass_the_hash" which would look up md5 hash from the pg_authid (if the > local username is the same as the remote user name) and use that to connect > to the foreign server, as an alternative option to recording the password in > plain text in the mapping itself. But that would require some changes to > libpq, not just postgres_fdw. > > And that wouldn't work for SCRAM. I guess that SCRAM does have some feature > to allow this kind of delegation, but I don't know enough about it to know > how hard it would be to implement in postgres_fdw or how useful it would be > to have. We really need some kind of method for delegating authentication. I don't know how it should work. Generally, password authentication is a silly choice for automated logins because then you've got to store the password someplace from which it can be digitally stolen; stealing a password from someone's brain is a different kind of problem. It's not even a good method for this situation, yet it's the only one we allow. I think that bites, but I don't really know what to do about it. > Yeah, I have not been finding it enjoyable. How much flexibility does the > SQL/MED spec even give us (I don't have access to the spec)? Sorry, I don't know. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Thu, Sep 14, 2017 at 1:08 PM, Robert Haaswrote: > On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janes wrote: > > I think that foreign tables ought to behave as views do, where they run > as > > the owner rather than the invoker. No one has talked me out of it, but > no > > one has supported me on it either. But I think it is too late to change > > that now. > > That's an interesting point. I think that you can imagine use cases > for either method. Obviously, if what you want to do is drill a hole > through the Internet to another server and then expose it to some of > your fellow users, having the FDW run with the owner's permissions > (and credentials) is exactly right. But there's another use case too, > which is where you have something that looks like a multi-user > sharding cluster. You want each person's own credentials to carry > over to everything they do remotely. > OK. And if you want the first one, you can wrap it in a view currently, but if it were changed I don't know what you would do if you want the 2nd one (other than having every user create their own set of foreign tables). So I guess the current situation is more flexible. It does seem like it would then be a good idea to have a user mapping option of "pass_the_hash" which would look up md5 hash from the pg_authid (if the local username is the same as the remote user name) and use that to connect to the foreign server, as an alternative option to recording the password in plain text in the mapping itself. But that would require some changes to libpq, not just postgres_fdw. And that wouldn't work for SCRAM. I guess that SCRAM does have some feature to allow this kind of delegation, but I don't know enough about it to know how hard it would be to implement in postgres_fdw or how useful it would be to have. > > I feel like the USER MAPPING stuff is a pretty clunky and annoying way > of trying to make this work, no matter which of those use cases you > happen to have. But I'm not exactly sure what would be better, > either, and like you say, it's a bit late to be breaking compatibility > at this point. > Yeah, I have not been finding it enjoyable. How much flexibility does the SQL/MED spec even give us (I don't have access to the spec)? From what I could tell, it requires USER MAPPING to exist but doesn't give any details, and doesn't say there can't be something else one could optionally use instead. Cheers, Jeff
Re: [HACKERS] postgres_fdw super user checks
On 09/14/2017 08:33 PM, Jeff Janes wrote:> Attached is a new patch which fixes the style issue you mentioned. Thanks, the patch looks good no,w and as far as I can tell there was no need to update the comments or the documentation so I am setting this as ready for committer. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janeswrote: > I think that foreign tables ought to behave as views do, where they run as > the owner rather than the invoker. No one has talked me out of it, but no > one has supported me on it either. But I think it is too late to change > that now. That's an interesting point. I think that you can imagine use cases for either method. Obviously, if what you want to do is drill a hole through the Internet to another server and then expose it to some of your fellow users, having the FDW run with the owner's permissions (and credentials) is exactly right. But there's another use case too, which is where you have something that looks like a multi-user sharding cluster. You want each person's own credentials to carry over to everything they do remotely. I feel like the USER MAPPING stuff is a pretty clunky and annoying way of trying to make this work, no matter which of those use cases you happen to have. But I'm not exactly sure what would be better, either, and like you say, it's a bit late to be breaking compatibility at this point. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Tue, Sep 12, 2017 at 1:13 AM, Andreas Karlssonwrote: > On 07/27/2017 09:45 PM, Jeff Janes wrote:> Here is an updated patch. This > version allows you use the password-less > >> connection if you either are the super-user directly (which is the >> existing committed behavior), or if you are using the super-user's mapping >> because you are querying a super-user-owned view which you have been >> granted access to. >> > > I have tested the patch and it passes the tests and works, and the code > looks good (I have a small nitpick below). > > The feature seems useful, especially for people who already use views for > security, so the question is if this is a potential footgun. I am leaning > towards no since the superuser should be careful when grant access to is > views anyway. > > It would have been nice if there was a more generic way to handle this > since 1) the security issue is not unique to postgres_fdw and 2) this > requires you to create a view. But since the patch is simple, an > improvement in itself and does not prevent any future further improvements > in this era I see no reason to let perfect be the enemy of good. > Thanks for the review. I think that foreign tables ought to behave as views do, where they run as the owner rather than the invoker. No one has talked me out of it, but no one has supported me on it either. But I think it is too late to change that now. Wrapping it in a view is not hard, but it sure clutters up a schema. I don't think this can be made too generic, because each database has a quite different security model, so the solution will be much different. Attached is a new patch which fixes the style issue you mentioned. Cheers, Jeff postgres_fdw_superuser_v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On 07/27/2017 09:45 PM, Jeff Janes wrote:> Here is an updated patch. This version allows you use the password-less connection if you either are the super-user directly (which is the existing committed behavior), or if you are using the super-user's mapping because you are querying a super-user-owned view which you have been granted access to. I have tested the patch and it passes the tests and works, and the code looks good (I have a small nitpick below). The feature seems useful, especially for people who already use views for security, so the question is if this is a potential footgun. I am leaning towards no since the superuser should be careful when grant access to is views anyway. It would have been nice if there was a more generic way to handle this since 1) the security issue is not unique to postgres_fdw and 2) this requires you to create a view. But since the patch is simple, an improvement in itself and does not prevent any future further improvements in this era I see no reason to let perfect be the enemy of good. = Nitpicking/style I would prefer if /* no check required if superuser */ if (superuser()) return; if (superuser_arg(user->userid)) return; was, for consistency with the if clause in connect_pg_server(), written as /* no check required if superuser */ if (superuser() || superuser_arg(user->userid)) return; Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Thu, Dec 1, 2016 at 7:11 PM, Haribabu Kommiwrote: > On Tue, Oct 18, 2016 at 10:38 AM, Michael Paquier < > michael.paqu...@gmail.com> wrote: > >> On Mon, Oct 17, 2016 at 10:51 PM, Robert Haas >> wrote: >> > On Mon, Oct 17, 2016 at 2:18 AM, Michael Paquier >> > wrote: >> >> On Mon, Oct 17, 2016 at 3:33 AM, Jeff Janes >> wrote: >> >>> postgres_fdw has some checks to enforce that non-superusers must >> connect to >> >>> the foreign server with a password-based method. The reason for this >> is to >> >>> prevent the authentication to the foreign server from happening on >> the basis >> >>> of the OS user who is running the non-foreign server. >> >>> >> >>> But I think these super user checks should be run against the userid >> of the >> >>> USER MAPPING being used for the connection, not the userid of >> currently >> >>> logged on user. >> >> >> >> So, if the user mapping user is a superuser locally, this would allow >> >> any lambda user of the local server to attempt a connection to the >> >> remote server. It looks dangerous rather dangerous to me to authorize >> >> that, even if the current behavior is a bit inconsistent I agree. >> > >> > I don't know what "any lambda user" means. Did you mean to write "any >> > random user"? >> >> Yes, in this context that would be "any non-superuser" or "any user >> without superuser rights". Actually that's a French-ism. I just >> translated it naturally to English to define a user that has no access >> to advanced features :) >> > > > Thanks for the patch, but it breaking the existing functionality as per > the other > mails. Marked as "returned with feedback" in 2016-11 commitfest. > Here is an updated patch. This version allows you use the password-less connection if you either are the super-user directly (which is the existing committed behavior), or if you are using the super-user's mapping because you are querying a super-user-owned view which you have been granted access to. It first I thought the currently committed behavior might be a security bug as a directly logged in superuser can use another user's user-defined mapping but without the password restriction when querying a view made by someone else. But consulting with the security list nearly a year ago, the conclusion was that it is never a good idea for a superuser to blindly query from other users' views, and that the current situation is no worse for postgres_fdw than it is for other features, and so nothing needs to be done about it. So that is why I've decided to allow the passwordless solution in either situation--a superuser using someone else mapping, or someone else using a super user's mapping. I didn't update any comments because the existing ones seem to apply equally well to the new code as the old code. The regression test passes with this version because I still allow the old behavior. I didn't add a new test to also test the new behavior, because I don't know how to do that with the existing make check framework, and a TAP test seems like overkill. Cheers, Jeff postgres_fdw_superuser_v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Tue, Oct 18, 2016 at 10:38 AM, Michael Paquierwrote: > On Mon, Oct 17, 2016 at 10:51 PM, Robert Haas > wrote: > > On Mon, Oct 17, 2016 at 2:18 AM, Michael Paquier > > wrote: > >> On Mon, Oct 17, 2016 at 3:33 AM, Jeff Janes > wrote: > >>> postgres_fdw has some checks to enforce that non-superusers must > connect to > >>> the foreign server with a password-based method. The reason for this > is to > >>> prevent the authentication to the foreign server from happening on the > basis > >>> of the OS user who is running the non-foreign server. > >>> > >>> But I think these super user checks should be run against the userid > of the > >>> USER MAPPING being used for the connection, not the userid of currently > >>> logged on user. > >> > >> So, if the user mapping user is a superuser locally, this would allow > >> any lambda user of the local server to attempt a connection to the > >> remote server. It looks dangerous rather dangerous to me to authorize > >> that, even if the current behavior is a bit inconsistent I agree. > > > > I don't know what "any lambda user" means. Did you mean to write "any > > random user"? > > Yes, in this context that would be "any non-superuser" or "any user > without superuser rights". Actually that's a French-ism. I just > translated it naturally to English to define a user that has no access > to advanced features :) > Thanks for the patch, but it breaking the existing functionality as per the other mails. Marked as "returned with feedback" in 2016-11 commitfest. Regards, Hari Babu Fujitsu Australia
Re: [HACKERS] postgres_fdw super user checks
On Mon, Oct 17, 2016 at 10:51 PM, Robert Haaswrote: > On Mon, Oct 17, 2016 at 2:18 AM, Michael Paquier > wrote: >> On Mon, Oct 17, 2016 at 3:33 AM, Jeff Janes wrote: >>> postgres_fdw has some checks to enforce that non-superusers must connect to >>> the foreign server with a password-based method. The reason for this is to >>> prevent the authentication to the foreign server from happening on the basis >>> of the OS user who is running the non-foreign server. >>> >>> But I think these super user checks should be run against the userid of the >>> USER MAPPING being used for the connection, not the userid of currently >>> logged on user. >> >> So, if the user mapping user is a superuser locally, this would allow >> any lambda user of the local server to attempt a connection to the >> remote server. It looks dangerous rather dangerous to me to authorize >> that, even if the current behavior is a bit inconsistent I agree. > > I don't know what "any lambda user" means. Did you mean to write "any > random user"? Yes, in this context that would be "any non-superuser" or "any user without superuser rights". Actually that's a French-ism. I just translated it naturally to English to define a user that has no access to advanced features :) -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Mon, Oct 17, 2016 at 2:18 AM, Michael Paquierwrote: > On Mon, Oct 17, 2016 at 3:33 AM, Jeff Janes wrote: >> postgres_fdw has some checks to enforce that non-superusers must connect to >> the foreign server with a password-based method. The reason for this is to >> prevent the authentication to the foreign server from happening on the basis >> of the OS user who is running the non-foreign server. >> >> But I think these super user checks should be run against the userid of the >> USER MAPPING being used for the connection, not the userid of currently >> logged on user. > > So, if the user mapping user is a superuser locally, this would allow > any lambda user of the local server to attempt a connection to the > remote server. It looks dangerous rather dangerous to me to authorize > that, even if the current behavior is a bit inconsistent I agree. I don't know what "any lambda user" means. Did you mean to write "any random user"? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Mon, Oct 17, 2016 at 11:48 AM, Michael Paquierwrote: > On Mon, Oct 17, 2016 at 3:33 AM, Jeff Janes wrote: >> postgres_fdw has some checks to enforce that non-superusers must connect to >> the foreign server with a password-based method. The reason for this is to >> prevent the authentication to the foreign server from happening on the basis >> of the OS user who is running the non-foreign server. >> >> But I think these super user checks should be run against the userid of the >> USER MAPPING being used for the connection, not the userid of currently >> logged on user. > > So, if the user mapping user is a superuser locally, this would allow > any lambda user of the local server to attempt a connection to the > remote server. It looks dangerous rather dangerous to me to authorize > that, even if the current behavior is a bit inconsistent I agree. A lambda user can use a user mapping same as a superuser if a. that user mapping is public and/or b. it uses a view owned by super user (RangeTblEntry::checkuser). When a is true but not b, the the user in UserMapping is set to lambda and not superuser, so this patch is correct here. If b is true, and lambda is able to access the view, the superuser has granted it permissions to do so and thus intends to let lambda use a super user user mapping. Since we trust super users to do the right thing, I don't see why it's unsafe. Any other objects accesses by lambda, will use a different user mapping based on the object being accessed. Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Mon, Oct 17, 2016 at 12:03 AM, Jeff Janeswrote: > postgres_fdw has some checks to enforce that non-superusers must connect to > the foreign server with a password-based method. The reason for this is to > prevent the authentication to the foreign server from happening on the basis > of the OS user who is running the non-foreign server. > > But I think these super user checks should be run against the userid of the > USER MAPPING being used for the connection, not the userid of currently > logged on user. > > That is, I think the last line in this script should succeed: ('jjanes' is > both a superuser, and a database): > > > CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public; > CREATE SERVER foo FOREIGN DATA WRAPPER postgres_fdw; > CREATE USER MAPPING FOR jjanes SERVER foo; > CREATE TABLE foobar1 ( x integer); > CREATE FOREIGN TABLE foobar2 ( x integer) SERVER foo OPTIONS ( table_name > 'foobar1'); > CREATE VIEW foobar3 AS SELECT foobar2.x FROM foobar2; > CREATE USER test; > GRANT SELECT ON TABLE foobar3 TO test; > \c jjanes test > select * from foobar3; > > It connects back to itself, simply for demonstration purposes. > > The attached patch implements this change in auth checking. > I agree with your analysis, that any passwordless foreign server access with super user's user mapping should be allowed. If it's safe to access a foreign server without password for a superuser, then it should be safe to do so when corresponding user mapping is used even when login user is non-superuser. But there's one problem with the patch. login as some useruser and run following commands. create extension postgres_fdw; create server foo foreign data wrapper postgres_fdw options (dbname 'postgres'); create user test; grant USAGE ON FOREIGN server foo to test; set role test; create user mapping for test server foo; create foreign table fpg_class (oid oid) server foo options (table_name 'pg_class', schema_name 'pg_catalog'); create view fview as select * from fpg_class; set role ; select * from fview limit 0; With your patch it gives error ERROR: password is required DETAIL: Non-superuser cannot connect if the server does not request a password. HINT: Target server's authentication method must be changed. Without the patch it does not give any error. Is that intentional? I guess, this is because of asymmetry in check_conn_params() and connect_pg_server(). The first one does not check any params if the logged in user is a superuser but the later checks if only the user in the mapping is superuser. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres_fdw super user checks
On Mon, Oct 17, 2016 at 3:33 AM, Jeff Janeswrote: > postgres_fdw has some checks to enforce that non-superusers must connect to > the foreign server with a password-based method. The reason for this is to > prevent the authentication to the foreign server from happening on the basis > of the OS user who is running the non-foreign server. > > But I think these super user checks should be run against the userid of the > USER MAPPING being used for the connection, not the userid of currently > logged on user. So, if the user mapping user is a superuser locally, this would allow any lambda user of the local server to attempt a connection to the remote server. It looks dangerous rather dangerous to me to authorize that, even if the current behavior is a bit inconsistent I agree. Your patch breaks the join pushdown logic when multiple user IDs are involved. Per se make check. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] postgres_fdw super user checks
postgres_fdw has some checks to enforce that non-superusers must connect to the foreign server with a password-based method. The reason for this is to prevent the authentication to the foreign server from happening on the basis of the OS user who is running the non-foreign server. But I think these super user checks should be run against the userid of the USER MAPPING being used for the connection, not the userid of currently logged on user. That is, I think the last line in this script should succeed: ('jjanes' is both a superuser, and a database): CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public; CREATE SERVER foo FOREIGN DATA WRAPPER postgres_fdw; CREATE USER MAPPING FOR jjanes SERVER foo; CREATE TABLE foobar1 ( x integer); CREATE FOREIGN TABLE foobar2 ( x integer) SERVER foo OPTIONS ( table_name 'foobar1'); CREATE VIEW foobar3 AS SELECT foobar2.x FROM foobar2; CREATE USER test; GRANT SELECT ON TABLE foobar3 TO test; \c jjanes test select * from foobar3; It connects back to itself, simply for demonstration purposes. The attached patch implements this change in auth checking. Cheers, Jeff postgres_fdw_superuser.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers