Re: [SQL] create role
> On 31 December 2010 05:14, Tony Capobianco > wrote: >> esave_dw=> \d members >> Did not find any relation named "members". >> esave_dw=> >> esave_dw=> \d esave.members >> Table "esave.members" >> Column | Type | Modifiers >> -+-+--- >> memberid | numeric | not null >> etc >> >> How can I get this so I don't have to preface the \d with the schema >> name every time? > > Hi Tony, you should changes the default search_path for the specified > users. > http://sql-info.de/postgresql/schemas.html > take a look at practical schema usage section. > Gibransyah, That did the trick! Thanks for your help. I modified my role name from developer to dev since I already have a dev schema. Here's the steps I ran below to get it working: create role dev login; alter role dev set default_tablespace=dev; alter role dev set search_path=dev,staging, esave, support, email,public; grant usage on schema esave to dev; grant usage on schema dev to dev; grant select on members to dev; grant create on schema dev to dev; grant create on tablespace dev to dev; I am a little confused as to why I had to grant usage & create on dev to dev since it's both the dev role's default_tablespace and has a schema named after it. Either way, this corrects my issue. Thanks for your help! Tony -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Friday 31 December 2010 8:22:23 am [email protected] wrote: > > On 31 December 2010 05:14, Tony Capobianco > > > > wrote: > >> esave_dw=> \d members > >> Did not find any relation named "members". > >> esave_dw=> > >> esave_dw=> \d esave.members > >> Table "esave.members" > >> Column | Type | Modifiers > >> -+-+--- > >> memberid | numeric | not null > >> etc > >> > >> How can I get this so I don't have to preface the \d with the schema > >> name every time? > > > > Hi Tony, you should changes the default search_path for the specified > > users. > > http://sql-info.de/postgresql/schemas.html > > take a look at practical schema usage section. > > Gibransyah, > That did the trick! Thanks for your help. I modified my role name from > developer to dev since I already have a dev schema. Here's the steps I > ran below to get it working: > > create role dev login; > alter role dev set default_tablespace=dev; > alter role dev set search_path=dev,staging, esave, support, email,public; > > grant usage on schema esave to dev; > grant usage on schema dev to dev; > grant select on members to dev; > grant create on schema dev to dev; > grant create on tablespace dev to dev; > > I am a little confused as to why I had to grant usage & create on dev to > dev since it's both the dev role's default_tablespace and has a schema > named after it. Either way, this corrects my issue. Thanks for your > help! > > Tony First tablespace != schema. From here http://www.postgresql.org/docs/9.0/interactive/manage-ag-tablespaces.html: "Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects. By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured. Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system. " Second, from the schema docs if you want 'user' schemas than "The value for search_path must be a comma-separated list of schema names. If one of the list items is the special value $user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema. (If not, $user is ignored.) " "The default value for this parameter is '"$user", public' (where the second part will be ignored if there is no schema named public). This supports shared use of a database (where no users have private schemas, and all share use of public), private per-user schemas, and combinations of these. Other effects can be obtained by altering the default search path setting, either globally or per-user. " -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Friday 31 December 2010 8:22:23 am [email protected] wrote: > > On 31 December 2010 05:14, Tony Capobianco > > > > wrote: > >> esave_dw=> \d members > >> Did not find any relation named "members". > >> esave_dw=> > >> esave_dw=> \d esave.members > >> Table "esave.members" > >> Column | Type | Modifiers > >> -+-+--- > >> memberid | numeric | not null > >> etc > >> > >> How can I get this so I don't have to preface the \d with the schema > >> name every time? > > > > Hi Tony, you should changes the default search_path for the specified > > users. > > http://sql-info.de/postgresql/schemas.html > > take a look at practical schema usage section. > > Gibransyah, > That did the trick! Thanks for your help. I modified my role name from > developer to dev since I already have a dev schema. Here's the steps I > ran below to get it working: > > create role dev login; > alter role dev set default_tablespace=dev; > alter role dev set search_path=dev,staging, esave, support, email,public; > > grant usage on schema esave to dev; > grant usage on schema dev to dev; > grant select on members to dev; > grant create on schema dev to dev; > grant create on tablespace dev to dev; > > I am a little confused as to why I had to grant usage & create on dev to > dev since it's both the dev role's default_tablespace and has a schema > named after it. Either way, this corrects my issue. Thanks for your > help! > > Tony In my previous message I forgot to add the following. The set commands and grants are not linked. Setting something does not necessarily confer privileges for that object. The search_path for instance. It really only sets up the search order for unqualified object names. What you can see or do with those objects is determined by the privileges on those objects. Those privileges come from either the role that created the object or are GRANT(ed) by a sufficiently privileged role to another role. -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
