dissallowing access to databases

zaor@sky.pl (Pawel Zaorski) writes:

Hello.

How can I prevent user from connecting to certain databases?

For example:

user1 has database user1. But he can easily do '\c otherdb' to connect
to a database own by other user.
And what scares me most, he cat create his own tables in this
database(!!)

In my pg_hba.conf I have a line:

local all crypt

which (IMO) means that any user can connect to any database. How to
change this to allow users connect *only* to their db?

That the user can connect to other databases, indicates that you have
created that user as a superuser, i.e. he have permission to create
new postgres users. If the user is not a superuser, he shouldn't be
able to connect to other databases, than the ones he own or have
explicit permission to.

Tomas

Pawel,

How can I prevent user from connecting to certain databases?

IIRC, users are server-wide, so there's no way to. To protect your tables,
you normally do something like:

create table blabla(
<...>
);

revoke all on blabla from public;
grant select on blabla to blabla_user;

As long as data dictionary is also kept in relations, you should be able
to protect it that way, too. It seems that only DB superusers and owners
have insert/delete rights on data dictionary.

Ed

---
Well I tried to be meek
And I have tried to be mild
But I spat like a woman
And I sulked like a child
I have lived behind the walls
That have made me alone
Striven for peace
Which I never have known

Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)

That the user can connect to other databases, indicates that you have
created that user as a superuser

Works perfectly for a simple mortal here. Moreof, a user can create his
tables:

copl=# select * from pg_shadow where usename = 'cdl_user';
usename | cdl_user
usesysid | 307
usecreatedb | f
usetrace | f
usesuper | f
usecatupd | f
passwd |
valuntil |

copl=# \c copl cdl_user
You are now connected to database copl as user cdl_user.
copl=> \dt
Name | copl_global
Type | table
Owner | root
------+------------
Name | ours_hints
Type | table
Owner | root
------+------------
Name | ours_refs
Type | table
Owner | root
------+------------
Name | ours_users
Type | table
Owner | root

copl=> select * from copl_global;
ERROR: copl_global: Permission denied.
copl=> create table test( id int4 );
CREATE
copl=> drop table test;
DROP
copl=> \c mailarch cdl_user
You are now connected to database mailarch as user cdl_user.
mailarch=> \dt
Name | messages
Type | table
Owner | root

mailarch=> select * from messages;
ERROR: messages: Permission denied.
mailarch=> create table test( id int4 );
CREATE
mailarch=> drop table test;
DROP

Am I missing something?

TIA

Ed

---
Well I tried to be meek
And I have tried to be mild
But I spat like a woman
And I sulked like a child
I have lived behind the walls
That have made me alone
Striven for peace
Which I never have known

Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)

CREATE TABLE is not considered to be an access violation; we have no
concept of read-only access to a whole database, only to individual
tables.

If you want to restrict each user to connect only to his own database,
there's an option for that in pg_hba.conf:

# host DBNAME IP_ADDRESS ADDRESS_MASK AUTHTYPE [AUTH_ARGUMENT]
#
# DBNAME is the name of a PostgreSQL database, "all" to indicate all
# databases, or "sameuser" to restrict a user's access to a database with
# the same user name.

If that's not flexible enough for you, allowing a particular subset of
users to connect to a particular database is possible but tedious.
One way is to set up a separate password file for each such DB, and use
password authentication that specifies the alternate password file ---
ie, a separate pg_hba.conf line for each such DB, with the allowed users
listed in a separate file per DB. Another way is to use ident
authentication, specifying a separate "ident map name" for each DB,
and listing the allowed users for each DB under that map name.
This could stand to be improved; there's no comparable facility for
Kerberos auth methods.

regards, tom lane

Sun, Oct 15, 2000 at 11:56:01AM -0400, Tom Lane wrote:

If you want to restrict each user to connect only to his own database,
there's an option for that in pg_hba.conf:

Thanks, that solves my problem.
I missunderstood the option 'sameuser'.

--
greets,
Pawel Zaorski
http://sky.pl