agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRoles and security
3+ messages / 3 participants
[nested] [flat]
* Roles and security
@ 2017-11-25 20:03 nikhil raj <nikhilraj474@gmail.com>
2017-11-25 23:15 ` Re: Roles and security John R Pierce <pierce@hogranch.com>
2017-11-26 15:18 ` Re: Roles and security Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 3+ messages in thread
From: nikhil raj @ 2017-11-25 20:03 UTC (permalink / raw)
To: pgsql-jobs@postgresql.org; pgsql-sql; pgsql-admin@postgresql.com; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
Hi
I am Nikhil currently working as a MS SQL DBA . Currently my company is
shifting from MS SQL to postgres
check the roles of user I want to give user roles only select, insert,
update, execute and create database to all users Permission
The users should not have these permission
drop database , delete table permission
**(Example --like in MS SQL we have like DB_roles-- ddladmin,data
writer,data reader ,etc like these do we have in postgres).***
Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Roles and security
2017-11-25 20:03 Roles and security nikhil raj <nikhilraj474@gmail.com>
@ 2017-11-25 23:15 ` John R Pierce <pierce@hogranch.com>
1 sibling, 0 replies; 3+ messages in thread
From: John R Pierce @ 2017-11-25 23:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 11/25/2017 12:03 PM, nikhil raj wrote:
> check the roles of user I want to give user roles only select,
> insert, update, execute and create database to all users Permission
>
those permissions can be assigned on a table by table basis, except
create database, thats a special permission. if you own a database
(the creator owns it by default) then you can drop it, if you don't, you
can't.
>
> The users should not have these permission
> drop database , delete table permission
generally the owners of objects can grant/revoke permissions on said
objects.
>
>
> **(Example --like in MS SQL we have like DB_roles-- ddladmin,data
> writer,data reader ,etc like these do we have in postgres).***
you could create roles like these, make users members of these roles,a
nd grant permissions to the roles.
for more information, see
https://www.postgresql.org/docs/current/static/user-manag.html
https://www.postgresql.org/docs/current/static/sql-grant.html
https://www.postgresql.org/docs/current/static/sql-revoke.html
https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html
--
john r pierce, recycling bits in santa cruz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Roles and security
2017-11-25 20:03 Roles and security nikhil raj <nikhilraj474@gmail.com>
@ 2017-11-26 15:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2017-11-26 15:18 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; pgsql-jobs@postgresql.org; pgsql-sql; pgsql-admin@postgresql.com; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
nikhil raj wrote:
> Currently my company is shifting from MS SQL to postgres
>
> check the roles of user I want to give user roles only select, insert, update, execute and create database to all users Permission
>
>
> The users should not have these permission
> drop database , delete table permission
If you want to allow a user (or role) CREATE DATABASE, give them the
CREATEDB privilege with ALTER ROLE.
If you mean "schema" when you say "database", give them the CREATE
privilege on the database instead.
SELECT, INSERT, UPDATE and EXECUTE are granted on individual objects,
not in general.
You can, however, use ALTER DEFUALT PRIVILEGES to automatically grant
privieges on any new object created.
Note that by default, everybody (the special role PUBLIC) has EXECUTE
privilege on all functions.
Only superusers and object owners are allowed DROP and ALTER on objects,
and there is no way to prevent that.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-11-26 15:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-25 20:03 Roles and security nikhil raj <nikhilraj474@gmail.com>
2017-11-25 23:15 ` John R Pierce <pierce@hogranch.com>
2017-11-26 15:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox