For When You Can't Have The Real Thing
[ start | index | login ]
start > dave > experiments > Net Flows > 2009-04-20 > 1

2009-04-20 #1

Created by dave. Last edited by dave, 9 years and 239 days ago. Viewed 3,490 times. #4
[diff] [history] [edit] [rdf]
labels
attachments

More Postgres

User issues today

To list users (this is inside a psql session):

netflow=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |          |
 netflow  |    16385 | f           | f        | f         | ******** |          |

More to the point: creating a db for another user (from the shell):

$ createdb -O netflow netflow
CREATE DATABASE

Change a user's postgres password:

$ psql netflow
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type: copyright for distribution terms h for help with SQL commands ? for help with psql commands g or terminate with semicolon to execute query q to quit

netflow=# alter user netflow with password 'newpassword'; ALTER ROLE netflow=# q $

So it turns out I have Postgresql 8.1.something in CentOS 5, not the 7.4 I've been playing with up until now. So by default, postgres wants to do some kind of 'ident' authorization, which won't work for some reason. So in ~postgres/data/pg_hba.conf I changed this line:

# IPv4 local connections:
host    all         all         127.0.0.1/32          ident sameuser
To this:
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
(Also good to know is that the tcpip_socket directive is no longer required/supported.) Restart postgres, and now my database reset-script works :)

inet values

Insert: You have to protect it with a quote:

postgres=# create table inettest (a inet); 
CREATE TABLE 
postgres=# insert into inettest values 
('192.168.0.1'),('192.168.1.1'),('10.0.0.1'); 
INSERT 0 3

Netmasking works:

select srcip from flows where '72.1.205.128/25' >> srcip ;
    srcip
--------------
 72.1.205.210
 72.1.205.210
 72.1.205.210
 72.1.205.173
 72.1.205.247
 72.1.205.181
 72.1.205.247
[...]

Neat.

no comments | post comment
This is a collection of techical information, much of it learned the hard way. Consider it a lab book or a /info directory. I doubt much of it will be of use to anyone else.

Useful:


snipsnap.org | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt