Re: Non-superuser subscription owners

Hi,

On 2023-01-23 12:39:50 -0500, Robert Haas wrote:
> On Sun, Jan 22, 2023 at 8:52 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > Perhaps we should have a way to directly turn on/off authentication
> > > methods in libpq through API functions and/or options?
> >
> > Yes. There's an in-progress patch adding, I think, pretty much what is
> > required here:
> > https://www.postgresql.org/message-id/9e5a8ccddb8355ea9fa4b75a1e3a9edc88a70cd3.camel@vmware.com
> >
> > require_auth=a,b,c
> >
> > I think an allowlist approach is the right thing for the subscription (and
> > postgres_fdw/dblink) use case, otherwise we'll add some auth method down the
> > line without updating what's disallowed in the subscription code.
>
> So what would we do here, exactly? We could force a require_auth
> parameter into the provided connection string, although I'm not quite
> sure of the details there

If we parse the connection string first, we can ensure that our values take
precedence, that shouldn't be an issue, I think.

> , but what value should we force? Is that going to be something hard-coded,
> or something configurable? If configurable, where does that configuration
> get stored?

I would probably start with something hardcoded, perhaps with an adjusted
value depending on things like pg_read_server_files.

I'd say just allowing password (whichever submethod), ssl is a good start,
with something like your existing code to prevent file access for ssl unless
pg_read_server_files is granted.

I don't think kerberos, gss, peer, sspi would be safe.

> Regardless, this only allows connection strings to be restricted along
> one axis: authentication type. If you want to let people connect only
> to a certain subnet or whatever, you're still out of luck.

True. But I think it'd get us a large percentage of the use cases.

Greetings,

Andres Freund