Re: [GENERAL] Role & User - Inheritance
Unfortunately, Everything that is a permission (CREATEROLE, etc) when creating a role cannot be inherited. Only the GRANT stuff is inherited and I think only when the WITH .. is given on the GRANT. I may be wrong on the last part though. David On 3/15/07, Alexi Gen <[EMAIL PROTECTED]> wrote: [GENERAL] Role & User - Inheritance? A ROLE dba01 has been given the option of SUPERSUSER. A USER user01 is created and tagged to the above ROLE dba01. When attempting to create a Tablespace (logged in as user01) it generates the following message: "permission denied to create tablespace (tblspc01). Must be superuser to create a tablespace.". Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt user01 also inherit this? If not - what benefit exists in grouping users under a Role? Cheers! _ Spice up your IM conversations. New, colorful and animated emoticons. Get chatting! http://server1.msn.co.in/SP05/emoticons/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored procedure
On 3/13/07, Alain Roger <[EMAIL PROTECTED]> wrote:
Hi,
I have a stored procedure which returns a SETOF RECORD.
so basically a partial rowtype from a table.
to execute the query in PHP, i must write :
select * from myschema.sp_a_002('username') as result(Column1 varchar);
to get the result.
However, is there another to get the result without using 'as
result(column1 varchar)' ?
something like a simple "select * from storedprocedure(param);", for
example :-)
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
That's what I do in PHP
if it returns a set "SELECT * FROM myfunction(arg1,arg2,...)"
Then I can just iterate over every record/line/row.
I don't have any stored proc that actually returns a setof record in any of
my functions though, but I would imagine that it doesn't make a difference
in how you call it.
Could you past the whole function?
David
Re: HIPPA (was Re: [GENERAL] Anyone know ...)
On 3/10/07, Kenneth Downs <[EMAIL PROTECTED]> wrote: Alvaro Herrera wrote: David Legault escribió: That's basically what I've done with my past questions on the ROLE system in place. Since roles are global, I wanted it fine grained to the DB level so I had to append DB_ in front of each role name and by using current_database() inside my functions, I could hide that from the exterior. Hmm, there used to be a facility to restrict users to specific databases, enabled by db_user_namespace (not by default). I tried this, but couldn't achieve what I wanted with it, it's also marked as "temp stuff could change in the future". It seems to still work on 8.2 ... there is also the 'samegroup' facility in pg_hba.conf. We create a group named after each database, and a person cannot get into a database unless they are in that group. I append the name of the DB on front of the roles and it works flawlessly, you can't have 2 DB with the same name in the cluster. It's also transparent to the application since the functions to interact with the roles + other tables use current_database() which gets the name of the DB to which the groups are assigned. All databases are REVOKED from public as all functions. When a user is created he is granted connect on the database to which he belongs. So even if someone gets the credentials to db1_user and attempts to connect to db2 with it it will fail. David
[GENERAL] pl/pgsql FOR LOOP with function
Hello, How can I return the contents of the row object without knowing the names of the parameters in it ? I'd like to do something like row[1] or something similar. Or is there a way to actually get the values knowing the return type but not the name? FOR row IN SELECT * FROM getgroups(usr) LOOP .. snip .. END LOOP; when FUNCTION getgroups(usr name) RETURNS SETOF name AS Thanks David
Re: HIPPA (was Re: [GENERAL] Anyone know ...)
That's basically what I've done with my past questions on the ROLE system in
place. Since roles are global, I wanted it fine grained to the DB level so I
had to append DB_ in front of each role name and by using current_database()
inside my functions, I could hide that from the exterior.
Now I have a web administration panel that can let me administer users and
groups. Functions are synced with app permissions and given to groups only.
Then users are assigned to groups to give them their app permissions + db
function permissions. I had to create a couple extra tables to do the sync
between app permissions (view page X, do action Y) and the functions needed
for each of these app permission combos.
Users of my products will now be able to control all the users via that
panel, it also removes any superuser credentials on the app level as now the
DB users are used for the web app login too.
I'm very pleased to have learned this new database and its incorporation in
my apps and future apps.
David
On 3/10/07, Kenneth Downs <[EMAIL PROTECTED]> wrote:
Awesome! That never occurred to me. This is really cool.
Tom Lane wrote:
Kenneth Downs <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> writes:
Perhaps a lesser form of CREATEROLE, CREATEROLE_LIMITED, who can create
roles and only grant to the roles he himself is a member of.
You can make that out of spare parts today, by granting non-superusers
execute rights on functions that create users.
regression=# create or replace function makeuser(text) returns void as $$
begin
execute 'create role ' || quote_ident($1) || ' login';
end$$ language plpgsql security definer;
CREATE FUNCTION
regression=# revoke all on function makeuser(text) from public;
REVOKE
regression=# create user joe;
CREATE ROLE
regression=# grant execute on function makeuser(text) to joe;
GRANT
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create user foo;
ERROR: permission denied to create role
regression=> select makeuser('foo');
makeuser
--
(1 row)
regression=> \c - foo
You are now connected to database "regression" as user "foo".
regression=>
regards, tom lane
Re: [GENERAL] security permissions for functions
On 3/8/07, Rikard Pavelic <[EMAIL PROTECTED]> wrote: Bill Moran wrote: >> Hmm, so the answer to my question >> "How can I assign execute permission to a role for a single function >> inside schema." >> is I can't? >> > > How did you interpret "do it like this" to mean "you can't do it"? > > REVOKE ALL ON FROM PUBLIC; > > >> So this basically means that I can't fine tune the permissions through >> functions, but I >> can through views and tables? >> This looks like a bug in design to me ;( >> > > Relax. You (somehow) misunderstood Tom. > > Urgh, I didn't meant it like that ;( Sorry. Anyway, maybe I didn't make myself clear enough. Let's try again ;) Let's say I have two functions in schema example example.function1() example.function2() and two users user1 and user2 Is there a way to grant user1 permission to execute function example.function1(), but not example.function2() And for user2 to be able to execute example.function2() and not example.function1()? To grant user1 permission to execute example.function1() I need to do GRANT USAGE ON SCHEMA example to user1 But this permissions grants him also ability to execute example.function2 () GRANT EXECUTE ON FUNCTION example.function1() TO user1 or REVOKE EXECUTE ON FUNCTION example.function1() FROM user1 don't have any affect. Role PUBLIC don't have access to this schema so that is not an issue. I have a GRANT USAGE on schema "name" to PUBLIC and I have a GRANT EXECUTE on function1 to user1 and a GRANT EXECUTE on function2 to user 2 and I can't access function2 as user1. But when I created the functions I did a REVOKE EXECUTE on functionN from PUBLIC Exception: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for function function2 ... Regards, Rikard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings David
Re: [GENERAL] get username of user calling function?
On 3/1/07, George Nychis <[EMAIL PROTECTED]> wrote: David Legault wrote: > > > See the EXECUTE function in the pl/pgSQL language in the docs for dynamic > queries. > So it turns out that in a SECURITY DEFINER the current_user is the owner of the function. I had to use session_user and it works now :) yes because you are running it in the context of the owner of the function there is also another SECURITY setting that will be in the context of the caller where current_user() should return the callee. - George
Re: [GENERAL] get username of user calling function?
On 3/1/07, George Nychis <[EMAIL PROTECTED]> wrote: A. Kretschmer wrote: > You can use the current_user - variable. Select current_user; I'm trying to create a function in which users can only kill their own processes, it works perfectly if i hardcode a username in such as this: CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select pg_cancel_backend(procpid) FROM (SELECT procpid FROM pg_stat_activity WHERE procpid=$1 and usename=''gnychis'') AS kill;' LANGUAGE SQL SECURITY DEFINER; See the EXECUTE function in the pl/pgSQL language in the docs for dynamic queries. But if i try to replace usename=''gnychis'' with usename=current_user it no longer works. Any ideas? - George ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] REVOKE ALL
Will report on it, There isn't anything in the FAQ about changes to the ROLES that I've seen though this line may be interesting: %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command The proposed syntax is: GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW TABLES IN public TO phpuser; It's marked with an % saying it's easy to implement, but isn't with a "-" so it won't be in 8.3. Will make REVOKE and GRANT commands much easier without knowing all the objects that need to be listed to use them. Thanks David On 2/21/07, Richard Huxton wrote: David Legault wrote: > I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it works > properly and report back on it. Excellent - never used it myself. > Is there a place where I can see what features were suggested and where I > could suggest my own? Full list, and what people are thinking about for 8.3: http://www.postgresql.org/docs/faqs.TODO.html http://developer.postgresql.org/index.php/Todo:WishlistFor83 Check the mailing-list archives before proposing a new feature - see what others have thought of in the past. Then, think through what you want and discuss it here before posting to the hackers list. If you're not planning to make changes yourself, you'll either need to directly persuade a developer, or gain enough support to convince one it's worth their time. -- Richard Huxton Archonet Ltd
Re: [GENERAL] REVOKE ALL
I only have a local root user for DBA pruposes, the rest will be DB specific roles (If I can do it properly). Users have no privileges except CONNECT to the DB. All the privileges are granted to group roles. Users are assigned groups to have access to DB functionality which are all stored in functions. There is a "guests" group role for public access which is locked down to the basic data stuff, minimum required to display stuff on the website. Then there is "administrators" group role for the admin of the system. A user member of that group can then start creating new groups add assign them privileges (application + DB - they are synched) and then create new users and assign them groups. Everything is built so that the modularity of the application side can be respected, I just need to drop in a new application module, update the application privileges table, add the new functions and sync them together (M to M table mapping) and the system will now detect the new possibilities itself and allow an admin to add the new module privileges to already existing groups or create a new group for them. I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it works properly and report back on it. Is there a place where I can see what features were suggested and where I could suggest my own? Thanks David On 2/21/07, Richard Huxton wrote: David Legault wrote: > From the docs > > db_user_namespace (boolean) > It doesn't talk about this in the CREATE ROLE docs though so it's a bit > ambiguous and the note saying this is a temp measure means they are > thinking > of something better for the future I assume. Well, it's clearly not ideal, but it's probably a fair bit of work to have global AND database-specific users in a clean way, and there's just not been the demand for it. > I'm trying to build a web managed system for my apps (PHP) where I can > manage ROLES (users/groups) using an admin area of the site without having > to touch the DB directly like a DBA. This enables the customer to set > himself the access levels of the groups which are assigned to their > different users. Restricting roles to databases is what I want > ultimately. I > use the DB role system as the auth mecanism of the website too, so no > actual > DB super user has access to the DB and is stored in server PHP code. If the > server is compromised, there is still another layer before reaching the DB > data. Hmm - never gone quite that far myself. You've got to balance the prospects of someone gaining access to your PHP code versus the risks of handing out database passwords to all your users. Recently I've been using one user my app connects as, then use SET ROLE to switch to individual user-types or users. Not proof against hackers (except the most stupid), but it does prevent e.g. accidental changes to lookup tables. It'd be nice to have a LOCK option on the SET ROLE, but again, it depends on demand. Then, I have a separate user who owns the database and I use that user for admin tasks (backups etc). > If there was an option to force each new ROLE to have no connexion > privileges to any DB until I set one via GRANT would also be good. Else > I'll > have to revoke all DBs when I create it and then GRANT only the single > one I > want. Yep - REVOKE public from all databases and then anything after that will have to be a member of a group you've explicitly GRANTed. -- Richard Huxton Archonet Ltd
Re: [GENERAL] REVOKE ALL
From the docs db_user_namespace (boolean) This parameter enables per-database user names. It is off by default. This parameter can only be set in the postgresql.conf file or on the server command line. If this is on, you should create users as [EMAIL PROTECTED] When username is passed by a connecting client, @ and the database name are appended to the user name and that database-specific user name is looked up by the server. Note that when you create users with names containing @ within the SQL environment, you will need to quote the user name. With this parameter enabled, you can still create ordinary global users. Simply append @ when specifying the user name in the client. The @ will be stripped off before the user name is looked up by the server. *Note: * This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed. If I read it properly, this means I can create the ROLES as [EMAIL PROTECTED] and they will be limited to the DB to which is contained in their ROLE name. It doesn't talk about this in the CREATE ROLE docs though so it's a bit ambiguous and the note saying this is a temp measure means they are thinking of something better for the future I assume. I'm trying to build a web managed system for my apps (PHP) where I can manage ROLES (users/groups) using an admin area of the site without having to touch the DB directly like a DBA. This enables the customer to set himself the access levels of the groups which are assigned to their different users. Restricting roles to databases is what I want ultimately. I use the DB role system as the auth mecanism of the website too, so no actual DB super user has access to the DB and is stored in server PHP code. If the server is compromised, there is still another layer before reaching the DB data. If there was an option to force each new ROLE to have no connexion privileges to any DB until I set one via GRANT would also be good. Else I'll have to revoke all DBs when I create it and then GRANT only the single one I want. Thanks David On 2/21/07, Richard Huxton wrote: David Legault wrote: > Concerning the pg_hba.conf file, I don't want to prevent external > connections to the DB as I need all my web apps to connect to them. I was > referring the fact that ROLE A "belongs" to DB G so that I don't want > him to > access anything in DB H for example. > > I'd like to be able to create roles that can't connect (not the pg_hba.conf > user configs) to any database except the ones for which they have been > granted the privilege to do so. > > So when creating ROLE A, he wouldn't be able to connect [through a PHP call > pg_connect(user, pass, db)] until I explicitly grant him access to the DB > for connexion via GRANT ON DATABASE G TO A. REVOKE CONNECT ON DATABASE g FROM public; Then you'll need to add "GRANT CONNECT" for each user/group. Also check the section on database-specific roles in the manuals (there's a setting in postgresql.conf that lets you have [EMAIL PROTECTED]). Might be useful. -- Richard Huxton Archonet Ltd
Re: [GENERAL] REVOKE ALL
Concerning the pg_hba.conf file, I don't want to prevent external connections to the DB as I need all my web apps to connect to them. I was referring the fact that ROLE A "belongs" to DB G so that I don't want him to access anything in DB H for example. I'd like to be able to create roles that can't connect (not the pg_hba.conf user configs) to any database except the ones for which they have been granted the privilege to do so. So when creating ROLE A, he wouldn't be able to connect [through a PHP call pg_connect(user, pass, db)] until I explicitly grant him access to the DB for connexion via GRANT ON DATABASE G TO A. As for the REVOKE and checking of privileges, haven't found anything for that on pgfoundry, will look on google. Thanks David On 2/21/07, Richard Huxton wrote: David Legault wrote: > In which table pg_* are stored the GRANT options? As I can do a cross-check > with a SELECT to see if the user has any grants on functions using the > pg_proc table. At the same time, I need to know exactly the names of the > functions to be able to REVOKE them which in my opinion, there should be a > wildcard which enables you to REVOKE everything at once without prior > knowing the names of the functions. Check pgfoundry / google for some functions to do just that - there are plenty out there. If you want to write your own, the permissions are stored in "proacl" in pg_proc (and similarly named columns in other tables for other objects). You may find the information_schema.routine* views simpler to work with for part of your effort though. -- Richard Huxton Archonet Ltd
Re: [GENERAL] REVOKE ALL
In which table pg_* are stored the GRANT options? As I can do a cross-check with a SELECT to see if the user has any grants on functions using the pg_proc table. At the same time, I need to know exactly the names of the functions to be able to REVOKE them which in my opinion, there should be a wildcard which enables you to REVOKE everything at once without prior knowing the names of the functions. The has_function_privilege(user, function, privilege) is of no use except to check if he has a GRANT on a function but again you need to explicitly name that function and arguments when you REVOKE. I'll then block everyone on connection, and allocate to new users using the grant options on database. Thanks David On 2/21/07, Richard Huxton wrote: David Legault wrote: > Hello, > > Is there a way to revoke all privileges of a role without actually > specifying the whole list of items. > > Like if a role has privileges on FUNCTIONs, is there a REVOKE all > FUNCTIONS. There's no GRANT/REVOKE ON public.* command format, but there are plenty of plpgsql functions that do something of the sort. > Is there a way to check if it has a GRANT in a particular type (CONNECT, > FUNCTION, TRIGGER) before calling the REVOKE command? You can wrap it in a function and check the system catalogues or use the has_xxx_privilege() functions, otherwise no. > Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to connect > to other databases if I haven't given him permission to do so (what is the > default value when a role is created since roles are global)? By default all users can connect to all databases. This is limited by your pg_hba.conf settings and after that by GRANT CONNECT; -- Richard Huxton Archonet Ltd
[GENERAL] REVOKE ALL
Hello, Is there a way to revoke all privileges of a role without actually specifying the whole list of items. Like if a role has privileges on FUNCTIONs, is there a REVOKE all FUNCTIONS. Is there a way to check if it has a GRANT in a particular type (CONNECT, FUNCTION, TRIGGER) before calling the REVOKE command? Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to connect to other databases if I haven't given him permission to do so (what is the default value when a role is created since roles are global)? Thanks David
[GENERAL] Per Database Roles
Hello, Is there a way to attach roles to only certain databases so that the login [from PHP pg_connect(username, password, database)] is tied to that particular database and any creation of roles (users/groups) can be constrained into that particular database. I plan to use the roles system to be able to create the users/groups access/permissions and I would like to have them isolated on a per database basis instead of having them in a situation that Role A (user) belonging to DB C could also be used in DB D (security issue). I've seen this from the docs : db_user_namespace (boolean) This parameter enables per-database user names. It is off by default. This parameter can only be set in the postgresql.conf file or on the server command line. If this is on, you should create users as [EMAIL PROTECTED] When username is passed by a connecting client, @ and the database name are appended to the user name and that database-specific user name is looked up by the server. Note that when you create users with names containing @ within the SQL environment, you will need to quote the user name. With this parameter enabled, you can still create ordinary global users. Simply append @ when specifying the user name in the client. The @ will be stripped off before the user name is looked up by the server. *Note: * This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed. But in the CREATE ROLE portion of the docs it talks about them being global and doesn't mention anything about creating them as [EMAIL PROTECTED] to tie them to that particular DB. Thanks David
Re: [GENERAL] Addons
I can't seem to be able to change/add builtin contrib items using the installer after it's been installed already. Is there another way to access those modules and install them manually ? Thanks On 2/17/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: David Legault wrote: > Hello, > > I can't find a list of addons on the website. I'd like to view the list of > addons like pgcrypto and download/install some of them into my installation > so I can use some of the functions. There is www.pgfoundry.org and there is the built in contrib. The built in contrib has the items such as pgcrypto. > > And is there any advanced docs on the PL/PGSQL language like how to > manipulate strings (string replace, search, split, etc) The language doesn't :), PostgreSQL does. Unlike something like Perl (or plPerl), something like replace() uses the PostgreSQL function replace() not a language function. See here: http://www.postgresql.org/docs/8.2/static/functions.html You can call anything in the above link from plpgsql, directly. Sincerely, Joshua D. Drake > > Thanks > > David > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
[GENERAL] Addons
Hello, I can't find a list of addons on the website. I'd like to view the list of addons like pgcrypto and download/install some of them into my installation so I can use some of the functions. And is there any advanced docs on the PL/PGSQL language like how to manipulate strings (string replace, search, split, etc) Thanks David
Re: [GENERAL] ROLE INHERIT
Well basically, I was planning on building my user accounts directly using the postgres user/group policy and get away from the model superuser always connects to the DB. I kind of think it defeats the purpose of inheritance of attributes if you can't inherit from all attributes of a certain group. basically, you have a web app with a guest account into a guests group for any anonymous user that comes in. Once they log in, they have access to their privileges. If an "administrator" group user logs in I want him to be able to add "sub-admin" people and administer the users/groups via an admin web interface. Since my application is built in modules, all web application privileges (usually page/action combo) are correlated with the group privileges (which determine which stored proc/functions) that person has access via his membership to the groups. The way I see it , is just an extra loop for me. When I create a new user and assign him a group, I'll simply check if any of the group membership he has contains the CREATEROLE and just give it to him and vice-versa if he loses a group and doesn't have it anymore. Could there be an option (off by default) that could tell Postgres to INHERIT those 4 conditions like the command: GRANT *role* [, ...] TO *username* [, ...] [ WITH [ADMIN OPTION] [FULL INHERIT] ] Or something similar. Thanks David On 2/15/07, Kenneth Downs <[EMAIL PROTECTED]> wrote: Tom Lane wrote: "David Legault" <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> writes: I thought it would transfer that CREATEROLE privilege too. I've been dying to get 2 cents in on this. Tell me if this suggestion makes any sense. We use real database users in our systems, we don't connect in with an over-endowed user and then arbitrate security in client code. Therefore, we depend entirely upon the server's ability to enforce security. The practical advantage of this, which is huge, is that nowhere in my Postgres settings do I have to make allowance for the web user (apache) to "go root" with respect to Postgres. As a general rule we consider this good of course, because a remote exploit on the web server could not do anything the user could not do anyway. Except for the hole. On a public site that lets users register, we have to have way to let the web server assume the role of somebody who has createuser privelege, and that's pretty much the end of the no-root policy. If an exploit could be placed, it could simply go into that mode and create a superuser. What would be really nice is if you could limit the ability of CREATEUSER to grant roles. A nice general solution would be to allow a user with CREATEUSER privelege to only put other users into the same groups that person is in, or perhaps into a list specified by a higher-privelege user. What's chances of anything like that showing up? And, dumb question, am I mistaking the purpose of INHERIT and it already does what I'm saying? I don't think so because INHERIT does not let somebody create users out of the void. This is documented someplace ... ah, under CREATE ROLE: : The INHERIT attribute governs inheritance of grantable privileges (that : is, access privileges for database objects and role memberships). It : does not apply to the special role attributes set by CREATE ROLE and : ALTER ROLE. For example, being a member of a role with CREATEDB : privilege does not immediately grant the ability to create databases, : even if INHERIT is set; it would be necessary to become that role via : SET ROLE before creating a database. The main reason we did that is that SUPERUSER seemed a bit too dangerous to be an inheritable privilege. You could argue the other role attribute bits either way, but for simplicity they all act the same. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ROLE INHERIT
The docs should probably be more clear about this because you can do: GRANT *role* [, ...] TO *username* [, ...] [ WITH ADMIN OPTION ] GRANT on Roles This variant of the GRANT command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its members. GRANT administrator TO admin; I thought it would transfer that CREATEROLE privilege too. Thanks David On 2/15/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: David Legault escribió: > Hello, > > I'm a bit new to Postgre, and I'm experimenting with the roles stuff. > > I want to know why If I create a role called "administrator" (a group > basically, no login) : > > CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; > > And then create a user > > CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; > > admin doesn't have the CREATEROLE privilege himself, but because he is part > of a group that has it, why doesn't this fall back on him having it? > > When I try to use that admin user to create another role, it says > insufficient privileges. > > Am I missing something in this role stuff ? A single point, which is that while the privileges that are inherited are those that you can GRANT and REVOKE with the respective commands. CREATEROLE and the others are not inherited. Also, keep in mind that while role "admin" does not have CREATEDB privilege, if you grant it the CREATEROLE privilege it will easily be able to create a database by creating another role with CREATEDB privilege. So don't grant CREATEROLE to just anyone. FYI, the short name of PostgreSQL is "Postgres", not Postgre. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
[GENERAL] ROLE INHERIT
Hello, I'm a bit new to Postgre, and I'm experimenting with the roles stuff. I want to know why If I create a role called "administrator" (a group basically, no login) : CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; And then create a user CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it? When I try to use that admin user to create another role, it says insufficient privileges. Am I missing something in this role stuff ? Thanks David
