Re: how to create an user with ONLY select permission and nothing else?

I created a test user with the following command:

$ createuser test
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

This user at the moment does not have permission to look at any existing tables, but it can create a table:

$ psql -Utest -d postgres
Password for user test:

postgres=> create table test(x int);
CREATE TABLE

Two questions:

1. How do I create a user with ONLY select permission but nothing else (not even creating any new tables)?

2. How do I check to see what permissions a particular user currently has?

I know that \z tablename can show who has what permissions on a table, but it does not tell if a user has "create table" permission or not.

Thanks,
Jessica

---------------------------------
Shape Yahoo! in your own image. Join our Network Research Panel today!

Jessica Richard написа:
[...]
> Two questions:
>
> 1. How do I create a user with ONLY select permission but nothing else (not even creating any new tables)?

You could not create a read-only role by just using CREATE ROLE - you
could control the privileges that role has on particular DB objects.

When you create DB objects (schemas, tables, functions etc.) revoke all
privileges from PUBLIC, because every authenticated user is (part of)
PUBLIC. Then give some privileges to those roles that need them - like
"SELECT" for the role in question.

>
> 2. How do I check to see what permissions a particular user currently has?
>
> I know that \z tablename can show who has what permissions on a table, but it does not tell if a user has "create table" permission or not.

Tables are created in schemas so you need to check if the role has
"CREATE" privilege for the particular schema. Check also the "CREATE"
privilege for that role on the DB.

--
Milen A. Radev

On Thursday 11 October 2007 12:33:24 Jessica Richard wrote:
> I created a test user with the following command:
>
> $ createuser test
> Shall the new role be a superuser? (y/n) n
> Shall the new role be allowed to create databases? (y/n) n
> Shall the new role be allowed to create more new roles? (y/n) n
> CREATE ROLE
>
> This user at the moment does not have permission to look at any existing
> tables, but it can create a table:
>
>
> $ psql -Utest -d postgres
> Password for user test:
>
> postgres=> create table test(x int);
> CREATE TABLE
>
>
> Two questions:
>
> 1. How do I create a user with ONLY select permission but nothing else (not
> even creating any new tables)?
>
> 2. How do I check to see what permissions a particular user currently has?
>
> I know that \z tablename can show who has what permissions on a table, but
> it does not tell if a user has "create table" permission or not.
>
> Thanks,
> Jessica
>
>
>
> ---------------------------------
> Shape Yahoo! in your own image. Join our Network Research Panel today!

ALTER USER username SET default_transaction_read_only to true;

With this you give permission to a table
GRANT SELECT ON TABLE table TO user;

2007/10/11, Jessica Richard <rjessil(at)yahoo(dot)com>:
> I created a test user with the following command:
>
> $ createuser test
> Shall the new role be a superuser? (y/n) n
> Shall the new role be allowed to create databases? (y/n) n
> Shall the new role be allowed to create more new roles? (y/n) n
> CREATE ROLE
>
> This user at the moment does not have permission to look at any existing
> tables, but it can create a table:
>
>
> $ psql -Utest -d postgres
> Password for user test:
>
> postgres=> create table test(x int);
> CREATE TABLE
>
>
> Two questions:
>
> 1. How do I create a user with ONLY select permission but nothing else (not
> even creating any new tables)?
>
> 2. How do I check to see what permissions a particular user currently has?
>
> I know that \z tablename can show who has what permissions on a table, but
> it does not tell if a user has "create table" permission or not.
>
> Thanks,
> Jessica
>
>
>
> ________________________________
> Shape Yahoo! in your own image. Join our Network Research Panel today!
>
>