Withdraw PL/Proxy from commitfest

In the previous discussion there was mentioned that Postgres should
move to the SQL-MED direction in remote connection handling.

SQL-MED specifies that connections should have names and referenced
everywhere using names. PL/Proxy currently does not conform to that
standard - it uses connection strings directly. Although it could
made work with SQL-MED backend, it would look ugly.

So I'd like to withdraw PL/Proxy from commitfest and rework it's
connection handling scheme to be also name->connstr based. Idea will
be that it will have user-definable connection handling backend,
which operates on named connections. And in the future we can
plug in a backend that reuses connection info from builtin SQL-MED store.

Although the current connection handling works and is secure it has
a deficiency that it's bit hard to hide the password that is used
for connecting. User can either play with table/function permissions
and SECURITY DEFINER functions but that's complex. Or he can put
passwords into .pgpass - this is easy and secure but has the problem
that the file is not manageable from inside database.

So PL/Proxy needs new SQL-MED based scheme that fixes it. When this
is ready we can re-discuss the builtin vs. PL-based remote functions.
As I don't plan to work on it near-term there is no point polluting
the commitfest page with it.

[ There was a attempt to paint the .pgpass based password handling
insecure because dblink makes the file world-readable. I still
fail to see how this any way points to flaws of the scheme... ]

--
marko

So, you'll implement the part of SQL-MED that deals with specifying
remote connections, e.g something like "CREATE CONNECTION" (no, I
haven't looked at what the syntax actually is)?

Yeah, that sounds like a good idea. We should get that into core, and
modify contrib/dblink to use it as well. It's just a small part of
SQL-MED, but it's a start, and it's useful for these other projects.

Marko Kreen wrote:
> In the previous discussion there was mentioned that Postgres should
> move to the SQL-MED direction in remote connection handling.
>
> SQL-MED specifies that connections should have names and referenced
> everywhere using names. PL/Proxy currently does not conform to that
> standard - it uses connection strings directly. Although it could
> made work with SQL-MED backend, it would look ugly.
>
> So I'd like to withdraw PL/Proxy from commitfest and rework it's
> connection handling scheme to be also name->connstr based. Idea will
> be that it will have user-definable connection handling backend,
> which operates on named connections. And in the future we can
> plug in a backend that reuses connection info from builtin SQL-MED store.
>
> Although the current connection handling works and is secure it has
> a deficiency that it's bit hard to hide the password that is used
> for connecting. User can either play with table/function permissions
> and SECURITY DEFINER functions but that's complex. Or he can put
> passwords into .pgpass - this is easy and secure but has the problem
> that the file is not manageable from inside database.
>
> So PL/Proxy needs new SQL-MED based scheme that fixes it. When this
> is ready we can re-discuss the builtin vs. PL-based remote functions.
> As I don't plan to work on it near-term there is no point polluting
> the commitfest page with it.
>
> [ There was a attempt to paint the .pgpass based password handling
> insecure because dblink makes the file world-readable. I still
> fail to see how this any way points to flaws of the scheme... ]
>

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, Sep 5, 2008 at 7:37 PM, Heikki Linnakangas <
heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:

> So, you'll implement the part of SQL-MED that deals with specifying remote
> connections, e.g something like "CREATE CONNECTION" (no, I haven't looked at
> what the syntax actually is)?
>
> Yeah, that sounds like a good idea. We should get that into core, and
> modify contrib/dblink to use it as well. It's just a small part of SQL-MED,
> but it's a start, and it's useful for these other projects.
>

Yes that's the plan.

>
>
> Marko Kreen wrote:
>
>> In the previous discussion there was mentioned that Postgres should
>> move to the SQL-MED direction in remote connection handling.
>>
>> SQL-MED specifies that connections should have names and referenced
>> everywhere using names. PL/Proxy currently does not conform to that
>> standard - it uses connection strings directly. Although it could
>> made work with SQL-MED backend, it would look ugly.
>>
>> So I'd like to withdraw PL/Proxy from commitfest and rework it's
>> connection handling scheme to be also name->connstr based. Idea will
>> be that it will have user-definable connection handling backend,
>> which operates on named connections. And in the future we can
>> plug in a backend that reuses connection info from builtin SQL-MED store.
>>
>> Although the current connection handling works and is secure it has
>> a deficiency that it's bit hard to hide the password that is used
>> for connecting. User can either play with table/function permissions
>> and SECURITY DEFINER functions but that's complex. Or he can put
>> passwords into .pgpass - this is easy and secure but has the problem
>> that the file is not manageable from inside database.
>>
>> So PL/Proxy needs new SQL-MED based scheme that fixes it. When this
>> is ready we can re-discuss the builtin vs. PL-based remote functions.
>> As I don't plan to work on it near-term there is no point polluting
>> the commitfest page with it.
>>
>> [ There was a attempt to paint the .pgpass based password handling
>> insecure because dblink makes the file world-readable. I still
>> fail to see how this any way points to flaws of the scheme... ]
>>
>>
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Fri, 2008-09-05 at 19:37 +0300, Heikki Linnakangas wrote:
> So, you'll implement the part of SQL-MED that deals with specifying
> remote connections, e.g something like "CREATE CONNECTION" (no, I
> haven't looked at what the syntax actually is)?

Why have you started top-posting in your replies (on multiple threads)?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2008-09-05 at 15:39 +0300, Marko Kreen wrote:

> There was a attempt to paint the .pgpass based password handling
> insecure because dblink makes the file world-readable. I still
> fail to see how this any way points to flaws of the scheme...

Hot Standby will probably need to connect from Standby to Primary.
Is .pgpass acceptable for that? Why?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2008-09-05 at 15:39 +0300, Marko Kreen wrote:
> In the previous discussion there was mentioned that Postgres should
> move to the SQL-MED direction in remote connection handling.
>
> SQL-MED specifies that connections should have names and referenced
> everywhere using names.

Where did you find that in SQL-MED

In my brief reading of SQL-MED spec I could only find info on defining
FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one
define connection parameters like username and password.

FSConnection handle uses these two, but again, I saw no place to put
credentials for making the connection in;

----------------
Hannu

Hannu Krosing wrote:

> In my brief reading of SQL-MED spec I could only find info on defining
> FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one
> define connection parameters like username and password.

It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take
generic options (list of key/value pairs). These can be used for
defining the actual connection to the remote server.

From http://www.wiscorp.com/sql_2003_standard.zip "4.4 User mappings":

"A user mapping is an SQL-environment element, pairing an ...
... The mapping is specified by generic options defined by the
foreign-data wrapper."

and "13.3 <user mapping definition>":

<user mapping definition> ::=
CREATE USER MAPPING FOR <specific or generic authorization identifier>
SERVER <foreign server name> [ <generic options> ]

regards,
Martin

On Tue, 2008-10-21 at 21:05 +0300, Martin Pihlak wrote:
> Hannu Krosing wrote:
>
> > In my brief reading of SQL-MED spec I could only find info on defining
> > FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one
> > define connection parameters like username and password.
>
> It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take
> generic options (list of key/value pairs). These can be used for
> defining the actual connection to the remote server.

Are you sure this is how it is intended to be done ?

> >From http://www.wiscorp.com/sql_2003_standard.zip "4.4 User mappings":
>
> "A user mapping is an SQL-environment element, pairing an ...
> ... The mapping is specified by generic options defined by the
> foreign-data wrapper."
>
> and "13.3 <user mapping definition>":
>
> <user mapping definition> ::=
> CREATE USER MAPPING FOR <specific or generic authorization identifier>
> SERVER <foreign server name> [ <generic options> ]

In pl/proxy context this would mean that in order to define connection
info we would at least need (foreign) SERVER and USER MAPPING objects

defined so

CREATE SERVER <foreign server name>
[ TYPE <server type> ]
[ VERSION <server version> ]
FOREIGN DATA WRAPPER <foreign-data wrapper name>
OPTIONS (HOST "host.ip", PORT "port_nr", DBNAME "databasename")
;

probably with a default / dummy "FOREIGN DATA WRAPPER" called "DEFAULT"

and

CREATE USER MAPPING
FOR <specific or generic authorization identifier>
SERVER <foreign server name>
OPTIONS (USER "username", PASSWORD "pwd")
;

plus a possibility to GRANT USAGE on those and also the function
ConnectServer(<foreign server name>) to actually make the connection.

I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually
need it.

there has to be some mechanism for prioritizing USER MAPPINGs in case
you can use many. Maybe have an extra argument for
ConnectServer(<foreign server name>, <specific or generic authorization
identifier>) .

----------------
Hannu

Hannu Krosing wrote:
>> It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take
>> generic options (list of key/value pairs). These can be used for
>> defining the actual connection to the remote server.
>
> Are you sure this is how it is intended to be done ?
>
Yes.

> In pl/proxy context this would mean that in order to define connection
> info we would at least need (foreign) SERVER and USER MAPPING objects
>
> defined so
>
> CREATE SERVER <foreign server name>
[snip]
> CREATE USER MAPPING
[snip]
> plus a possibility to GRANT USAGE on those and also the function
> ConnectServer(<foreign server name>) to actually make the connection.
>

Yes, and with the generic options actually any connection can be described
(dbi, odbc, ...). In some situations the client module would probably need
to know the type of the connection - this is where we could use either
TYPE or FDW from the server definition.

> I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually
> need it.
>

Actually a minimal FDW would be useful for validation the generic options
of SERVER and USER MAPPING. For instance this could be used to check that
server options are valid libpq conninfo parameters and that no password
is present in server options. The downside is that a wrapper would need to
be provided for all driver types (pgsql, dbi, ...). But I guess we could
start with just two - pgsql and "default". The latter would not validate
anything, thus enabling arbitrary options to be specified.

The FDW could also provide a function for obtaining the connection details
for the server and user mapping pair. For libpq connections it could merge
the server and user options into single connect string. Others probably
need username and password separately. This would take some of the complexity
off the client -- it needs not be concerned with what the actual option
keywords mean (there could be some that are not part of connect string).

For clients we could then provide a SQL accessible wrapper function that would
lookup server and user mapping. Then call the FDW to obtain connection details.
Something along the lines of:

# select * from pg_get_remote_connection_info('remotedb');

key value
----------- ----------------------------------------
conninfo dbname=remotedb host=remotehost user=...

Whereas dbi might also include a username and password:

key value
----------- ------------------------------------------------------------
conninfo dbi:mysql:database=sakila;host=localhost
username root
password salakala

What do you think, usable?

> there has to be some mechanism for prioritizing USER MAPPINGs in case
> you can use many. Maybe have an extra argument for
> ConnectServer(<foreign server name>, <specific or generic authorization
> identifier>) .
>

There is a restriction that we can have only one mapping per user/server.
And then there is PUBLIC if nothing else matches. In 13.3:

"The SQL-environment shall not include a user mapping descriptor whose
authorization identifier is U and whose foreign server name is equivalent
to FSN."

regards,
Martin

On Wed, Oct 22, 2008 at 11:02:34AM +0300, Martin Pihlak wrote:
> Hannu Krosing wrote:
> >> It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take
> >> generic options (list of key/value pairs). These can be used for
> >> defining the actual connection to the remote server.
> >
> > Are you sure this is how it is intended to be done ?
> >
> Yes.
>
> > In pl/proxy context this would mean that in order to define connection
> > info we would at least need (foreign) SERVER and USER MAPPING objects
> >
> > defined so
> >
> > CREATE SERVER <foreign server name>
> [snip]
> > CREATE USER MAPPING
> [snip]
> > plus a possibility to GRANT USAGE on those and also the function
> > ConnectServer(<foreign server name>) to actually make the connection.
> >
>
> Yes, and with the generic options actually any connection can be described
> (dbi, odbc, ...). In some situations the client module would probably need
> to know the type of the connection - this is where we could use either
> TYPE or FDW from the server definition.
>
> > I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually
> > need it.
> >
>
> Actually a minimal FDW would be useful for validation the generic options
> of SERVER and USER MAPPING. For instance this could be used to check that
> server options are valid libpq conninfo parameters and that no password
> is present in server options. The downside is that a wrapper would need to
> be provided for all driver types (pgsql, dbi, ...). But I guess we could
> start with just two - pgsql and "default". The latter would not validate
> anything, thus enabling arbitrary options to be specified.
>
> The FDW could also provide a function for obtaining the connection details
> for the server and user mapping pair. For libpq connections it could merge
> the server and user options into single connect string. Others probably
> need username and password separately. This would take some of the complexity
> off the client -- it needs not be concerned with what the actual option
> keywords mean (there could be some that are not part of connect string).
>
> For clients we could then provide a SQL accessible wrapper function that would
> lookup server and user mapping. Then call the FDW to obtain connection details.
> Something along the lines of:
>
> # select * from pg_get_remote_connection_info('remotedb');
>
> key value
> ----------- ----------------------------------------
> conninfo dbname=remotedb host=remotehost user=...
>
> Whereas dbi might also include a username and password:
>
> key value
> ----------- ------------------------------------------------------------
> conninfo dbi:mysql:database=sakila;host=localhost
> username root
> password salakala
>
> What do you think, usable?

Sure.

DBI's DBD::Excel <http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm>
is a more extreme example of this kind of thing. You can get connect
strings that look like this:

my $hDb = DBI->connect(
"DBI:Excel:file=dbdtest.xls",
undef,
undef,
{
xl_vtbl => {
TESTV => {
sheetName => 'TEST_V',
ttlRow => 5,
startCol => 1,
colCnt => 4,
datRow => 6,
datlmt => 4,
}
}
}
);

That last bit with the hash of hashes of hashes is probably where we'd
use CTEs to expand things out.

Speaking of CTEs, now that we'll have them, is there anything we could
be doing more easily in pg_catalog, etc.?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
> DBI's DBD::Excel <http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm>
> is a more extreme example of this kind of thing. You can get connect
> strings that look like this:
>
> my $hDb = DBI->connect(
> "DBI:Excel:file=dbdtest.xls",
> undef,
> undef,
> {
> xl_vtbl => {
> TESTV => {
> sheetName => 'TEST_V',
> ttlRow => 5,
> startCol => 1,
> colCnt => 4,
> datRow => 6,
> datlmt => 4,
> }
> }
> }
> );
>
> That last bit with the hash of hashes of hashes is probably where we'd
> use CTEs to expand things out.

Or we could just leave the attribute dict as it is and use something like:

CREATE SERVER dbdtest FOREIGN DATA WRAPPER dbi
OPTIONS (
datasource 'DBI:Excel:file=dbdtest.xls',
attributes '{xl_vtbl => {TESTV => { ... } }}'
);

CREATE USER MAPPING FOR bob SERVER dbdtest
OPTIONS (username NULL, password NULL);

The connection lookup for "dbtest" (user bob) would return:

key value
----------- ------------------------------------------------------------
datasource DBI:Excel:file=dbdtest.xls
attributes {xl_vtbl => {TESTV => { ... } }}
username NULL
password NULL

Regards,
Martin