PostgreSQL, connect to one table from Database?

[oRekiM]

i have a small question,

i connected my postgresql database with base

Code: Select all

dbname=timerecording  host=localhost user=testuser password=********

the database has more then 10 tables, is it possible to connect to only one table?
It is importent, because not any user, should be able to see the whole database.

[rudolfo]

The PostgreSQL database is a full fledged database with a granular access right system.
I always follow a multi level concept. I have one database user that creates the tables (and triggers and views) with ownership and hence full rights on the tables. And I have a second user that is only allowed to select from the table or update some rows.

Code: Select all

 grant select,insert on dbname.tablename to other_user

Then login as the other_user and see if you can select from that table. The GRANT command

[oRekiM]

so i have to create on my postgresql server a user with the grant select,insert on dbname.tablename to other_user option
and only change the username in the base posthresql connection?

Thanks

[rudolfo]

One main user (that you already have) that has created the table. That owner of the table can grant access rights to other users. (But the GRANT statement will fail as long as the user that receives the rights does not exist in the database).

You need the superuser of the database (postgres in all newer postgresql database versions) for one time to create a new non-privileged user:
CREATE USER theName identified by 'password';

The problem here is that this is a two-step process. The superuser creates the user (this task does not have a fine granularity). It's either normal user (without any privileges) or superuser. But a normal user can gain access rights to
other tables if the owner of that table allows them to (that's done by the GRANT command of my last post).

You might also need to modify the pg_hba.conf file to allow this new user to access the database (engine).

Once the new user exists and can connect to the database you need to grant him access for every new table that your privileged user creates. (Of course only if he or she should have access to this table). And you got the point this new user will be the user that you use for connecting to PostgreSQL within OOo Base.