Thread: dissallowing access to databases
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? PS. Forgive, if the question is obvious. Just point me to the right explanation in the doc/faq. -- greetings, Pawel Zaorski http://sky.pl
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