Re: [GENERAL] Bug in postgres 9.6.2?
If I can provide a pg_dump backup with a db where I can reproduce the error and then also my postgresql.conf along with the query, would that be what you need for a test case? Thanks, Greig -- View this message in context: http://www.postgresql-archive.org/Bug-in-postgres-9-6-2-tp5972185p5972450.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Bug in postgres 9.6.2?
I do still have the 9.6.2 instance. Here is the explain analyze results from the 9.6.2 instance: Aggregate (cost=764612.56..764612.57 rows=1 width=8) (actual time=695.166..695.166 rows=1 loops=1) CTE test -> Nested Loop (cost=3345.90..757594.63 rows=311908 width=4) (actual time=76.936..632.852 rows=73500 loops=1) -> Hash Left Join (cost=3345.47..406121.74 rows=83289 width=16) (actual time=76.920..264.217 rows=73500 loops=1) Hash Cond: ((g.text_field)::text = (i.text_field)::text) -> Gather (cost=.89..404626.67 rows=333156 width=30) (actual time=76.877..219.843 rows=73500 loops=1) Workers Planned: 4 Workers Launched: 4 -> Nested Loop (cost=2333.89..370311.07 rows=83289 width=30) (actual time=74.266..408.763 rows=14700 loops=5) -> Hash Join (cost=2333.33..215520.29 rows=24240 width=16) (actual time=74.226..295.244 rows=14652 loops=5) Hash Cond: (e.id1 = a.id1) Join Filter: ((a.created_at >= e.effective_at) AND (a.created_at < e.expired_at)) Rows Removed by Join Filter: 66412 -> Parallel Seq Scan on dim_1 e (cost=0.00..133595.06 rows=153406 width=20) (actual time=0.029..137.850 rows=141713 loops=5) -> Hash (cost=1417.59..1417.59 rows=73259 width=20) (actual time=73.284..73.284 rows=73259 loops=5) Buckets: 131072 Batches: 1 Memory Usage: 5031kB -> Seq Scan on staging a (cost=0.00..1417.59 rows=73259 width=20) (actual time=0.016..36.003 rows=73259 loops=5) -> Index Scan using dim_2_id on dim_2 g (cost=0.56..6.36 rows=3 width=38) (actual time=0.006..0.007 rows=1 loops=73259) Index Cond: ((id2 = a.id2) AND (a.created_at >= effective_at)) Filter: (a.created_at < expired_at) Rows Removed by Filter: 1 -> Hash (cost=10.70..10.70 rows=70 width=516) (actual time=0.028..0.028 rows=27 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on dim_3 i (cost=0.00..10.70 rows=70 width=516) (actual time=0.005..0.015 rows=27 loops=1) -> Index Scan using dim_4_id on dim_4 h (cost=0.43..4.18 rows=4 width=20) (actual time=0.004..0.004 rows=1 loops=73500) Index Cond: ((id4 = a.id4) AND (a.created_at >= effective_at)) Filter: (a.created_at < expired_at) Rows Removed by Filter: 0 -> CTE Scan on test (cost=0.00..6238.16 rows=311908 width=0) (actual time=76.938..675.796 rows=73500 loops=1) Planning time: 0.618 ms Execution time: 701.594 ms And here is the result from 9.6.3: Aggregate (cost=836957.12..836957.13 rows=1 width=8) (actual time=803.091..803.091 rows=1 loops=1) CTE test -> Gather (cost=3335.93..815257.26 rows=964438 width=4) (actual time=80.394..713.993 rows=73259 loops=1) Workers Planned: 4 Workers Launched: 4 -> Nested Loop (cost=2335.93..717813.46 rows=241110 width=4) (actual time=99.061..755.383 rows=14652 loops=5) -> Hash Left Join (cost=2335.50..333199.71 rows=68143 width=16) (actual time=99.025..579.332 rows=14652 loops=5) Hash Cond: ((g.text_field)::text = (i.text_field)::text) -> Nested Loop (cost=2333.89..332924.06 rows=68143 width=30) (actual time=98.905..562.756 rows=14652 loops=5) -> Hash Join (cost=2333.33..196015.85 rows=21048 width=16) (actual time=98.858..369.890 rows=14652 loops=5) Hash Cond: (e.id1 = a.id1) Join Filter: ((a.created_at >= e.effective_at) AND (a.created_at < e.expired_at)) Rows Removed by Join Filter: 66412 -> Parallel Seq Scan on dim_1 e (cost=0.00..91462.65 rows=177066 width=20) (actual time=0.032..165.829 rows=141713 loops=5) -> Hash (cost=1417.59..1417.59 rows=73259 width=20) (actual time=97.828..97.828 rows=73259 loops=5) Buckets: 131072 Batches: 1 Memory Usage: 5031kB -> Seq Scan on staging a (cost=0.00..1417.59 rows=73259 width=20) (actual time=0.019..44.612 rows=73259 loops=5) -> Index Scan using dim_2_id on dim_2 g (cost=0.56..6.47 rows=3 width=38) (actual time=0.011..0.011 rows=1 loops=73259) Index Cond: ((id2 = a.id2) AND (a.created_at >= effective_at)) Filter: (a.created
[GENERAL] Bug in postgres 9.6.2?
So, I have this query with nothing non-deterministic in it, yet I can run it multiple times and get different results in postgres 9.6.2: with test as ( select g.id2 from staging a join dim_1 e on e.id1 = a.id1 and a.created_at >= e.effective_at and a.created_at < e.expired_at join dim_2 g on g.id2 = a.id2 and a.created_at >= g.effective_at and a.created_at < g.expired_at left join dim_3 i on i.text_field = g.text_field join dim_4 h on h.id4 = a.id4 and a.created_at >= h.effective_at and a.created_at < h.expired_at) select count(*) from test; Now if I rework this query slightly, it produces a consistent result: select count(*) from ( select g.id2 from staging a join dim_1 e on e.id1 = a.id1 and a.created_at >= e.effective_at and a.created_at < e.expired_at join dim_2 g on g.id2 = a.id2 and a.created_at >= g.effective_at and a.created_at < g.expired_at left join dim_3 i on i.text_field = g.text_field join dim_4 h on h.id4 = a.id4 and a.created_at >= h.effective_at and a.created_at < h.expired_at) as test; Furthermore, if I run the first query on postgres 9.6.3 rather than 9.6.2, it also seems to produce a consistent result. Is it possible that this is a bug in 9.6.2 that was fixed in 9.6.3? Or is it more likely that somehow just restarting the instance (after doing the point release upgrade) is what fixed this issue. Noteworthy is that all the "created_at", "effective_at" and "expired_at" fields are all timestamp without time zone. All the id's are integers. Thanks in advance, Greig Wise -- View this message in context: http://www.postgresql-archive.org/Bug-in-postgres-9-6-2-tp5972185.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Weirdness with "not in" query
Wow. That is exactly it. Thank you. I really would not have expected there to be NULLs in that field. Geez. -- View this message in context: http://www.postgresql-archive.org/Weirdness-with-not-in-query-tp5965573p5965576.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weirdness with "not in" query
So, I'm using postgres version 9.6.3 on a mac and the results to this series of queries seems very strange to me: db# select count(*) from table1 where id in (1706302,1772130,1745499,1704077); count --- 4 (1 row) db# select count(*) from table2 where table1_id in (1706302,1772130,1745499,1704077); count --- 0 (1 row) db# select count(*) from table1 where id not in (select table1_id from table2); count --- 0 (1 row) I would expect the "not in" query to return a result of at least 4. Am I totally misunderstanding how this should work (I really don't think so) or is something wrong? Thanks, Greig Wise -- View this message in context: http://www.postgresql-archive.org/Weirdness-with-not-in-query-tp5965573.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Query with large in clauses uses a lot of memory
Wow. Thanks for the prompt answer. As a follow-up I was wondering if maybe there would be a way to tell it to NOT try to plan/execute the query (and instead throw an error) if the memory usage exceeded X. Thanks again. Greig -- View this message in context: http://postgresql.nabble.com/Query-with-large-in-clauses-uses-a-lot-of-memory-tp5931716p5932279.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query with large in clauses uses a lot of memory
I had an issue today where the OOM killer terminated one of my postgres processes. On my server I have 8 GB of RAM, shared_memory is 1 GB and work_memory is 24MB. I have connection pooling which limits us to 25 connections. Even if I'm maxed out there, I'm still only using 1.6 MB of RAM of my 8 which seems like it shouldn't be a problem. Looking through my postgres logs, I noticed that right about the time of the OOM incident, I had some queries running with pretty massive in clauses (thank you ruby/ActiveRecord). One of the queries was about 28MB in size. So, I decided to try an experiment. I wrote 2 queries as follows: 1 ) select pg_sleep(100) ; 2 ) with q (s1, s2) as (select pg_sleep(100), 1) select * from q where s2 in ( 1, ) I ran those queries via psql and did this: -sh-4.1$ ps aux | grep -i -E "local|COMMAND" | grep -v ruby USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND postgres 20896 27.0 28.2 3416812 2132112 ? Ss 21:18 0:02 postgres: hireology hireology [local] SELECT postgres 20899 0.0 0.0 1281368 4800 ?Ss 21:18 0:00 postgres: hireology hireology [local] SELECT It looks to me like the connection running the big query is using about 2GB more memory than the other one. I could see why it might use *some* more (like 28MB more?), but 2GB more seems excessive. So, the question is why does it use so much more memory. And is there anything I can do to limit this problem other than fixing the silly queries? Thanks in advance for any help, Greig Wise -- View this message in context: http://postgresql.nabble.com/Query-with-large-in-clauses-uses-a-lot-of-memory-tp5931716.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] GSS Authentication
OMG!!! I finally got it working. Problem was that on the windows side on the service account within the account options, we needed to check "Use DES encryption types for this account". I had that changed on the AD side and that fixed the whole problem. Bryan, if you're still trying to get this to work I'd be happy to help if I can. Thanks all for the help. Greig - Original Message - From: "Greig Wise" To: "Bryan Montgomery" Cc: "pgsql-general" Sent: Wednesday, June 16, 2010 1:09:16 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication Nope. I get this: kinit(v5): Client not found in Kerberos database while getting initial credentials On Jun 15, 2010, at 10:03 PM, Bryan Montgomery wrote: I'm not in front of a linux machine, but does kinit -kt postgres.keytab -S POSTGRES/ host.domain.com grant a ticket without asking for the password? On Tue, Jun 15, 2010 at 2:38 PM, < greigw...@comcast.net > wrote: As suggested below, I just tried this: kinit -S POSTGRES/ host.domain.com user (where user is my account name in AD). That then asked for my password and when I entered it, it seemed to work. And now klist shows that I have a ticket. Doing it this way though, the keytab file doesn't seem to come into play. Does this point to something in my keytab file being wrong? I did this: klist -ket postgres.keytab and got: KVNO Timestamp Principal - 3 12/31/69 19:00:00 POSTGRES/ host.domain.com @ DOMAIN.COM (DES cbc mode with RSA-MD5) That timestamp seems kinda funky, doesn't it? 12/31/69? That can't be right, can it? Thanks again. Greig - Original Message - From: "Stephen Frost" < sfr...@snowman.net > To: "Bryan Montgomery" < mo...@english.net > Cc: greigw...@comcast.net , pgsql-general@postgresql.org Sent: Saturday, June 12, 2010 8:35:13 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * Bryan Montgomery ( mo...@english.net ) wrote: > I've been trying this as well off and on. In my case I'm not convinced the > AD configuration is correct (And someone else manages that). Yeah, that can be a challenge.. but it's *definitely* possible to get it set up and working correctly. > Can you use kinit with the key tab options to get a good response from the > server? I think I should be able to do this .. > $ kinit -V -k -t poe3b.keytab HTTP/ poe3b.lab2k.net > kinit(v5): Preauthentication failed while getting initial credentials err, I'm not sure that should be expected to work. What does klist -ek return? Also, you should be able to kinit to *your* princ in the AD, and if you can do that, you should be able to use your princ to request the service princ ticket from the KDC by doing kinit -S HTTP/ poe3b.lab2k.net your.princ Also, provided your *client* is set up/configured correctly, you should be able to see that it acquires the ticket (by using klist) when you try to connect to the server, even if the server is misconfigured. > I'd be interested to know if you get something different - and the steps you > went through on the AD side. You have to create an account in Active Directory for the PG service and then use: ktpass /princ POSTGRES/ myserver.mydomain.com @ MYDOMAIN.COM /mapuser postg...@mydomain.com /pass mypass /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL /out krb5.keytab Then copy that krb5.keytab to the server. Note that you then have to adjust the server config to have service name set to POSTGRES, and adjust clients using the environment variables to indiciate they should ask for POSTGRES (instead of the postgres default). Thanks, Stephen
Re: [GENERAL] GSS Authentication
OK. So, to get it to use a different encryption type, I'm thinking I'd have to specify that when I create the keytab (and then uncheck the Use DES option on the account setup in Windows). So, when I created my keytab, I used a command like this on the AD side: ktpass -princ POSTGRES/host.domain@domain.com -crypto DES-CBC-MD5 -mapuser host -pass mypasswd -out postgres.keytab So for the -crypto option, what would be your recommendation for what I should use and would this require changes on the DB server side? Thanks again. Greig - Original Message - From: "Stephen Frost" To: greigw...@comcast.net Cc: "Bryan Montgomery" , "pgsql-general" Sent: Wednesday, June 16, 2010 11:05:16 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication Greig, * greigw...@comcast.net (greigw...@comcast.net) wrote: > I finally got it working. Problem was that on the windows side on the service > account within the account options, we needed to check "Use DES encryption > types for this account". I had that changed on the AD side and that fixed the > whole problem. Great, glad to hear you got it working. Just to reiterate- you really should be looking at using a 2008 AD with AES encryption types instead of DES. DES is depreciated and no longer secure given today's computers. Thanks, Stephen signature.asc Description: Digital signature -- 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] GSS Authentication
One interesting thing I just came across. I had another user try to connect to my DB using the GSS authentication and it failed. I checked everything out on the client side and it seemed to be OK, so I was puzzled. So then I had another user try and it worked just fine for him. That's weird, right? So then I went up and talked to our sysadmin guy who sets up the windows domain stuff and asked him if we could look at the accounts. The 2 accounts that worked (mine and the 3rd guy) were in a certain group and the other was not a member of that group. So, I had them put the user into that group. Then it suddenly starts working fine for that user. So, evidently, there is some setting on the Windows side for each account which authenticates via GSS that is required for the authentication to work right. We're going to go through the privs for that group and see if anything sticks out for us, but in the meantime, does anyone have any idea why the one user wouldn't work? Thanks, Greig - Original Message - From: "Stephen Frost" To: greigw...@comcast.net Cc: "Bryan Montgomery" , "pgsql-general" Sent: Wednesday, June 16, 2010 11:05:16 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication Greig, * greigw...@comcast.net (greigw...@comcast.net) wrote: > I finally got it working. Problem was that on the windows side on the service > account within the account options, we needed to check "Use DES encryption > types for this account". I had that changed on the AD side and that fixed the > whole problem. Great, glad to hear you got it working. Just to reiterate- you really should be looking at using a 2008 AD with AES encryption types instead of DES. DES is depreciated and no longer secure given today's computers. Thanks, Stephen signature.asc Description: Digital signature -- 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] GSS Authentication
2008 - Original Message - From: "Stephen Frost" To: greigw...@comcast.net Cc: "Bryan Montgomery" , "pgsql-general" Sent: Wednesday, June 16, 2010 11:32:05 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * greigw...@comcast.net (greigw...@comcast.net) wrote: > So for the -crypto option, what would be your recommendation for what I > should use and would this require changes on the DB server side? What OS are you running on your AD..? 2003? 2008? Stephen signature.asc Description: Digital signature -- 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] GSS Authentication
Bryan, one thing I did have to do on the Linux side was to set dns_lookup_kdc = true in my krb5.conf file in the libdefaults section. Hope that helps. Greig - Original Message - From: greigw...@comcast.net To: "Bryan Montgomery" , sfr...@snowman.net Cc: "pgsql-general" Sent: Wednesday, June 16, 2010 10:17:10 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication OMG!!! I finally got it working. Problem was that on the windows side on the service account within the account options, we needed to check "Use DES encryption types for this account". I had that changed on the AD side and that fixed the whole problem. Bryan, if you're still trying to get this to work I'd be happy to help if I can. Thanks all for the help. Greig - Original Message - From: "Greig Wise" To: "Bryan Montgomery" Cc: "pgsql-general" Sent: Wednesday, June 16, 2010 1:09:16 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication Nope. I get this: kinit(v5): Client not found in Kerberos database while getting initial credentials On Jun 15, 2010, at 10:03 PM, Bryan Montgomery wrote: I'm not in front of a linux machine, but does kinit -kt postgres.keytab -S POSTGRES/ host.domain.com grant a ticket without asking for the password? On Tue, Jun 15, 2010 at 2:38 PM, < greigw...@comcast.net > wrote: As suggested below, I just tried this: kinit -S POSTGRES/ host.domain.com user (where user is my account name in AD). That then asked for my password and when I entered it, it seemed to work. And now klist shows that I have a ticket. Doing it this way though, the keytab file doesn't seem to come into play. Does this point to something in my keytab file being wrong? I did this: klist -ket postgres.keytab and got: KVNO Timestamp Principal - 3 12/31/69 19:00:00 POSTGRES/ host.domain.com @ DOMAIN.COM (DES cbc mode with RSA-MD5) That timestamp seems kinda funky, doesn't it? 12/31/69? That can't be right, can it? Thanks again. Greig - Original Message - From: "Stephen Frost" < sfr...@snowman.net > To: "Bryan Montgomery" < mo...@english.net > Cc: greigw...@comcast.net , pgsql-general@postgresql.org Sent: Saturday, June 12, 2010 8:35:13 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * Bryan Montgomery ( mo...@english.net ) wrote: > I've been trying this as well off and on. In my case I'm not convinced the > AD configuration is correct (And someone else manages that). Yeah, that can be a challenge.. but it's *definitely* possible to get it set up and working correctly. > Can you use kinit with the key tab options to get a good response from the > server? I think I should be able to do this .. > $ kinit -V -k -t poe3b.keytab HTTP/ poe3b.lab2k.net > kinit(v5): Preauthentication failed while getting initial credentials err, I'm not sure that should be expected to work. What does klist -ek return? Also, you should be able to kinit to *your* princ in the AD, and if you can do that, you should be able to use your princ to request the service princ ticket from the KDC by doing kinit -S HTTP/ poe3b.lab2k.net your.princ Also, provided your *client* is set up/configured correctly, you should be able to see that it acquires the ticket (by using klist) when you try to connect to the server, even if the server is misconfigured. > I'd be interested to know if you get something different - and the steps you > went through on the AD side. You have to create an account in Active Directory for the PG service and then use: ktpass /princ POSTGRES/ myserver.mydomain.com @ MYDOMAIN.COM /mapuser postg...@mydomain.com /pass mypass /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL /out krb5.keytab Then copy that krb5.keytab to the server. Note that you then have to adjust the server config to have service name set to POSTGRES, and adjust clients using the environment variables to indiciate they should ask for POSTGRES (instead of the postgres default). Thanks, Stephen
Re: [GENERAL] GSS Authentication
I just notice that in your message you had more text further down (regarding the DES encryption). I didn't see that at first. So, I did klist -e as you suggested and I got this: Ticket cache: FILE:/tmp/krb5cc_502 Default principal: u...@domain.com Valid starting Expires Service principal 06/15/10 18:07:33 06/16/10 04:07:36 krbtgt/domain@domain.com renew until 06/16/10 04:07:33, Etype (skey, tkt): ArcFour with HMAC/md5, ArcFour with HMAC/md5 Kerberos 4 ticket cache: /tmp/tkt502 klist: You have no tickets cached Is that the problem? I don't see anything about permitted enctypes in my krb5.conf. Should I add something in there to allow DES, or should I recreate my keytab to use a different encryption type? If so, what should I use? Thanks again. I feel like I'm making progress. Greig - Original Message - From: "Stephen Frost" To: greigw...@comcast.net Cc: pgsql-general@postgresql.org, "Bryan Montgomery" Sent: Tuesday, June 15, 2010 4:25:55 PM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * greigw...@comcast.net (greigw...@comcast.net) wrote: > kinit -S POSTGRES/host.domain.com user > > (where user is my account name in AD). That then asked for my password and > when I entered it, it seemed to work. And now klist shows that I have a > ticket. Doing it this way though, the keytab file doesn't seem to come into > play. Does this point to something in my keytab file being wrong? Good that you were able to get a ticket manually. Next you need to try getting a client application (eg: psql) to get that same ticket. Before you run psql, do: kdestroy kinit export PGKRBSRVNAME=POSTGRES psql -d postgres -h host.domain.com klist And see if you acquired the same ticket you got with the manual klist. > I did this: > > klist -ket postgres.keytab > > and got: > > KVNO Timestamp Principal > - > > 3 12/31/69 19:00:00 POSTGRES/host.domain@domain.com (DES cbc mode with > RSA-MD5) > > That timestamp seems kinda funky, doesn't it? 12/31/69? That can't be right, > can it? The timestamp isn't really "right", but it shouldn't really hurt either- that's just when it was "created". The encyprtion is crappy though and might be disabled by default (MIT Kerberos recently started disabling DES and lower encryption because it's horribly insecure). Check your /etc/krb5.conf for permitted_enctypes. Also, after you get a POSTGRES/host.domain.com ticket using kinit (or psql), do a klist -e and see if the encryption type of the ticket you got matches that of the keytab. If it doesn't, then you might have created multiple keys for the same princ on the server (not generally a bad thing), but not exported and loaded all of them into the keytab on the unix system (which would be a problem...). Thanks, Stephen signature.asc Description: Digital signature -- 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] GSS Authentication
OK. I tried what you suggested. I pasted the whole sequence of commands and the results below. As you can see, the connection to postgres still failed, but it looks like it actually acquired the ticket (I think). What do you make of that? Thanks again for the help. Greig [u...@client ~]$ kdestroy [u...@client ~]$ klist klist: No credentials cache found (ticket cache FILE:/tmp/krb5cc_503) Kerberos 4 ticket cache: /tmp/tkt503 klist: You have no tickets cached [u...@client ~]$ kinit Password for u...@domain.com: [u...@client ~]$ klist Ticket cache: FILE:/tmp/krb5cc_503 Default principal: u...@domain.com Valid starting Expires Service principal 06/15/10 17:16:37 06/16/10 03:16:42 krbtgt/domain@domain.com renew until 06/16/10 03:16:37 Kerberos 4 ticket cache: /tmp/tkt503 klist: You have no tickets cached [u...@client ~]$ psql -d postgres -h server.DOMAIN.COM psql: FATAL: accepting GSS security context failed DETAIL: Miscellaneous failure: Unknown code krb5 181 [u...@client ~]$ klist Ticket cache: FILE:/tmp/krb5cc_503 Default principal: u...@domain.com Valid starting Expires Service principal 06/15/10 17:16:37 06/16/10 03:16:42 krbtgt/domain@domain.com renew until 06/16/10 03:16:37 06/15/10 17:17:01 06/16/10 03:16:42 POSTGRES/server.domain@domain.com renew until 06/16/10 03:16:37 Kerberos 4 ticket cache: /tmp/tkt503 klist: You have no tickets cached - Original Message - From: "Stephen Frost" To: greigw...@comcast.net Cc: pgsql-general@postgresql.org, "Bryan Montgomery" Sent: Tuesday, June 15, 2010 4:25:55 PM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * greigw...@comcast.net (greigw...@comcast.net) wrote: > kinit -S POSTGRES/host.domain.com user > > (where user is my account name in AD). That then asked for my password and > when I entered it, it seemed to work. And now klist shows that I have a > ticket. Doing it this way though, the keytab file doesn't seem to come into > play. Does this point to something in my keytab file being wrong? Good that you were able to get a ticket manually. Next you need to try getting a client application (eg: psql) to get that same ticket. Before you run psql, do: kdestroy kinit export PGKRBSRVNAME=POSTGRES psql -d postgres -h host.domain.com klist And see if you acquired the same ticket you got with the manual klist. > I did this: > > klist -ket postgres.keytab > > and got: > > KVNO Timestamp Principal > - > > 3 12/31/69 19:00:00 POSTGRES/host.domain@domain.com (DES cbc mode with > RSA-MD5) > > That timestamp seems kinda funky, doesn't it? 12/31/69? That can't be right, > can it? The timestamp isn't really "right", but it shouldn't really hurt either- that's just when it was "created". The encyprtion is crappy though and might be disabled by default (MIT Kerberos recently started disabling DES and lower encryption because it's horribly insecure). Check your /etc/krb5.conf for permitted_enctypes. Also, after you get a POSTGRES/host.domain.com ticket using kinit (or psql), do a klist -e and see if the encryption type of the ticket you got matches that of the keytab. If it doesn't, then you might have created multiple keys for the same princ on the server (not generally a bad thing), but not exported and loaded all of them into the keytab on the unix system (which would be a problem...). Thanks, Stephen signature.asc Description: Digital signature -- 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] GSS Authentication
As suggested below, I just tried this: kinit -S POSTGRES/host.domain.com user (where user is my account name in AD). That then asked for my password and when I entered it, it seemed to work. And now klist shows that I have a ticket. Doing it this way though, the keytab file doesn't seem to come into play. Does this point to something in my keytab file being wrong? I did this: klist -ket postgres.keytab and got: KVNO Timestamp Principal - 3 12/31/69 19:00:00 POSTGRES/host.domain@domain.com (DES cbc mode with RSA-MD5) That timestamp seems kinda funky, doesn't it? 12/31/69? That can't be right, can it? Thanks again. Greig - Original Message - From: "Stephen Frost" To: "Bryan Montgomery" Cc: greigw...@comcast.net, pgsql-general@postgresql.org Sent: Saturday, June 12, 2010 8:35:13 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication * Bryan Montgomery (mo...@english.net) wrote: > I've been trying this as well off and on. In my case I'm not convinced the > AD configuration is correct (And someone else manages that). Yeah, that can be a challenge.. but it's *definitely* possible to get it set up and working correctly. > Can you use kinit with the key tab options to get a good response from the > server? I think I should be able to do this .. > $ kinit -V -k -t poe3b.keytab HTTP/poe3b.lab2k.net > kinit(v5): Preauthentication failed while getting initial credentials err, I'm not sure that should be expected to work. What does klist -ek return? Also, you should be able to kinit to *your* princ in the AD, and if you can do that, you should be able to use your princ to request the service princ ticket from the KDC by doing kinit -S HTTP/poe3b.lab2k.net your.princ Also, provided your *client* is set up/configured correctly, you should be able to see that it acquires the ticket (by using klist) when you try to connect to the server, even if the server is misconfigured. > I'd be interested to know if you get something different - and the steps you > went through on the AD side. You have to create an account in Active Directory for the PG service and then use: ktpass /princ POSTGRES/myserver.mydomain@mydomain.com /mapuser postg...@mydomain.com /pass mypass /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL /out krb5.keytab Then copy that krb5.keytab to the server. Note that you then have to adjust the server config to have service name set to POSTGRES, and adjust clients using the environment variables to indiciate they should ask for POSTGRES (instead of the postgres default). Thanks, Stephen signature.asc Description: Digital signature -- 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] GSS Authentication
One other thing possibly worth noting I tried to connect to the Postgres DB using pgAdmin III and it gives a very similar error to the test perl program that I wrote: Error connecting to the server: FATAL: accepting GSS security context failed DETAIL: Miscellaneous failure: Unknown code ggss 3 So, it seems as if it must be something on the server side as 2 different clients are failing in the same way I think. Thanks again. Greig - Original Message - From: greigw...@comcast.net To: "Stephen Frost" Cc: pgsql-general@postgresql.org Sent: Monday, June 14, 2010 3:22:36 PM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] GSS Authentication Thanks for the help. In response to your questions, I did make sure the service name was right. klist -k on the keytab file gives: KVNO Principal -- 3 POSTGRES/hostname.domain@domain.com I replaced our real domain with an example obviously, but that's what it looks like. I'm thinking it looks correct. By testing with psql locally first, do you mean running psql right on the postgres server itself? To test the GSS authentication? I tried to set the local connections in the pg_hba.conf to use gss authentication locally, but then when I tried to restart postgres, the logs said that GSS authentication wasn't allowed for local connections (see log message below): 2010-06-14 14:42:24 EDTLOG: F: gssapi authentication is not supported on local sockets I did change the default service name to POSTGRES instead of postgres. Reverse DNS is working and I think the default realm is right. I'm a little unclear on exactly what that should be, but I'm thinking that based on the example above it should be something like "domain.com". I did give the server side logs in my original message, but I'll include more. So, in this log entry I'll paste below (it's a little lengthy), we have a startup, then a failed connection from the windows client, then a shutdown. What should I try next? Thanks for the help. Greig Wise 2010-06-14 15:12:21 EDTLOG: 0: database system was shut down at 2010-06-14 15:12:08 EDT 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5243 2010-06-14 15:12:21 EDTDEBUG: 0: checkpoint record is at 1/BD20 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5340 2010-06-14 15:12:21 EDTDEBUG: 0: redo record is at 1/BD20; shutdown TRUE 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5366 2010-06-14 15:12:21 EDTDEBUG: 0: next transaction ID: 0/696; next OID: 16400 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5370 2010-06-14 15:12:21 EDTDEBUG: 0: next MultiXactId: 1; next MultiXactOffset: 0 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5373 2010-06-14 15:12:21 EDTDEBUG: 0: transaction ID wrap limit is 2147484295, limited by database "template1" 2010-06-14 15:12:21 EDTLOCATION: SetTransactionIdLimit, varsup.c:285 2010-06-14 15:12:21 EDTDEBUG: 0: shmem_exit(0): 3 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:21 EDTDEBUG: 0: proc_exit(0): 2 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: proc_exit_prepare, ipc.c:183 2010-06-14 15:12:21 EDTDEBUG: 0: exit(0) 2010-06-14 15:12:21 EDTLOCATION: proc_exit, ipc.c:135 2010-06-14 15:12:21 EDTDEBUG: 0: shmem_exit(-1): 0 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:21 EDTDEBUG: 0: proc_exit(-1): 0 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: proc_exit_prepare, ipc.c:183 2010-06-14 15:12:21 EDTDEBUG: 0: reaping dead processes 2010-06-14 15:12:21 EDTLOCATION: reaper, postmaster.c:2238 2010-06-14 15:12:21 EDTLOG: 0: autovacuum launcher started 2010-06-14 15:12:21 EDTLOCATION: AutoVacLauncherMain, autovacuum.c:529 2010-06-14 15:12:21 EDTLOG: 0: database system is ready to accept connections 2010-06-14 15:12:21 EDTLOCATION: reaper, postmaster.c:2326 2010-06-14 15:12:26 EDTDEBUG: 0: forked new backend, pid=4750 socket=8 2010-06-14 15:12:26 EDTLOCATION: BackendStartup, postmaster.c:3085 2010-06-14 15:12:26 EDTDEBUG: 0: Processing received GSS token of length 2007 2010-06-14 15:12:26 EDTLOCATION: pg_GSS_recvauth, auth.c:965 2010-06-14 15:12:26 EDTDEBUG: 0: gss_accept_sec_context major: 851968, minor: -2045022973, outlen: 0, outflags: 7f 2010-06-14 15:12:26 EDTLOCATION: pg_GSS_recvauth, auth.c:984 2010-06-14 15:12:26 EDTFATAL: XX000: accepting GSS security context failed 2010-06-14 15:12:26 EDTDETAIL: Miscellaneous failure: Unknown code ggss 3 2010-06-14 15:12:26 EDTLOCATION: pg_GSS_error, auth.c:866 2010-06-14 15:12:26 EDTDEBUG: 0: shmem_exit(1): 0 callbacks to make 2010-06-14 15:12:26 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:26 EDTDEBUG: 0: pr
Re: [GENERAL] GSS Authentication
Thanks for the help. In response to your questions, I did make sure the service name was right. klist -k on the keytab file gives: KVNO Principal -- 3 POSTGRES/hostname.domain@domain.com I replaced our real domain with an example obviously, but that's what it looks like. I'm thinking it looks correct. By testing with psql locally first, do you mean running psql right on the postgres server itself? To test the GSS authentication? I tried to set the local connections in the pg_hba.conf to use gss authentication locally, but then when I tried to restart postgres, the logs said that GSS authentication wasn't allowed for local connections (see log message below): 2010-06-14 14:42:24 EDTLOG: F: gssapi authentication is not supported on local sockets I did change the default service name to POSTGRES instead of postgres. Reverse DNS is working and I think the default realm is right. I'm a little unclear on exactly what that should be, but I'm thinking that based on the example above it should be something like "domain.com". I did give the server side logs in my original message, but I'll include more. So, in this log entry I'll paste below (it's a little lengthy), we have a startup, then a failed connection from the windows client, then a shutdown. What should I try next? Thanks for the help. Greig Wise 2010-06-14 15:12:21 EDTLOG: 0: database system was shut down at 2010-06-14 15:12:08 EDT 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5243 2010-06-14 15:12:21 EDTDEBUG: 0: checkpoint record is at 1/BD20 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5340 2010-06-14 15:12:21 EDTDEBUG: 0: redo record is at 1/BD20; shutdown TRUE 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5366 2010-06-14 15:12:21 EDTDEBUG: 0: next transaction ID: 0/696; next OID: 16400 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5370 2010-06-14 15:12:21 EDTDEBUG: 0: next MultiXactId: 1; next MultiXactOffset: 0 2010-06-14 15:12:21 EDTLOCATION: StartupXLOG, xlog.c:5373 2010-06-14 15:12:21 EDTDEBUG: 0: transaction ID wrap limit is 2147484295, limited by database "template1" 2010-06-14 15:12:21 EDTLOCATION: SetTransactionIdLimit, varsup.c:285 2010-06-14 15:12:21 EDTDEBUG: 0: shmem_exit(0): 3 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:21 EDTDEBUG: 0: proc_exit(0): 2 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: proc_exit_prepare, ipc.c:183 2010-06-14 15:12:21 EDTDEBUG: 0: exit(0) 2010-06-14 15:12:21 EDTLOCATION: proc_exit, ipc.c:135 2010-06-14 15:12:21 EDTDEBUG: 0: shmem_exit(-1): 0 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:21 EDTDEBUG: 0: proc_exit(-1): 0 callbacks to make 2010-06-14 15:12:21 EDTLOCATION: proc_exit_prepare, ipc.c:183 2010-06-14 15:12:21 EDTDEBUG: 0: reaping dead processes 2010-06-14 15:12:21 EDTLOCATION: reaper, postmaster.c:2238 2010-06-14 15:12:21 EDTLOG: 0: autovacuum launcher started 2010-06-14 15:12:21 EDTLOCATION: AutoVacLauncherMain, autovacuum.c:529 2010-06-14 15:12:21 EDTLOG: 0: database system is ready to accept connections 2010-06-14 15:12:21 EDTLOCATION: reaper, postmaster.c:2326 2010-06-14 15:12:26 EDTDEBUG: 0: forked new backend, pid=4750 socket=8 2010-06-14 15:12:26 EDTLOCATION: BackendStartup, postmaster.c:3085 2010-06-14 15:12:26 EDTDEBUG: 0: Processing received GSS token of length 2007 2010-06-14 15:12:26 EDTLOCATION: pg_GSS_recvauth, auth.c:965 2010-06-14 15:12:26 EDTDEBUG: 0: gss_accept_sec_context major: 851968, minor: -2045022973, outlen: 0, outflags: 7f 2010-06-14 15:12:26 EDTLOCATION: pg_GSS_recvauth, auth.c:984 2010-06-14 15:12:26 EDTFATAL: XX000: accepting GSS security context failed 2010-06-14 15:12:26 EDTDETAIL: Miscellaneous failure: Unknown code ggss 3 2010-06-14 15:12:26 EDTLOCATION: pg_GSS_error, auth.c:866 2010-06-14 15:12:26 EDTDEBUG: 0: shmem_exit(1): 0 callbacks to make 2010-06-14 15:12:26 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:26 EDTDEBUG: 0: proc_exit(1): 1 callbacks to make 2010-06-14 15:12:26 EDTLOCATION: proc_exit_prepare, ipc.c:183 2010-06-14 15:12:26 EDTDEBUG: 0: exit(1) 2010-06-14 15:12:26 EDTLOCATION: proc_exit, ipc.c:135 2010-06-14 15:12:26 EDTDEBUG: 0: shmem_exit(-1): 0 callbacks to make 2010-06-14 15:12:26 EDTLOCATION: shmem_exit, ipc.c:211 2010-06-14 15:12:26 EDTDEBUG: 0: proc_exit(-1): 0 callbacks to make 2010-06-14 15:12:26 EDTLOCATION: proc_exit_prepare, ipc.c:183 2010-06-14 15:12:26 EDTDEBUG: 0: reaping dead processes 2010-06-14 15:12:26 EDTLOCATION: reaper, postmaster.c:2238 2010-06-14 15:12:26 EDTDEBUG: 0: server process (PID 4750) exited with exit code 1 2010-06-14
[GENERAL] GSS Authentication
I'm trying to get my PostgreSQL server on Linux configured so that I can
connect from a Windows client using GSS Authentication against Active
Directory. I found some helpful references on how to do this, but I'm still
coming up short. To summarize what I've done so far by way of configuration:
1) On the Linux server, setup my krb5.conf file such that I can get a ticket
from AD using kinit and confirm using klist.
2) Setup a new account in AD and used ktpass to create a keytab file for the
SPN.
3) Copied the keytab file onto my postgres server and updated my
postgresql.conf file appropriately (set the krb_server_keyfile to point to the
file I just created.)
Then I wrote a little test Perl program to connect to my postgres database.
use DBI;
use strict;
my $dbh =
DBI->connect('DBI:Pg:dbname=postgres;host=host.domain.com;krbsrvname=POSTGRES')
or die DBI->errstr;
When I try to run the Perl program I get this error:
DBI connect('dbname=postgres;host=host.domain.com;krbsrvname=POSTGRES')
failed: FATAL: accepting GSS security context failed
DETAIL: Miscellaneous failure: Unknown code ggss 3 at g.pl line 4
FATAL: accepting GSS security context failed
DETAIL: Miscellaneous failure: Unknown code ggss 3 at g.pl line 4
I then ramped up the debug logging on the postgres side and get this off the
server:
2010-06-11 17:23:49 EDTDEBUG: 0: Processing received GSS token of length
2119
2010-06-11 17:23:49 EDTLOCATION: pg_GSS_recvauth, auth.c:965
2010-06-11 17:23:49 EDTDEBUG: 0: gss_accept_sec_context major: 851968,
minor: -2045022973, outlen: 0, outflags: 7f
2010-06-11 17:23:49 EDTLOCATION: pg_GSS_recvauth, auth.c:984
2010-06-11 17:23:49 EDTFATAL: XX000: accepting GSS security context failed
2010-06-11 17:23:49 EDTDETAIL: Miscellaneous failure: Unknown code ggss 3
2010-06-11 17:23:49 EDTLOCATION: pg_GSS_error, auth.c:866
I'm using PostgreSQL 8.4.4 on Enterprise Linux 4.
Can anyone offer any suggestions?
Thanks in advance.
Greig
