Обсуждение: select only user
I created a user and granted only "select" to this user so that this user can only select the tables on a production system. But by default, this user can also CREATE TABLE successfully....
How can I revoke this "create table" privilege (and possible others) and limit this user to only the permissions granted by me?
Thanks,
Jessica
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
How can I revoke this "create table" privilege (and possible others) and limit this user to only the permissions granted by me?
Thanks,
Jessica
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
Jessica Richard написа: > I created a user and granted only "select" to this user so that this user can only select the tables on a production system.But by default, this user can also CREATE TABLE successfully.... > > How can I revoke this "create table" privilege (and possible others) and limit this user to only the permissions grantedby me? > Revoke the "create" privilege on the "public" schema (or any other schema you've created) from 'public' (that's special "role"). Also revoke the "create" privilege on the database(s) in question from the same special role 'public' to prevent creation of new schemas. More here - http://www.postgresql.org/docs/current/static/sql-grant.html. -- Milen A. Radev
Milen A. Radev написа: > Jessica Richard написа: >> I created a user and granted only "select" to this user so that this >> user can only select the tables on a production system. But by >> default, this user can also CREATE TABLE successfully.... >> >> How can I revoke this "create table" privilege (and possible others) >> and limit this user to only the permissions granted by me? >> > > Revoke the "create" privilege on the "public" schema (or any other > schema you've created) from 'public' (that's special "role"). Also > revoke the "create" privilege on the database(s) in question from the > same special role 'public' to prevent creation of new schemas. > > More here - http://www.postgresql.org/docs/current/static/sql-grant.html. Almost forgot - there is another way if the role in question would be read-only: ALTER USER username SET default_transaction_read_only to true; (http://archives.postgresql.org/pgsql-admin/2007-10/msg00101.php) -- Milen A. Radev
Thanks!!!
"ALTER USER username SET default_transaction_read_only to true" worked.
One more question:
where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is there a table that keeps this kind of info for all users?
Thanks again,
Jessica
"Milen A. Radev" <milen@radev.net> wrote:
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
"ALTER USER username SET default_transaction_read_only to true" worked.
One more question:
where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is there a table that keeps this kind of info for all users?
Thanks again,
Jessica
"Milen A. Radev" <milen@radev.net> wrote:
Milen A. Radev напиÑа:
> Jessica Richard напиÑа:
>> I created a user and granted only "select" to this user so that this
>> user can only select the tables on a production system. But by
>> default, this user can also CREATE TABLE successfully....
>>
>> How can I revoke this "create table" privilege (and possible others)
>> and limit this user to only the permissions granted by me?
>>
>
> Revoke the "create" privilege on the "public" schema (or any other
> schema you've created) from 'public' (that's special "role"). Also
> revoke the "create" privilege on the database(s) in question from the
> same special role 'public' to prevent creation of new schemas.
>
> More here - http://www.postgresql.org/docs/current/static/sql-grant.html.
Almost forgot - there is another way if the role in question would be
read-only:
ALTER USER username SET default_transaction_read_only to true;
(http://archives.postgresql.org/pgsql-admin/2007-10/msg00101.php)
--
Milen A. Radev
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Jessica Richard написа: > Thanks!!! > "ALTER USER username SET default_transaction_read_only to true" worked. > > One more question: > > where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is therea table that keeps this kind of info for all users? [...] SELECT * FROM pg_roles where rolname = '<username>'; -[ RECORD 1 ]-+------------------------------------- rolname | <username> rolsuper | f rolinherit | t rolcreaterole | f rolcreatedb | f rolcatupdate | f rolcanlogin | t rolconnlimit | -1 rolpassword | ******** rolvaliduntil | rolconfig | {default_transaction_read_only=true} oid | 53531114 (http://www.postgresql.org/docs/current/static/database-roles.html) -- Milen A. Radev