Re: [GENERAL] PG choosing nested loop for set membership?
On Tue, Mar 25, 2014 at 5:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah. The weird thing about that is that the nestloop rowcount estimate isn't the product of the two input rowcounts --- you'd sort of expect an estimate of 158 given the input-relation sizes. While that's not ipso facto evidence of a bug (because the estimates are arrived at in different ways), I'm having a hard time replicating it here. Are you using an up-to-date PG release? All right, I think I'm onto something. But first I'll answer your questions. Version is 9.3.3 from the Postgres Debian archives. One thing that might help is to increase the statistics target for pl2._visible_accounts_by_rule_set. The other two tables are small enough that you don't need to do that for them. (Although come to think of it, they are also small enough that maybe auto-analyze isn't triggering for them ... does a manual ANALYZE improve matters?) You were right that auto-analyze didn't go after them. Weird. But a few manual analyzes later, and no change. Here's what I did, though. I collapsed the pl2.current_user view into pl2.visible_accounts: === select acc.account, acc.manager, acc.is_fund from pl2._visible_accounts_by_rule_set acc inner join (pl2._users u left join pl2._users iu on u.impersonating = iu.user_id) on acc.rule_set_id = coalesce(iu.permission_rule_set_id, u.permission_rule_set_id) where u.user_principal_name = session_user === I noticed that join-on-coalesce pattern that gave us trouble in SQL Server. The query planner can't do a thing with that. So I rewrote the query so the last join would be solid: === select acc.account, acc.manager, acc.is_fund from pl2._users lu inner join pl2._users u on u.user_id = coalesce(lu.impersonating, lu.user_id) inner join pl2._visible_accounts_by_rule_set acc on acc.rule_set_id = u.permission_rule_set_id where lu.user_principal_name = session_user === The join order is the same, and the indexes used are the same, but the estimate is much better: 'Nested Loop (cost=0.68..13.70 rows=133 width=10) (actual time=0.073..0.211 rows=241 loops=1)' ' Output: acc.account, acc.manager, acc.is_fund' ' Buffers: shared hit=10' ' - Nested Loop (cost=0.54..8.58 rows=1 width=4) (actual time=0.056..0.059 rows=1 loops=1)' 'Output: u.permission_rule_set_id' 'Buffers: shared hit=7' '- Index Scan using _pl2_users_user_principal_name_idx on pl2._users lu (cost=0.27..4.29 rows=1 width=8) (actual time=0.045..0.047 rows=1 loops=1)' ' Output: lu.user_id, lu.user_principal_name, lu.name, lu.permission_rule_set_id, lu.impersonating, lu.is_admin' ' Index Cond: (lu.user_principal_name = (session_user())::text)' ' Buffers: shared hit=4' '- Index Scan using _users_pkey on pl2._users u (cost=0.27..4.29 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)' ' Output: u.user_id, u.user_principal_name, u.name, u.permission_rule_set_id, u.impersonating, u.is_admin' ' Index Cond: (u.user_id = COALESCE(lu.impersonating, lu.user_id))' ' Buffers: shared hit=3' ' - Index Scan using _visible_accounts_by_rule_set_idx on pl2._visible_accounts_by_rule_set acc (cost=0.15..3.54 rows=158 width=14) (actual time=0.015..0.089 rows=241 loops=1)' 'Output: acc.rule_set_id, acc.account, acc.manager, acc.is_fund' 'Index Cond: (acc.rule_set_id = u.permission_rule_set_id)' 'Buffers: shared hit=3' 'Total runtime: 0.297 ms' I'll see if I can write an isolated test case for the coalesce misestimate. Or do you think the query planner will ever be able to do anything with that form? --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG choosing nested loop for set membership?
On Wed, Mar 26, 2014 at 10:23 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hm. It's not obvious from here that those give the same results --- but you probably understand your schema better than the rest of us. The _users table has a user_id, and a nullable column impersonating which refers to a user_id you want to impersonate. If impersonating isn't null, you want the rule_set_id for that user. If not, you want the rule_set_id of your own user. Hence the first query's left join to the second, impersonated user. The final join grabs the first rule_set_id it can find with a coalesce. The second query does the same thing with an inner join; the second _users reference will have the impersonated user if there is one, or the original user if there isn't. Either way, there's a solid user to join to, which I guess is enough for the query planner. They're really equivalent, since there is still just one rule_set_id at the end. Probably not much. I'd guess that the real benefit of this approach is that it avoids the join-condition-using-three-input-relations, which is a bear from any angle. Well look what happens when I remove impersonation, and stick a coalesce in the wrong place: === select acc.account, acc.manager, acc.is_fund from pl2._users lu inner join pl2._visible_accounts_by_rule_set acc on acc.rule_set_id = coalesce(lu.permission_rule_set_id, 0) where lu.user_principal_name = session_user === 'Hash Join (cost=2.62..9.07 rows=9 width=10) (actual time=0.066..0.239 rows=241 loops=1)' ' Output: acc.account, acc.manager, acc.is_fund' ' Hash Cond: (acc.rule_set_id = COALESCE(lu.permission_rule_set_id, 0))' ' Buffers: shared hit=4' Just removing the coalesce (acc.rule_set_id = lu.permission_rule_set_id) does this: 'Hash Join (cost=2.62..10.31 rows=133 width=10) (actual time=0.063..0.257 rows=241 loops=1)' ' Output: acc.account, acc.manager, acc.is_fund' ' Hash Cond: (acc.rule_set_id = lu.permission_rule_set_id)' ' Buffers: shared hit=4' Which says to me coalesce has a selectivity. --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG choosing nested loop for set membership?
On Wed, Mar 26, 2014 at 11:43 AM, Tom Lane t...@sss.pgh.pa.us wrote: In principle I guess we could somehow merge the stats of y and z when looking at a coalesce(y, z) expression, but I'm not sure how that would work exactly. Yeah, I'm not sure there's anything to fix here, either. Just a reminder that coalesces in joins are bad. The only thing I could think was making an exception for the case when all inputs to coalesce() have one row. I think that's what's happening here; coalesce selectivity is estimated at less than one, even though you can't get a cardinality any less than the inputs (they're already one), so the nested loop sees an estimate that's less than the product of its inputs. Or that's my guess anyhow. Thanks for having a look! --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG choosing nested loop for set membership?
Hello, it's me, a Postgres n00b again. I'm dealing with a query that scans a rather large table (94,000,000 tuples or so) and just picks out certain rows and sums them: select dci.snapshot_time as time, round(sum(dci.todays_pl)::numeric,0) as pl from dbo._pl_data_cache_intraday dci where dci.snapshot_time between '2014-03-25 11:32:40.004552-05'::timestamptz and '2014-03-25 12:02:40.015177-05'::timestamptz and dci.symbol in (select sec.symbol from dbo.security_underliers sec where sec.ultimate_underlier = 'SPY') and dci.manager = 'BJC' and dci.account in (select account from pl2.visible_accounts where is_fund) group by dci.snapshot_time order by dci.snapshot_time; For the most part, Postgres is doing the right thing: snapshot_time is the lead column in all of the table's indexes, so it's able to pick up the source rows fairly quickly in its index scan. It's also enforcing dci.manager = 'BJC' in the same scan, and does a Hash Semi Join for dci.symbol in (...). The trouble comes when enforcing the dci.account in (...) search condition: pl2.visible_accounts is a view that determines which accounts the current user can see, which, depending on who you are, can be several hundred or none at all. Postgres estimates the output of this query as two rows, but in my case, it's actually 240. Unfortunately, that leads the query planner to try to think a nested loop is cheap enough to enforce this, when actually it's really expensive. If I hard-code the results from pl2.visible_accounts, Postgres will do a hash semi join for me, which is much faster, but then I have to wrap up this whole query as a function in order to preserve its security properties. Not only is that the situation I was trying to avoid, it means I can't use EXPLAIN for my query anymore. I've noticed I can also do the really sneaky dci.account in (select unnest(array_agg(account)) from pl2.visible_accounts), which tricks the estimator into thinking there will be 100 rows. That _really_ feels like cheating. Besides the above, is there anything I can do to get Postgres to do a hash instead of a nested loop? --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG choosing nested loop for set membership?
On Tue, Mar 25, 2014 at 4:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
So the main estimation error is inside that view, which you didn't
show us :-(
I didn't know which direction you'd want to go with it. :P
The view is like this:
===
create or replace view pl2.visible_accounts
as
select
-- {{pk}} The account in question. This is the primary key.
acc.account,
-- The manager for this account.
acc.manager,
-- True if this account is in the fund, false otherwise.
acc.is_fund
from pl2._visible_accounts_by_rule_set acc
inner join pl2.current_user u on acc.rule_set_id =
u.impersonated_user_permission_rule_set_id;
===
pl2._visible_accounts_by_rule_set has rule_set_id = 1 with 241
entries, rule_set_id = 3 with 76, and nothing else. Postgres correctly
assumes pl2_current_user will return one row. In my case, this will
return rule_set_id = 1.
Explaining just this view yields:
'Nested Loop (cost=2.77..10.23 rows=2 width=10) (actual
time=0.086..0.222 rows=241 loops=1)'
' Output: acc.account, acc.manager, acc.is_fund'
' Buffers: shared hit=7'
' - Hash Right Join (cost=2.62..5.12 rows=1 width=8) (actual
time=0.064..0.068 rows=1 loops=1)'
'Output: real_user.permission_rule_set_id,
impersonated_user.permission_rule_set_id'
'Hash Cond: (impersonated_user.user_id = real_user.impersonating)'
'Buffers: shared hit=4'
'- Seq Scan on pl2._users impersonated_user
(cost=0.00..2.35 rows=35 width=8) (actual time=0.002..0.007 rows=35
loops=1)'
' Output: impersonated_user.user_id,
impersonated_user.user_principal_name, impersonated_user.name,
impersonated_user.permission_rule_set_id,
impersonated_user.impersonating, impersonated_user.is_admin'
' Buffers: shared hit=2'
'- Hash (cost=2.61..2.61 rows=1 width=8) (actual
time=0.041..0.041 rows=1 loops=1)'
' Output: real_user.impersonating,
real_user.permission_rule_set_id'
' Buckets: 1024 Batches: 1 Memory Usage: 1kB'
' Buffers: shared hit=2'
' - Seq Scan on pl2._users real_user (cost=0.00..2.61
rows=1 width=8) (actual time=0.026..0.036 rows=1 loops=1)'
'Output: real_user.impersonating,
real_user.permission_rule_set_id'
'Filter: (real_user.user_principal_name =
(session_user())::text)'
'Rows Removed by Filter: 34'
'Buffers: shared hit=2'
' - Index Scan using _visible_accounts_by_rule_set_idx on
pl2._visible_accounts_by_rule_set acc (cost=0.15..3.54 rows=158
width=14) (actual time=0.018..0.086 rows=241 loops=1)'
'Output: acc.rule_set_id, acc.account, acc.manager, acc.is_fund'
'Index Cond: (acc.rule_set_id =
COALESCE(impersonated_user.permission_rule_set_id,
real_user.permission_rule_set_id))'
'Buffers: shared hit=3'
'Total runtime: 0.313 ms'
All of the estimates on this view are reasonable, except for that
nested loop at the top. The only thing I can think is that it's
uncertain which ID I will pick, and I can't help it there.
--Brian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG choosing nested loop for set membership?
On Tue, Mar 25, 2014 at 4:12 PM, David Johnston pol...@yahoo.com wrote: 2 - and the one I'd use by default) Use an INNER JOIN That's where I started, but Postgres is smart enough to know that this is equivalent to what I'm doing, and still picks the nested loop. I went to IN in the hopes of tricking it. I haven't tried EXISTS. I'll have to try that tomorrow. --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovering from failed transaction
I feel dumb asking this question, but I can't seem to find the answer online. I'm running serializable transactions, and so naturally, they will sometimes fail with the error could not serialize access due to concurrent update. But then I try to issue a ROLLBACK so I can continue using the connection, and I get error 25P02: current transaction is aborted, commands ignored until end of transaction block. ...doesn't ROLLBACK end a transaction block? What does Postgres want here? How can I retry without closing the connection altogether? --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering from failed transaction
On Mon, Mar 10, 2014 at 4:16 PM, Jeff Janes jeff.ja...@gmail.com wrote: ... Or maybe it is mangling the ROLLBACK; into some form the database doesn't recognize. Look in the postgres log files to see what the events look like from PostgreSQL's perspective. Well that's the clue I needed. I was misinterpreting Postgres's log file; it was complaining about the SET statement_timeout statement Npgsql was slipping ahead of my ROLLBACK. Apparently I need to do transactions with Npgsql's transaction class. --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GSSAPI/SSPI and mismatched user names
I'm going to file this as a bug as well, but I guess I'm hoping to catch some developers here for discussion. I'm working with the Npgsql group on getting integrated security to just work in the same way SQL Server's does. I wrote a workaround for one issue, only to find out that I need more workarounds, and I finally realized that this a problem with the way Postgres handles GSSAPI/SSPI logins. You can read my full description here: https://github.com/npgsql/Npgsql/issues/162#issuecomment-35916650 The short version is that Postgres requires two user names when using GSSAPI/SSPI: one from the startup packet, and one from the Kerberos ticket, and if these don't match exactly, the login fails. It's generally impossible to determine the correct user name to send in the startup packet. I think Postgres should either not require or ignore the user name in the startup packet for these two login types. What do you think? --Brian
Re: [GENERAL] GSSAPI/SSPI and mismatched user names
On Mon, Feb 24, 2014 at 12:55 PM, Stephen Frost sfr...@snowman.net wrote: * Brian Crowell (br...@fluggo.com) wrote: https://github.com/npgsql/Npgsql/issues/162#issuecomment-35916650 Reading through this- can't you use GSSAPI to get the Kerberos princ found the ticket which is constructed? I'm pretty sure the MIT libraries support that, at least... I expected I might be able to do that on Linux, but right now I'm trying to work out the Windows non-domain case. Just as with .k5login, they do *not* have to match, but if they don't then there needs to be a mapping provided from the Kerberos princ to the PG username. Check out pg_ident and note that it even supports regexp's, so you may be able to construct a mapping such that the princ is mixed case and the login works- provided you send the lowercase'd username as the PG user to log in as. Unfortunately, in this case I don't even have a wrong-cased username to start with. I have the user name of the logged-in non-domain user, which is not the user name of the domain credentials I'm sending across the network. I think Postgres should either not require or ignore the user name in the startup packet for these two login types. What do you think? We need the username to figure out which auth method we're using... Oh dear. --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI/SSPI and mismatched user names
On Mon, Feb 24, 2014 at 1:06 PM, Stephen Frost sfr...@snowman.net wrote: I'm afraid you're going to need to try harder to find out how to get the Windows GSSAPI/SSPI code to give you the princ. I was actually pretty sure that GSSAPI defined a way, but I don't know the Windows side of it or if they decided to not bother implementing parts of GSSAPI. I don't think there's a Windows GSSAPI implementation at all. You're expected to go through Win32 and all of their security providers. Read here for some of the loveliness: http://stackoverflow.com/questions/7613468/getting-the-current-username-when-impersonated https://groups.google.com/forum/#!topic/microsoft.public.platformsdk.security/5L7ugO0Fc90 (Really, though, the Windows login infrastructure and API is rather nice.) Exactly- this is not something we can solve with a little bit of tweaking... Nuts. It sounded easy :P --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
Thought I might add, now that I've had more time to work with it, that adding the PostgreSQL host to the domain isn't necessary, and at least with Samba 3 is a huge waste of time thanks to the machine password changing policies in Active Directory. Much better to create an ordinary user account with a password that can't change, and then use ktpass to generate a password and create an appropriate keytab. You may or may not be able to use ktpass to set up an SPN, I didn't go about that in an orthodox way. --Brian On Tue, Nov 12, 2013 at 9:13 AM, Brian Crowell br...@fluggo.com wrote: On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr. franci...@npgsql.org wrote: It would be awesome if you could write a little guide about how to configure PostgreSQL to work with sspi authentication from Windows. I could add it to our Npgsql user manual... A guide will have to wait until I do all this again in a few months, but I'll jot down my mental notes. Here's what I did, using Debian 6, Samba 3.5 and PostgreSQL 9.3 against a Win2k3 domain: 1. Set up Kerberos for the domain. Plenty of guides for this. 2. Joined the Linux machine to the domain. Also lots of guides for this, but most of them are outdated and add a ton of settings that you don't need. Don't follow a guide until you read the relevant chapters of the Samba HOWTO. Familiarity with Active Directory helps. I'm using winbindd because I think it maintains the server's TGTs, but I'm not really sure. 3. Created a service principal for PostgreSQL as root using the Samba net ads keytab add command (helpful hint: leave items off the end of the net command to see other available options). I created two service principals, one for the short name and one for the FQDN: net ads keytab add postgres/machinen...@realm.com -U DOMAIN\Administrator net ads keytab add postgres/machinename.domain@realm.com -U DOMAIN\Administrator I verified the service principals with the command net ads keytab list. They should also end up in the system keytab, which you can verify with a command like klist -k /etc/krb5.keytab. (If they don't, then I probably did net ads kerberos kinit at this point.) My Samba produced six entries; three encryption types for each of the two principal names. 4. Exported the service principals to a Postgres-specific keytab. I did this with ktutil. You'll start ktutil and issue a read_kt /etc/krb5.keytab to import the system keytab, then issue delete_entry to delete all the but the Postgres principals. Use write_kt to write them to a Postgres-specific location; I used write_kt /etc/postgresql/9.3/main/krb5.keytab where the postgres account will own the file. I also removed write permissions from this file. 5. In postgres.conf, I set krb_server_keyfile = '/etc/postgresql/9.3/main/krb5.keytab' and krb_srvname = 'postgres'. krb_srvname's case didn't seem to matter for me, YMMV. (I just noticed this environment file sitting here. I wonder if I could use that with MIT Kerberos 1.9 and the KRB5_TRACE variable to get detailed Kerberos traces. Hmmm) 6. In pg_hba.conf, I added the line host all all all gss include_realm=1. I'm including the realm because I'm in a forest. 7. Restart PostgreSQL. 8. Log into Postgres locally and create a role for your domain self: create role bcrow...@realm.com login inherit; 9. If all went well, and I didn't leave out any steps, on a Windows machine you should be able to go to the command prompt and do: psql --host=machinename.domain.com --username=BCrowell@REALM.COMpostgres ...and get in without any password prompts, assuming you got the case on your username correct. If the case is wrong, Postgres will tell you what it's supposed to be in the system logs. 10. Patch Npgsql to understand GSSAPI authentication :P --Brian
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr. franci...@npgsql.org wrote: It would be awesome if you could write a little guide about how to configure PostgreSQL to work with sspi authentication from Windows. I could add it to our Npgsql user manual... A guide will have to wait until I do all this again in a few months, but I'll jot down my mental notes. Here's what I did, using Debian 6, Samba 3.5 and PostgreSQL 9.3 against a Win2k3 domain: 1. Set up Kerberos for the domain. Plenty of guides for this. 2. Joined the Linux machine to the domain. Also lots of guides for this, but most of them are outdated and add a ton of settings that you don't need. Don't follow a guide until you read the relevant chapters of the Samba HOWTO. Familiarity with Active Directory helps. I'm using winbindd because I think it maintains the server's TGTs, but I'm not really sure. 3. Created a service principal for PostgreSQL as root using the Samba net ads keytab add command (helpful hint: leave items off the end of the net command to see other available options). I created two service principals, one for the short name and one for the FQDN: net ads keytab add postgres/machinen...@realm.com -U DOMAIN\Administrator net ads keytab add postgres/machinename.domain@realm.com -U DOMAIN\Administrator I verified the service principals with the command net ads keytab list. They should also end up in the system keytab, which you can verify with a command like klist -k /etc/krb5.keytab. (If they don't, then I probably did net ads kerberos kinit at this point.) My Samba produced six entries; three encryption types for each of the two principal names. 4. Exported the service principals to a Postgres-specific keytab. I did this with ktutil. You'll start ktutil and issue a read_kt /etc/krb5.keytab to import the system keytab, then issue delete_entry to delete all the but the Postgres principals. Use write_kt to write them to a Postgres-specific location; I used write_kt /etc/postgresql/9.3/main/krb5.keytab where the postgres account will own the file. I also removed write permissions from this file. 5. In postgres.conf, I set krb_server_keyfile = '/etc/postgresql/9.3/main/krb5.keytab' and krb_srvname = 'postgres'. krb_srvname's case didn't seem to matter for me, YMMV. (I just noticed this environment file sitting here. I wonder if I could use that with MIT Kerberos 1.9 and the KRB5_TRACE variable to get detailed Kerberos traces. Hmmm) 6. In pg_hba.conf, I added the line host all all all gss include_realm=1. I'm including the realm because I'm in a forest. 7. Restart PostgreSQL. 8. Log into Postgres locally and create a role for your domain self: create role bcrow...@realm.com login inherit; 9. If all went well, and I didn't leave out any steps, on a Windows machine you should be able to go to the command prompt and do: psql --host=machinename.domain.com --username=bcrow...@realm.com postgres ...and get in without any password prompts, assuming you got the case on your username correct. If the case is wrong, Postgres will tell you what it's supposed to be in the system logs. 10. Patch Npgsql to understand GSSAPI authentication :P —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Tue, Nov 12, 2013 at 9:13 AM, Brian Crowell br...@fluggo.com wrote: net ads keytab add postgres/machinen...@realm.com -U DOMAIN\Administrator net ads keytab add postgres/machinename.domain@realm.com -U DOMAIN\Administrator D'oh! These should be: net ads keytab add postgres/machinen...@realm.com -U administra...@realm.com net ads keytab add postgres/machinename.domain@realm.com -U administra...@realm.com Stupid SAM names. —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Mon, Nov 11, 2013 at 11:56 PM, Christian Ullrich ch...@chrullrich.net wrote: On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell br...@fluggo.com wrote: * If I don't specify my username, Npgsql sends it in lowercase bcrowell Hmm. That is related one problem I've been having with SSPI auth from libpq/ODBC. The database treats the claimed user name case-sensitively when looking up the user info in pg_authid, and if the user logged on to Windows with a name differing in case from what the database thinks it is, authentication fails. Npgsql sending it always in lower case is precisely what I landed on as a workaround (basically overriding libpq's automatic user name detection in the ODBC connection string by appending a UID option). The message I get in the log is provided user name (bcrow...@realm.com) and authenticated username (bcrow...@realm.com) do not match, so it looks like I have to teach Npgsql to match whatever Windows is sending in GSSAPI. That, or teach Postgres how to lowercase the name on arrival. What did you do to get around this? —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Tue, Nov 12, 2013 at 10:03 AM, Christian Ullrich ch...@chrullrich.net wrote: Pseudocode: n = GetUserNameEx(NameSamCompatible)// logon screen case NameTranslate.Set(ADS_NAME_TYPE_NT4, n) n = NameTranslate.Get(ADS_NAME_TYPE_DOMAIN_SIMPLE) // official case n = n.CutAtTheAtSign() db.Connect(DSN=foo;UID= + n) To get a usable realm name, ADS_NAME_TYPE_USER_PRINCIPAL_NAME is probably more correct. This works if the role name in pg_authid matches the user name in the directory, case-wise. It cannot be shortened to GetUserNameEx(NameUserPrincipal) because that also returns logon screen case. I don't see any reason this can't be put in Npgsql. If the username isn't supplied, the NpgsqlConnectionStringBuilder guesses it in the UserName property. I'm going to make it guess correctly. —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Tue, Nov 12, 2013 at 5:45 AM, Francisco Figueiredo Jr. franci...@npgsql.org wrote: I'm looking forward your patch. Npgsql source can be found at github.com/npgsql/Npgsql I figured out the username issue, and so I've sent a pull request: https://github.com/npgsql/Npgsql/pull/95 I encountered an issue where connection pooling doesn't distinguish between Integrated Security users. I tried to fix that. Hopefully it's ship-shape. —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Tue, Nov 5, 2013 at 11:35 AM, Christian Ullrich ch...@chrullrich.net wrote: Hence my suspicion that it doesn't. I did not have the time to compare every function call yet. It doesn't. But it's a pretty close match; it looks like it was ported directly from the libpq code. libpq actually uses the same code path for SSPI and GSSAPI on Windows, with a boolean to switch a few things between the two. I've been trying to duplicate those changes in Npgsql. I think I'm getting closer though. I have psql on Windows successfully authenticating, so I can't be too far off. The GSSAPI error messages are of the usual helpful kind, even including the colon that is followed by no detail. I've been trying to enable the KRB5_TRACE environment variable in the Postgres child processes, but I can't seem to make it stick. That would (should!) provide some sort of help. —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell br...@fluggo.com wrote: I think I'm getting closer though. I have psql on Windows successfully authenticating, so I can't be too far off. Got it. The NpgsqlPasswordPacket class has a bug: a utility function it calls appends a null character to the data, which completely screws up GSSAPI. Now that I fixed that, I've got successful integrated authentication from Windows to PostgreSQL on Linux. However: * If I don't specify my username, Npgsql sends it in lowercase bcrowell * Npgsql isn't sending the realm, and I've got PostgreSQL configured to expect it Otherwise, it's working. As far as I know, the changes necessary are: * Use hostname in the SPN instead of IP address * Use kerberos package in AcquireCredentialsHandle call instead of negotiate * Fix PGUtil.WriteBytes to not send the extra null (this method is only used by NpgsqlPasswordPacket, but this fix will most likely break other authentication methods) * As stated above, may need to specify username manually (UserName = bcrow...@domain.com); I want to fix this If I figure out the username issue, I'll submit a patch. Also, in my case, it doesn't seem to matter for the SPN whether the service name is postgres or POSTGRES. I've got PostgreSQL set to postgres, and Npgsql is specifying POSTGRES, but I also at some point configured two sets of SPNs on the domain for uppercase and lowercase, so I don't know if that's a mitigating factor. —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
Hello again! I've been setting up my PostgreSQL server by doing something I've never done before: I've joined a Linux server to a domain so I can use integrated Kerberos authentication from server to server. I've managed to make this work from Linux machine to Linux machine. On the client, I retrieve my Kerberos ticket with kinit, and then I can use psql with my username to connect to the server without a password over GSSAPI. So far, so good. However, the eventual goal was to connect to this same server from a .NET app running on Windows, and here I've run into a snag. The Npgsql library does not support GSSAPI—it only supports SSPI, which is nearly-but-not-enough-like the same thing to count in this situation, because I can't seem to configure my PostgreSQL on Linux to accept SSPI. If I try it, I get the error: invalid authentication method sspi: not supported by this build The docs say that SSPI is supported if GSSAPI is available (http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH). Is this just a matter of me running the wrong build? I'm using PostgreSQL 9.3 from the official builds for Debian 6. If the docs are wrong, and SSPI isn't available server-side on Linux, what are my other options? —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows
I've thought of one option, which I'm investigating: implementing GSSAPI support in Npgsql. Microsoft claims this is possible using the SSPI API: http://msdn.microsoft.com/en-us/library/windows/desktop/aa380496(v=vs.85).aspx —Brian On Wed, Oct 30, 2013 at 3:16 PM, Brian Crowell br...@fluggo.com wrote: Hello again! I've been setting up my PostgreSQL server by doing something I've never done before: I've joined a Linux server to a domain so I can use integrated Kerberos authentication from server to server. I've managed to make this work from Linux machine to Linux machine. On the client, I retrieve my Kerberos ticket with kinit, and then I can use psql with my username to connect to the server without a password over GSSAPI. So far, so good. However, the eventual goal was to connect to this same server from a .NET app running on Windows, and here I've run into a snag. The Npgsql library does not support GSSAPI—it only supports SSPI, which is nearly-but-not-enough-like the same thing to count in this situation, because I can't seem to configure my PostgreSQL on Linux to accept SSPI. If I try it, I get the error: invalid authentication method sspi: not supported by this build The docs say that SSPI is supported if GSSAPI is available (http://www.postgresql.org/docs/9.3/static/auth-methods.html#SSPI-AUTH). Is this just a matter of me running the wrong build? I'm using PostgreSQL 9.3 from the official builds for Debian 6. If the docs are wrong, and SSPI isn't available server-side on Linux, what are my other options? —Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preserving the source code of views
On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, and it's very unlikely that there ever will be, because it's completely against the system structure at a number of levels. However, there's more than one way to skin this cat. Many people keep their DDL as text in some external CMS, and just load it into the database again after any change. If you write the view as CREATE OR REPLACE VIEW ... in your DDL, this is easy, at least for cases where you're not changing the set of columns provided by the view. All right, so I'm getting the picture. Postgres developers are keeping their code in larger scripts and committing them to databases as batches, rather than editing them in place. We had a pretty slick setup going with developers writing code against a live dev database, with system documentation pulled from the SQL object comments, and the ability to script the whole database to/from git when needed for source control purposes. In some raw form, the workflow here is more like: write a module in a script file that re-commits all its objects to the database, and then execute that whole batch when testing or deploying. Commit that script to git. If I want to keep my documentation scheme, I'll need to pull the comments out of that file. A harder sell to my developers, but not impossible. I noticed some objects (like views) are very picky about dependents. Do you drop all the module's objects at the beginning of the script, just in case there's a change in the number or types of columns? That seems tricky, especially considering there will be modules that depend on yours. You also mentioned an external CMS. Any suggestions? --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preserving the source code of views
On Sun, Oct 20, 2013 at 7:01 AM, Bill Moran wmo...@potentialtech.com wrote: You could adjust your workflow to use something like dbsteward: http://dbsteward.org/ Nifty, but without an editor, I don't think I could convince our developers to author the databases in XML. --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preserving the source code of views
On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule pavel.steh...@gmail.com wrote: For my work is very significant @a point - I wrote and I am writing usually database centric stored procedures centric applications and @a works perfect. For me a SQL code is code as any other - I use a my favourite editor, I use a GIT for versioning, I can simple distributed application to my customers. What do you do to manage dependencies among the objects? How do you have your scripts set up to handle deploying, say, an extra column to an existing view? Our databases tended to be SP-heavy before I came along. I'm big on views because that allows my client code to do very specific queries without having to write new SPs all the time. --Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Preserving the source code of views
Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets. I've run across one thing that would make a transfer difficult. Postgres doesn't preserve the source code for views, as far as I can tell. It parses them and then prints them its own way. We have a lot of complicated views, where both the formatting and the comments are significant. In fact, we produce some of our system documentation directly from the comments. Is there currently a way to preserve the original source code of a view as entered in the CREATE VIEW statement? --Brian
