On 20 August 2012 19:34, Evil <evilofrevenge@hotmail.com> wrote:
> Hello List,
> First time here also beginner to Postgres.So please forgive me for any
> mistakes.
> I'm pretty sure i have same problem.=>
> http://archives.postgresql.org/pgsql-admin/2012-03/msg00105.php
> (After searching it i found it)
> However it is not solution for me.:( *I'm pretty sure i'm doing something in
> wrong manner*.
> After issusing that revoke from public my postgres user still able to
> connect to any database.
> More over
> when executing \l user is able to see complete database names.
>
> So i have 2 questions:
> 1 ) How i can grant my user(s) to connect only to *granted* database not
> *any*
> 2 ) Users still able to execute OS (operation system) commands on system.
> This is a big security risk.How i can prevent it too.
>
> Any recommendations,manuals,helps,hints,RTFM :P are welcome;)
The postgres user is a database superuser. Trying to prevent it from
connecting to databases is understandably impossible using the GRANT
and REVOKE system, but no end-user should ever connect to the database
cluster as a superuser. Any operating system commands issued via
"unsafe" procedural languages are only run as the user the database
instance is running as, typically the user "postgres" on the OS, so it
has limited permissions by default.
But here's an example of how to prevent a typical user from connecting
to a database:
postgres=# create database test;
CREATE DATABASE
postgres=# create user test;
CREATE ROLE
postgres=# \c test test
You are now connected to database "test" as user "test".
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke connect on database test from public, test;
REVOKE
postgres=# \c test test
FATAL: permission denied for database "test"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
You can also set up further connection rules in pg_hba.conf:
http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
It will even allow you to prevent database superusers from logging in.
Regards
Thom
