pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to create an user with ONLY select permission and nothing else?
4+ messages / 4 participants
[nested] [flat]
* how to create an user with ONLY select permission and nothing else?
@ 2007-10-11 18:33 Jessica Richard <rjessil@yahoo.com>
0 siblings, 3 replies; 4+ messages in thread
From: Jessica Richard @ 2007-10-11 18:33 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to create an user with ONLY select permission and nothing else?
@ 2007-10-11 19:35 Milen A. Radev <milen@radev.net>
parent: Jessica Richard <rjessil@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Milen A. Radev @ 2007-10-11 19:35 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to create an user with ONLY select permission and nothing else?
@ 2007-10-11 20:02 Kevin Kempter <kevin@kevinkempterllc.com>
parent: Jessica Richard <rjessil@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Kevin Kempter @ 2007-10-11 20:02 UTC (permalink / raw)
To: pgsql-admin
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to create an user with ONLY select permission and nothing else?
@ 2007-10-11 20:13 Agustin Ignacio Genoves <agustingenoves@gmail.com>
parent: Jessica Richard <rjessil@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Agustin Ignacio Genoves @ 2007-10-11 20:13 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; pgsql-admin
With this you give permission to a table
GRANT SELECT ON TABLE table TO user;
2007/10/11, Jessica Richard <rjessil@yahoo.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!
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-10-11 20:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-11 18:33 how to create an user with ONLY select permission and nothing else? Jessica Richard <rjessil@yahoo.com>
2007-10-11 19:35 ` Milen A. Radev <milen@radev.net>
2007-10-11 20:02 ` Kevin Kempter <kevin@kevinkempterllc.com>
2007-10-11 20:13 ` Agustin Ignacio Genoves <agustingenoves@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox