On 20 August 2012 19:34, Evil <evilofrevenge@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
