pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
[pgsql-admin] Simple question about security in 8.1.1
3+ messages / 3 participants
[nested] [flat]

* [pgsql-admin] Simple question about security in 8.1.1
@ 2006-01-23 02:02  Dan Bikle <dan.bikle@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Dan Bikle @ 2006-01-23 02:02 UTC (permalink / raw)
  To: pgsql-admin

People,

I just installed postgreSQL 8.1.1 on my free-bsd box:


Creation of a database is easy:

bash-2.05b$ id
uid=70(pgsql) gid=70(pgsql) groups=70(pgsql)
bash-2.05b$ createdb -O pgsql db10
CREATE DATABASE
bash-2.05b$

I took a peek at help:

bash-2.05b$ psql --help
This is psql 8.1.1, the PostgreSQL interactive terminal.

Usage:
  psql [OPTIONS]... [DBNAME [USERNAME]]

General options:
  -d DBNAME       specify database name to connect to (default: "pgsql")
  -c COMMAND      run only single command (SQL or internal) and exit
  -f FILENAME     execute commands from file, then exit
  -l              list available databases, then exit
  -v NAME=VALUE   set psql variable NAME to VALUE
  -X              do not read startup file (~/.psqlrc)
  --help          show this help, then exit
  --version       output version information, then exit

Input and output options:
  -a              echo all input from script
  -e              echo commands sent to server
  -E              display queries that internal commands generate
  -q              run quietly (no messages, only query output)
  -o FILENAME     send query results to file (or |pipe)
  -n              disable enhanced command line editing (readline)
  -s              single-step mode (confirm each query)
  -S              single-line mode (end of line terminates SQL command)
  -L FILENAME     send session log to file

Output format options:
  -A              unaligned table output mode (-P format=unaligned)
  -H              HTML table output mode (-P format=html)
  -t              print rows only (-P tuples_only)
  -T TEXT         set HTML table tag attributes (width, border) (-P tableattr=)
  -x              turn on expanded table output (-P expanded)
  -P VAR[=ARG]    set printing option VAR to ARG (see \pset command)
  -F STRING       set field separator (default: "|") (-P fieldsep=)
  -R STRING       set record separator (default: newline) (-P recordsep=)

Connection options:
  -h HOSTNAME     database server host or socket directory (default:
"local socket")
  -p PORT         database server port (default: "5432")
  -U NAME         database user name (default: "pgsql")
  -W              prompt for password (should happen automatically)

For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.

Report bugs to <pgsql-bugs@postgresql.org>.
bash-2.05b$

I read up on creation of a database user.
Creation of a user inside the database is easy:


bash-2.05b$ psql db10 pgsql
Welcome to psql 8.1.1, the PostgreSQL interactive terminal.

Type:  $B!@(Bcopyright for distribution terms
       $B!@(Bh for help with SQL commands
       $B!@(B? for help with psql commands
       $B!@(Bg or terminate with semicolon to execute query
       $B!@(Bq to quit

db10=# $B!@(Bh create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE rolename [, ...]
    | IN GROUP rolename [, ...]
    | ROLE rolename [, ...]
    | ADMIN rolename [, ...]
    | USER rolename [, ...]
    | SYSID uid

db10=# CREATE USER scott PASSWORD 'tiger';
CREATE ROLE
db10=#



So, now I have a user named scott with password of tiger.

I login to another unix account named 'oracle'.


bash-2.05b$ id
uid=1004(oracle) gid=1005(oracle) groups=1005(oracle)
bash-2.05b$
bash-2.05b$


I'm allowed to connect to the db10 database without authentication!:

bash-2.05b$ psql db10 scott
Welcome to psql 8.1.1, the PostgreSQL interactive terminal.

Type:  $B!@(Bcopyright for distribution terms
       $B!@(Bh for help with SQL commands
       $B!@(B? for help with psql commands
       $B!@(Bg or terminate with semicolon to execute query
       $B!@(Bq to quit

db10=> create table dropme (n integer);
CREATE TABLE
db10=> drop table dropme;
DROP TABLE
db10=>

This seems insecure.

How do I create a postgreSQL user account such that attempts to connect
to it require authentication?


-Dan



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [pgsql-admin] Simple question about security in 8.1.1
@ 2006-01-23 02:35  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Dan Bikle <dan.bikle@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2006-01-23 02:35 UTC (permalink / raw)
  To: Dan Bikle <dan.bikle@gmail.com>; +Cc: pgsql-admin

On Sun, 22 Jan 2006, Dan Bikle wrote:


> I'm allowed to connect to the db10 database without authentication!:
>
> bash-2.05b$ psql db10 scott
> Welcome to psql 8.1.1, 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
>
> db10=> create table dropme (n integer);
> CREATE TABLE
> db10=> drop table dropme;
> DROP TABLE
> db10=>
>
> This seems insecure.
>
> How do I create a postgreSQL user account such that attempts to connect
> to it require authentication?

You probably should look at the following:

http://www.postgresql.org/docs/8.1/interactive/client-authentication.html



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [pgsql-admin] Simple question about security in 8.1.1
@ 2006-01-23 04:28  Joshua D. Drake <jd@commandprompt.com>
  parent: Dan Bikle <dan.bikle@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Joshua D. Drake @ 2006-01-23 04:28 UTC (permalink / raw)
  To: Dan Bikle <dan.bikle@gmail.com>; +Cc: pgsql-admin

>
>
> This seems insecure.
>
> How do I create a postgreSQL user account such that attempts to connect
> to it require authentication?
>   

http://www.postgresql.org/docs/8.1/interactive/client-authentication.html
>
> -Dan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>   




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-01-23 04:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-23 02:02 [pgsql-admin] Simple question about security in 8.1.1 Dan Bikle <dan.bikle@gmail.com>
2006-01-23 02:35 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-01-23 04:28 ` Joshua D. Drake <jd@commandprompt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox