Re: General Query on Roles - Reg

User 'test' is allow to connect to 'postgres' because by default it gets that permission.
The default is no public access for
tables, columns, schemas, and tablespaces;  CONNECT privilege and TEMP table creation privilege for databases; EXECUTE privilege for functions; USAGE privilege for languages.

If you do not want to allow 'test' user to connect with database 'postgres', you can edit pg_hba.conf as follows.
If there is only local connections to database, add following line into pg_hba.conf
      local  all  test  reject

In case of remote database connections (Replace xxx.xx.xx.xx with IP address of remote machine from where it gets connections to database),
      host  all  test  xxx.xx.xx.xx/32 reject

After making above changes, reload the configuration.
pg_ctl -D path/to/datadir reload

Thanks,
Ketana

________________________________
From: sramay <nic(dot)srama(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Sent: Friday, 5 July 2013 4:14 AM
Subject: [GENERAL] General Query on Roles - Reg

Hi,

I always use a community source, compile and use for all those projects.
I have doubt
Assume I am creating a user say 'test' with nosuperuser privilege with just
login
and associate this user with any database using authorization.  But I am
able to create a table
for the test user in 'postgres' database.  Why user test is allowed to login
in to 'postgres' database
when he is superuser,  why can at login level stopped?  Can it be done using
pg_hba.conf?

My activity

--begin

postgres(at)otc:~/bin$
postgres(at)otc:~/bin$ psql -U postgres
psql (9.3beta2)
Type "help" for help.

postgres=# \d
No relations found.
postgres=# create role test nosuperuser login password 'test';
CREATE ROLE
postgres=# exit
postgres-# \q
postgres(at)otc:~/bin$ psql -U test postgres
psql (9.3beta2)
Type "help" for help.

postgres=> \d
No relations found.
postgres=> create table test (id numeric
postgres(> );
CREATE TABLE
postgres=> \d
      List of relations
Schema | Name | Type  | Owner
--------+------+-------+-------
public | test | table | test
(1 row)

------------------------end --

Can any one answer where I am going wrong?

Regards

Ramachandran S

--
View this message in context: http://postgresql.1045698.n5.nabble.com/General-Query-on-Roles-Reg-tp5762655.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.